We recently hosted an entrepreneur contest internally on a SharePoint 2019 on-prem team site. Entries were voted on by everyone at the company. The form we created gave everyone $1,000,000 to distribute among the teams offering an idea. On the voting form we had a Currency column for each team. At the top of the form we showed the Allotted Funds ($1,000,000) and then had two Calculated Value controls. One Calculated Value for Allocated Funds that used a simple formula: Team01+Team02+Team03...etc. Another Calculated Value formula subtracted the Allocated from the Allotted Funds to show the Available Funds. Easy, right?
The problem we ran into was that if someone entered a value in a team and then removed it, the formula started treating the blank values in the Currency columns as strings and then the Allocated Funds and Available Funds Calculated Values started to concatenate values instead of add them. We ended up having to modify the Calculated Value formula to accommodate for this as follows:
While we did find a solution, this seems a bit counterintuitive and overly difficult, especially given that we were only working with columns that were declared as integers. We also tried regular Number columns and got the same result. Both times, we also tested the Save data as type options (Number and Currency) in the Calculated Value control Is there an easier way to accomplish this? Are we overlooking something?