Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    #Div/0! (Excel 2003)

    I am having a Div/0! problem. I attached a version of the problem in a generic example to illustrate. The short of it is that I cant get rid of the #Div/0! error when a lookup returns no result. Excel seems to think the result is zero, but any division calculation does not recognize the zero returned.

    In the Animals tab I do lookups for various animals that are categorized in a different format. I have a Moose, which is not on my lookup reference table, so the VLookup returns zero as a value. However, cell D6 returns #Div/0! no matter what I do. I even have the traditional =IF (B6=0,0,(B6-C6)/B6), but that didnt help here. I also tried to make it "" so it would be blank, to no avail. Lastly, I tried conditional formatiing to mask the zero, but that didnt remove the error either.

    I think the problem is that its not really zero- maybe its NULL, or "not found". Does anyone know what is happening here and how I can effectively get rid of the error?

    Because of the nature of the real life problem, I cant just add "moose" to the lookup- in effect, I want to ignore values not found or returned.

    Thanks!

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: #Div/0! (Excel 2003)

    The value in B6 is not stored as a number. You can either modify the equation in D6 to:

    <code><big>=IF(B6="0",0,(B6-C6)/B6)</big></code>

    or change the array formula in B6 and remove the quotation marks from the 0 in the middle of the formula.

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Div/0! (Excel 2003)

    Well, that was easy- thanks!

Posting Permissions

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