Okay, want to learn this once and for all, how _do_ we get rid of zeros formulaically?

[StargateFanNotAtHome]

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! D

 

[GTVT06]

Hello,
Can you reformat the cell? Format Cells/Custom/ and formatting it as #
should clear it, or you can do =if(SUM('2007-2009'!
L22:O22)=0,"",SUM('2007-2009'!L22:O22))

 

[Gord Dibben]

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

 

[StargateFanNotAtHome]

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

You're right. I got that wrong. I see now that I'm actually really
using the formula above that I didn't foresee something. When the
source cell(s) are _blank_ the target cell should be blan; but when
the value is "0", it should reflect "0". Does that make sense?

The reason this important to differentiate is since this workbook is
dealing with year-to-date issues. Anything after today will be blank
but should not show zeros in the summary YTD as these haven't occurred
yet. But though the fiscal year is still relatively short, there have
been months where no expenditures occurred in various categories so
they must, indeed, reflect 0.

I've put the above formula into my tips folder because it is very
valuable. But how would the real case that I'm dealing with today be
dealt with? I promise that I'll use that as a template from now on!
I think I have enough "0" cases to cover everything after we get this
one resolved <g> I know how to deal with so many other issues because
I keep them in my tips folder and just copy/paste when needed and
modify the figures, also as needed. I'm just missing these zero-case
scenarios. <g>

Thanks!!! D

 

[Jim Thomlinson]

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...

 

[Gord Dibben]

Unfortunately a blank cell is treated as a zero by Excel.

If you want the zero to show when the result equates to zero, not just the
result of blank cells, I think you would have to COUNT on the range and if
any zeros, return zero, else return ""

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


Gord