Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can't get a formula to work in each row of an Excel sheet.
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. |
#2
|
|||
|
|||
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" wrote in message ... 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. |
#3
|
|||
|
|||
It works - thanks so much, I was about to go crazy! I didn't even think of
text. Thanks again. "N Harkawat" wrote: 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Frequency formula | Excel Worksheet Functions | |||
Formula doesnt work | Excel Worksheet Functions | |||
formula won't work | Excel Worksheet Functions | |||
formula won't work | Excel Worksheet Functions |