Except if the sum of the range are values that add up to zero then the range
was not blank. It just happened to add up to zero. For that reason I often
use a count as the criteria for my if.

As for the bigger question "What to do about zeros" the answer is...
Depends...

If you are graphing 0 or blank graph as zero so you may need to return #N/A
to avoid a graph that looks like a heart monitor. If you sum up a blank range
it return zero when you may want it to return a blank. If on the other hand
the range contains values that add up to zero then you may want to dispay the
zero. In the end you can also set the View option to just not show zeros...
--
HTH...

Jim Thomlinson


"Gord Dibben" wrote:

If a cell has a formula it cannot be truly blank.

=IF(SUM('2007-2009'!L22:O22)=0,"",SUM('2007-2009'!L22:O22))

Will look blank if result is 0


Gord Dibben MS Excel MVP

On Fri, 15 Aug 2008 15:07:57 -0400, StargateFanNotAtHome
wrote:

Okay, once and for all, I really need to learn how to deal with zeros.
Whenever I've had a problem, if I can't phase it out with conditional
formatting and coloured cells, I've asked a couple of times or so for
help on individual cases to get rid of the zeros properly through the
formula. But I've looked through various examples and I can't see the
pattern. So perhaps some kind soul can give me a general overview?
Perhaps my brain will register once and for all how to deal with those
pesky zeros that come up due to results of a formula based on, as yet,
empty source fields?

In this good example of what I'm trying to fix today, it's a simple
formula:

=SUM('2007-2009'!L22:O22)

When the source sheet has no data in it in the pertinent cells, then
the target sheet where this formula is in shows a "0". We need to
keep the cells truly blank in those cases.

Thanks! :oD