Hide Zero Values

**stoey** · 09-09-2011, 11:53 AM

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

**MarvinP** · 09-09-2011, 11:58 AM

Hi stoey,

Put this in E12

Please Login or Register  to view this content.

see the attached.

**stoey** · 09-09-2011, 12:27 PM

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

**stoey** · 09-09-2011, 12:33 PM

Here's an attached workbook Marvin, Thanks

**MarvinP** · 09-09-2011, 01:23 PM

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?

**stoey** · 09-09-2011, 01:50 PM

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

**barryleajo** · 09-11-2011, 05:32 AM

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