Getting a #VALUE error in Excel sheet

Question

I need help with two formulas. First, I am getting a #VALUE for one formula. When the sheet is blank, the error is in cell P28:

Cell P28 is formula =(P26*P27)+N24

Cell P27 is a fixed value of 0.52

Cell P26 is =M24

Cell M24 is a sum formula of numbers =IF(SUM(M7:M23)=0,"",SUM(M7:M23))

Cell N24 is a sum formula of numbers =IF(SUM(N7:N23)=0,"",SUM(N7:N23))

*The error only occurs when the sheet is blank.

Also, when that error is corrected, I would like cell P28 to have a formula for it to be blank when the total is zero. I'm not sure how to write that formula.

Thank you for your help!

Answer 1

Hi,

Rather than the IF True equaling " ", make it equal to 0. In other words:

=IF(SUM(M7:M23)=0,0,SUM(M7:M23))

Make the same change in the other IF statement, too.

The #VALUE is because in the original formula Excel was being asked to multiply a blank space rather than a number.

Regarding P28 to have a formula for it to be blank when the total is zero. conditional formatting may help, but to be sure, you will have a formula still in the cell, just the format would be, say, white text on a white background rather than a cell devoid of any content whatsoever.

Mike

Answer 2

=IF(M24="", "", (P26*P27)+N24)