Can't get a formula to work in each row of an Excel sheet.

[JessicaMc]

I have a spreadsheet set up to analyze data from survey results. The column
that is set up to calculate the average response (1-10 scale) for each
question is suddenly not working correctly. The formula is
=+($D$2*D3+$E$2*E3+$F$2*F3+$G$2*G3+$H$2*H3+$I$2*I3+$J$2*J3+$K$2*K3+$L$2*L3+$M$2*M3)/O3,
and has worked perfectly in other spreadsheets from previous issues of the
survey. This time, it works in the first row and none of the other 53. I get
either a VALUE! error message or the answer it returns is incorrect.

It seemed to me that there was corruption in the cells, and I have done
everything I could think of to fix it. I deleted the cells, cleared all
contents and formats, deleted the column, reviewed the formula for accuracy,
and asked other people to look it over. Nothing helps. I even created an
entirely new sheet and the same thing happened.

 

[N Harkawat]

You may be getting a #value! error because one of the cells in the row is
probably not a number
even though it appears as number but really may be text

Use this formula and see if this works across all
=SUMPRODUCT($D$2:$M$2,D3:M3)/O3
and copy it all the way down to your 53 rows

This formula will ignore any text that may exist

 

[JessicaMc]

It works - thanks so much, I was about to go crazy! I didn't even think of
text. Thanks again.