# Hide Zero Values

1. ## Hide Zero Values

Hi gurus,

I have a very simple formula: SUM(E5:E10) and this is place on E12. now how could I possibly hide the value from the formula on E12 when E5:E10 are blank?

Thanks,

stoey  Register To Reply

2. ## Re: Hide Zero Values

Hi stoey,

Put this in E12 ``Please Login or Register  to view this content.``
see the attached.  Register To Reply

3. ## Re: Hide Zero Values

hi Marvin,

that was quick and it work! however, I tried using this formula...well i tried to copy its syntax and represent different values...here's what happened:

tried: =IF(SUM(E5:E10)=0,"",SUM(E5:E10)) it works BUT when i tried using this formula an represent the value on another cell it gives me #DIV/0 value.

E12:J12 contains the format formula you gave =IF(SUM(E5:E10)=0,"",SUM(E5:E10))

but once E12:J12 are zero, cell K12 where i place this formula =IF(AVERAGE(E12:j12)=0,"",AVERAGE(E12:j12)) give the result #DIV/0....is the formula you gave just intended for blanks with formula is for SUM value only?

thanks,

stoey  Register To Reply

4. ## Re: Hide Zero Values

Here's an attached workbook Marvin, Thanks  Register To Reply

5. ## Re: Hide Zero Values

Yep - Average takes the sum of the numbers and divides by how many numbers there are. In math we don't know (yet) how to divide by zero. You will get the #Div/0 error if there are no numbers in the range.

Did you need an answer for how to get a blank if there are no numbers? Look at IsError and the If statement.

In the newer Excel there is an IFError which is a little more freindly. Are you using 2003?  Register To Reply

6. ## Re: Hide Zero Values

hi again marvin,

well yeah i was trying to substitute the values of sum to average yet it gives me that #Div/0 error...well i manage to change the needed formula instead. thank you very much for your help...  Register To Reply

7. ## Re: Hide Zero Values

Hi stoey
just for completeness...
from a display point of view (not trapping #Div/0)
You can globally set cells with zero value not to display which may avoid having to program them.
From the Windows Office Button ..
.. Excel Options
.. Advanced (in the LH display list)
.. scroll down to Display Options for this Worksheet group
.. uncheck "Show a zero in cells that have a zero value"
This effects the whole worksheet  Register To Reply

8. ## Re: Hide Zero Values

This also satisfies the requirement
Insted of
=IF(AVERAGE(E12:j12)=0,"",AVERAGE(E12:j12))
Use
=IF(SUM(E12:j12)=0,"",AVERAGE(E12:j12))  Register To Reply

9. ## Re: Hide Zero Values Originally Posted by MarvinP In math we don't know (yet) how to divide by zero.
Or ever will   Register To Reply