+ Reply to Thread
Results 1 to 9 of 9

Hide Zero Values

  1. #1
    Forum Contributor
    Join Date
    08-07-2008
    Location
    United States
    MS-Off Ver
    Microsoft Office 2003
    Posts
    362

    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
    Last edited by stoey; 09-09-2011 at 01:51 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,272

    Re: Hide Zero Values

    Hi stoey,

    Put this in E12
    Please Login or Register  to view this content.
    see the attached.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    08-07-2008
    Location
    United States
    MS-Off Ver
    Microsoft Office 2003
    Posts
    362

    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. #4
    Forum Contributor
    Join Date
    08-07-2008
    Location
    United States
    MS-Off Ver
    Microsoft Office 2003
    Posts
    362

    Re: Hide Zero Values

    Here's an attached workbook Marvin, Thanks
    Attached Files Attached Files

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,272

    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. #6
    Forum Contributor
    Join Date
    08-07-2008
    Location
    United States
    MS-Off Ver
    Microsoft Office 2003
    Posts
    362

    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. #7
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    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. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2010
    Posts
    5,252

    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))
    Last edited by kvsrinivasamurthy; 09-12-2011 at 05:22 AM. Reason: spelling mistake

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Hide Zero Values

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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