Why am I getting a #VALUE! error?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm getting a #VALUE! error in this formula:
=SUM(Main!K180:K197)+Main!K218

If I remove the +Main!K218 from the formula, the error goes away. So, I
figured that there must be a problem with Main!K218. So, just to test it, I
manually entered a "zero" in Main!K218. Voila, the error went away. So, I
figured that the formula doesn't like an "empty" value ... but it can,
apparently, handle a "zero" in the field. (When Main!K218 >= 0, I don't get
the #VALUE! error.)

So, again, I guess the formula doesn't like an "empty" value which,
apparently, results from the "" in Main!K218's formula here:
=IF(A218=0,"",IF(SUM(L174:L219)+SUM(M174:M219)=0,"",IF(L218>0,(J218-(L218/100*J218)),(J218-M218))))

But, I DON'T want to display a "zero" in Main!K218. I want it to appear as
"blank" (empty). A lot of fields have that same (type of) formula so if I
have to display a zero instead of blank/empty, I'll have zeroes all over the
place. It'll make my spreadsheet look very "messy" so I want to avoid that.

If all of the above makes sense ...
how can I display a blank/empty value (not a "zero") in Main!K218 and still
avoid the #VALUE! error in the formula =SUM(Main!K180:K197)+Main!K218?

Whew.

Thanks. Dan
 
Back
Top