Okay so I have a list which is basically a survey with 20 question with 1-4 and X responses, I need to get the average of only those with a numerical value.  I have tried all of the different type of Average functions and Sum functions with no luck, saw someone had posted this question before for 2007 and converted the answer to fit my questions with no luck.

=(SUM(VALUE(IF([Q01]="X", [Q01])), VALUE(IF([Q02]="X", [Q02])), VALUE(IF([Q03]="X", [Q03])), VALUE(IF([Q04]="X", [Q04])), VALUE(IF([QO5]="X", [Q05])), VALUE(IF([QO6]="X", [Q06])), VALUE(IF([Q07]="X", [Q07])), VALUE(IF([Q08]="X", [Q08])), VALUE(IF([Q09]="X", [Q09])), VALUE(IF([Q10]="X", [Q10])), VALUE(IF([Q11]="X", [Q11])), VALUE(IF([Q12]="X", [Q12])), VALUE(IF([Q13]="X", [Q13])), VALUE(IF([Q14]="X", [Q14])), VALUE(IF([Q15]="X", [Q15])), VALUE(IF([Q16]="X", [Q16])), VALUE(IF([Q17]="X", [Q17])), VALUE(IF([Q18]="X", [Q19])), VALUE(IF([Q19]="X", [Q19])), VALUE(IF([Q20]="X", [Q20]))))/(SUM(VALUE(IF([Q01]="X",1)),VALUE(IF([Q02]="X",1)),VALUE(IF([Q03]="X",1)),VALUE(IF([Q04]="X",1)),VALUE(IF([Q05]="X",1)),VALUE(IF([Q06]="X",1)),VALUE(IF([Q07]="X",1)),VALUE(IF([Q08]="X",1)),VALUE(IF([Q09]="X",1)),VALUE(IF([Q10]="X",1)),VALUE(IF([Q11]="X",1)),VALUE(IF([Q12]="X",1)),VALUE(IF([Q13]="X",1)),VALUE(IF([Q14]="X",1)),VALUE(IF([Q16]="X",1)),VALUE(IF([Q17]="X",1)),VALUE(IF([Q18]="X",1)),VALUE(IF([Q19]="X",1)),VALUE(IF([Q20]="X",1))))

I am guessing somewhere it should be some type of not equal.

The numerator is wrong it looks like. You are actually summing the 'X' values. Your IF statements should be more like

IF([Q01] = "X",0,[Q01])    - if it's an X, you add in 0, else you add in the value.  

I also think you're going to run into length issues for this calculation if you're doing it in a calculated field. You might need to split it up into multiple fields in that case.

AVERAGE should have worked, though.  "Averages the Numbers, ignoring non-number values. I'm assuming your answers are text fields since they can contain numbers or X. Then you'd have to do this to change the text to a number.

=AVERAGE(if([Q01]="X",[Q01],Value([Q01]), if([Q02]="X",[Q02],VALUE([Q02), ....

AVERAGEA is supposed to understand and interpret numbers from text, so that might work:

=AVERAGEA([Q01], [Q02], [Q03].......    if should drop out the X and not count it in the average. So the documentation says, but I haven't tested it recently.

Yep I forgot the other side of the argument.  Here is the algorithm I am now using and it works perfectly thank you.

=(SUM(VALUE(IF(Q01="X",0,Q01)),VALUE(IF(Q02="X",0,Q02)),VALUE(IF(Q03="X",0,Q03)),VALUE(IF(Q04="X",0,Q04)),VALUE(IF(Q05="X",0,Q05)),VALUE(IF(Q06="X",0,Q06)),VALUE(IF(Q07="X",0,Q07)),VALUE(IF(Q08="X",0,Q08)),VALUE(IF(Q09="X",0,Q09)),VALUE(IF(Q10="X",0,Q10)),VALUE(IF(Q11="X",0,Q11)),VALUE(IF(Q12="X",0,Q12)),VALUE(IF(Q13="X",0,Q13)),VALUE(IF(Q14="X",0,Q14)),VALUE(IF(Q15="X",0,Q15)),VALUE(IF(Q16="X",0,Q16)),VALUE(IF(Q17="X",0,Q17)),VALUE(IF(Q18="X",0,Q19)),VALUE(IF(Q19="X",0,Q19)),VALUE(IF(Q20="X",0,Q20))))/(SUM(VALUE(IF(Q01="X",0,1)),VALUE(IF(Q02="X",0,1)),VALUE(IF(Q03="X",0,1)),VALUE(IF(Q04="X",0,1)),VALUE(IF(Q05="X",0,1)),VALUE(IF(Q06="X",0,1)),VALUE(IF(Q07="X",0,1)),VALUE(IF(Q08="X",0,1)),VALUE(IF(Q09="X",0,1)),VALUE(IF(Q10="X",0,1)),VALUE(IF(Q11="X",0,1)),VALUE(IF(Q12="X",0,1)),VALUE(IF(Q13="X",0,1)),VALUE(IF(Q14="X",0,1)),VALUE(IF(Q15="X",0,1)),VALUE(IF(Q16="X",0,1)),VALUE(IF(Q17="X",0,1)),VALUE(IF(Q18="X",0,1)),VALUE(IF(Q19="X",0,1)),VALUE(IF(Q20="X",0,1))))

• Marked as answer by Lisa Chen1226Microsoft contingent staff, Moderator 14 minutes ago

Hi hawkeye68,

Thanks for your sharing.

Best Regards,

Lisa Chen