# Calculation in Calculated Column varying based on null values?

Here's one I've never dealt with before: I have calculated columns (in a SharePoint 2013 survey) that calculate values based on particular survey questions. One of those values, however, is an "N/A" value (currently set to the number zero). What I'm trying to do is this - I need to create an average of the NON-N/A answers. For example - I have 4 survey questions from which I extrapolate an average. If one of the of the answers is N/A, I need to get the average of the other three only. What should my calculated column formula be? I've never fathomed this one before.

I know that in Excel, we could use a function like this:

``````=AVERAGEIF(A2:D2, ">0")
``````

But SharePoint doesn't recognize AVERAGEIF. Does this example help?

The following works for three fields:

``````=(q1+q2+q3)/(IF(q1=0,0,1)+IF(q2=0,0,1)+IF(q3=0,0,1))
``````

So, add the values for the three fields, and then divide by the number of fields with a non-zero value.

You may need to add another if statement around the rest to avoid a divide by zero error.

• So, according to the logic of what you've shown above, calculation for five fields would look like =(q1+q2+q3+q4+q5)/(IF(q1=0,0,0,0,1)+IF(q2=0,0,0,0,1)+IF(q3=0,0,0,0,1)+IF(q4=0,0,0,0,1)+IF(q5=0,0,0,0,1) ?
– DanJ
Aug 21, 2019 at 18:33
• The If syntax is if(condition, true value, false value). So, If(q5=0,0,1). So, given values of 1,2,3,4,0, the bottom set of if statements will result in: 1+1+1+1+0, and therefore will be 10/4 Aug 21, 2019 at 18:38
• Makes sense. Thanks, Mike.
– DanJ
Aug 21, 2019 at 19:36
• CORRECTION @Mike2500 - This isn't working. When I have 4 fields, and two of them are 9, with the other two as 0, This calculation still gives me 4.50 instead of 9 as an average. Any other ideas?
– DanJ
Aug 21, 2019 at 20:46
• Can you paste in the formula you have for the four fields? The end result should be (9+9+0+0)/(1+1+0+0) = 18/2 = 9 Aug 21, 2019 at 20:49