not displaying value in cells with a 0 value, causes "Value" error in other formulas

[Andy]

Okay - what I am trying to do is make a large spread sheet look pretty.
The spreadsheet is tracking the number of surgeries for 3 docs over
time. If a value of one of the surgeries is 0 (eg no surgeries of this
type done) I don't want the cell to display anything. (This keeps the
full year spread sheet from loading with zeros wich looks really bad.)
The way I am currently doing this is with this type of function:
"=IF(M3+C3=0,"",M3+C3)" where M3 is one surgery type and C3 is another-
this column is tracking the total of these 2 surgeries.. Now if I
evaluate the total number of surgeries, including much more common
surgeries and add the total from the more common surgeries to the less
common, I get a "Value" error because I cannot add the text field of
"". How do I get around this?

Andy

 

[jpdphd]

Andy,

It's not very hard! Forget about using your formula. Let the zero's be
zero. Instead,

Excel > Preferences > View > uncheck "Zero values"

The zeros will be invisible to you, but will still be numbers for a
formula.

jpdphd

 

[Andy]

Andy,

It's not very hard! Forget about using your formula. Let the zero's be
zero. Instead,

Excel > Preferences > View > uncheck "Zero values"

The zeros will be invisible to you, but will still be numbers for a
formula.

CRAP! I wish I had seen that before. I have wasted SO much time
using that stupid "if x=o,"", then" function. Thanks SO MUCH!
Andy

 

[JE McGimpsey]

How do I get around this?

instead of using

= Z1+Z2+Z3+S4

use

=SUM(Z1:Z3,S4)

SUM() ignores text.

 

[Andy]

SUM() ignores text.

I didn't realise that. Another nugget to be filed away. Thanks!
Andy