0

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?

Improve this question
1

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.

Improve this answer
15
  • 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 '19 at 18:33
  • 1
    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
    – Mike2500
    Aug 21 '19 at 18:38
  • Makes sense. Thanks, Mike.
    – DanJ
    Aug 21 '19 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 '19 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
    – Mike2500
    Aug 21 '19 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.