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
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
Last edited by stoey; 09-09-2011 at 01:51 PM.
Hi stoey,
Put this in E12
see the attached.Please Login or Register to view this content.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
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
Here's an attached workbook Marvin, Thanks
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?
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...
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
This also satisfies the requirement
Insted of
=IF(AVERAGE(E12:j12)=0,"",AVERAGE(E12:j12))
Use
=IF(SUM(E12:j12)=0,"",AVERAGE(E12:j12))
Last edited by kvsrinivasamurthy; 09-12-2011 at 05:22 AM. Reason: spelling mistake
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks