In Sharepoint, if you are trying to create a calculated value, you may need ‘isnull’ logic. In the case I was working on, had three columns:
Year1_Spend Year2_Spend Year3_Spend
In a fourth column called “LRP_Total_Cost”, I wanted to add these three values. If each column had a number in it, the formula is easy:
But if a column was blank, the formula wouldn’t work. One way to handle this is to have a default value of 0 (zero) in the column and to make sure all cells are populated.
However, from a user point of view, blank means zero and visually, not showing zero makes the output easier to read.
So I could set all my output screens and reports to hide the value if it is zero, or I can create a formula that assumes a blank cell is zero. I went with the formula:
= sum( (IF(ISNULL(Year1_Spend),0,Year1_Spend)), (IF(ISNULL(Year2_Spend),0,Year2_Spend)), (IF(ISNULL(Year3_Spend),0,Year3_Spend)) )