# 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

2. ## Re: Hide Zero Values

Hi stoey,

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

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

4. ## Re: Hide Zero Values

Here's an attached workbook Marvin, Thanks

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?

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

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

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

9. ## Re: Hide Zero Values

Originally Posted by MarvinP
In math we don't know (yet) how to divide by zero.
Or ever will

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1