Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Custom Format for Hiding #DIV/0!

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It's probably wishful thinking but I'm sure I've used it before - is there a custom format which I can apply to a cell to hide the #DIV/0 error?

    thanks Matt

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not that I know of, but a simple test in the cell would solve it also........ IF numerator is in A, and denominator is in B, formula would be =IF(B1<>0,+A1/B1,"")

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-28 08:34, Matt wrote:
    It's probably wishful thinking but I'm sure I've used it before - is there a custom format which I can apply to a cell to hide the #DIV/0 error?

    thanks Matt
    Matt,

    It's better to add control to formulas that lead to #DIV/0! as in:

    =IF(B1,A1/B1,0)

    instead of just:

    =A1/B1

    If you insist on formatting, use conditional formatting with the formula:

    =ISERR(E2)

    and select white a color on the Font tab.

    Aladin

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Guys

  5. #5
    New Member Geronimo's Avatar
    Join Date
    Mar 2002
    Location
    Toledo, OH
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is an example of some formatting that I'm using currently:

    =IF(ISBLANK(B32)," ",AVERAGE(B32:E32))

    It does away with the #DIV/0 error text. Of course, replace my cell & range references with your own.

    [ This Message was edited by: Geronimo on 2002-04-01 04:14 ]

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-01 04:13, Geronimo wrote:
    Here is an example of some formatting that I'm using currently:

    =IF(ISBLANK(B32)," ",AVERAGE(B32:E32))

    It does away with the #DIV/0 error text. Of course, replace my cell & range references with your own.

    [ This Message was edited by: Geronimo on 2002-04-01 04:14 ]
    Geronimo,

    An alternative to your current way of avoiding the #DIV/0! error would be:

    =SUM(B32:E32)/MAX(1,COUNT(B32:E32))

    I notice that you return a space (i.e., " ") instead of a blank (i.e., "") or a zero.

    The alternative above will return a zero. If you'd like not to see a zero, custom format the cell of the formula as:

    0.0;-0.0;;@

    or

    [=0]"";General

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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