Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: div/o error

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a reasonably complicated spreadsheet which includes whatif functions in certain areas. In one particular "if" function, the formula includes a divisible calculation that may under certain circumstances become "0" but in other circumstances become a number greater than "0". In other words the "if" function needs this formula. How do I have excel ignore the "div/0" error when this condition exists. Here is the formula:

    =IF(DL23*(AJ23+AK23)/SUM(AJ23:BI23)
    DL23 will always have a number great than "0"
    AJ23 & AK23 may not have a number great than "0"
    SUM(AJ23:BI23) may not have a number great than "0"
    DB23 may not have a number great than "0"

    Your help would be greatly appreciated

    Garry


    [ This Message was edited by: pilot48 on 2002-05-11 14:42 ]

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-11 14:20, pilot48 wrote:
    I have a reasonably complicated spreadsheet which includes whatif functions in certain areas. In one particular "if" function, the formula includes a divisible calculation that may under certain circumstances become "0" but in other circumstances become a number greater than "0". In other words the "if" function needs this formula. How do I have excel ignore the "div/0" error when this condition exists. Here is the formula:

    =IF(DL23*(AJ23+AK23)/SUM(AJ23:BI23)
    DL23 will always have a number great than "0"
    AJ23 & AK23 may not have a number great than "0"
    SUM(AJ23:BI23) may not have a number great than "0"
    DB23 may not have a number great than "0"

    Your help would be greatly appreciated

    Garry
    Basically:
    =if(my [FORMULA] DIVISOR = 0, then Show 0 or "",Otherwise show my [FORMULA])


    Kind Regards,
    Ivan F Moala From the City of Sails

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-11 14:20, pilot48 wrote:
    I have a reasonably complicated spreadsheet which includes whatif functions in certain areas. In one particular "if" function, the formula includes a divisible calculation that may under certain circumstances become "0" but in other circumstances become a number greater than "0". In other words the "if" function needs this formula. How do I have excel ignore the "div/0" error when this condition exists. Here is the formula:

    =IF(DL23*(AJ23+AK23)/SUM(AJ23:BI23)
    DL23 will always have a number great than "0"
    AJ23 & AK23 may not have a number great than "0"
    SUM(AJ23:BI23) may not have a number great than "0"
    DB23 may not have a number great than "0"

    Your help would be greatly appreciated

    Garry
    =MAX(IF(SUM(AJ23:BI23),(DL23*(AJ23+AK23))/SUM(AJ23:BI23),0),DB23)

    could be what you are looking for?

  4. #4
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ivan
    I'm sorry but I dids not post my entire formula...could you suggest the amendment

    =IF(DL23*(AJ23+AK23)/SUM(AJ23:BI23)
    Thanks Garry


    [ This Message was edited by: pilot48 on 2002-05-11 14:41 ]

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thats strange ?? I answered your post
    Assuming what was posted as;

    =IF(DL23*(AJ23+AK23)/SUM(AJ23:BI23)
    DL23 will always have a number great than "0"
    AJ23 & AK23 may not have a number great than "0"
    SUM(AJ23:BI23) may not have a number great than "0"
    DB23 may not have a number great than "0"

    Your help would be greatly appreciated

    Garry

    And Alladins shows:

    =IF(DL23*(AJ23+AK23)/SUM(AJ23:BI23)
    DL23 will always have a number great than "0"
    AJ23 & AK23 may not have a number great than "0"
    SUM(AJ23:BI23) may not have a number great than "0"
    DB23 may not have a number great than "0"

    Your help would be greatly appreciated

    Garry
    I see why now ......

    Go with what Alladin has given...



    Kind Regards,
    Ivan F Moala From the City of Sails

  6. #6
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Aladin's suggestion worked in terms of removing the div/o error, but it does not default to DB23 in the event that there is a numerical value in that cell after the formula tests to the calculation
    DL23*(AJ23+AK23)/SUM(AJ23:BI23) Any suggestions
    Garry

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-11 15:23, pilot48 wrote:
    Hi
    Aladin's suggestion worked in terms of removing the div/o error, but it does not default to DB23 in the event that there is a numerical value in that cell after the formula tests to the calculation
    DL23*(AJ23+AK23)/SUM(AJ23:BI23) Any suggestions
    Garry
    Garry,

    You lost me: The formula I suggested doesn't have the test:

    DL23*(AJ23+AK23)/SUM(AJ23:BI23)
    It's rather meant to replace your original formula as a whole. The formula

    =MAX(IF(SUM(AJ23:BI23),(DL23*(AJ23+AK23))/SUM(AJ23:BI23),0),DB23)

    compares

    the result of

    (DL23*(AJ23+AK23))/SUM(AJ23:BI23)

    with the value in DB23, picks out whichever is bigger.

    Now it is up to you to say what must be the case, so we can adjust the formula.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-05-11 15:34 ]

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
  •