+ Reply to Thread
Results 1 to 19 of 19

#div/0 Error

  1. #1
    Duke Carey
    Guest

    RE: #div/0 Error

    Maybe..

    =IF(SUM(IF(City="New York",TotalInv,0)=0,0,SUM(IF(City="New
    York",Sold,0))/SUM(IF(City="New York",TotalInv,0)))

    "Andy" wrote:

    > Like a previous post, trying to avoid the #div/0 error. I tried to get
    > something from the previous post, but have no ideas? Any help?
    >
    > Named Ranges:
    > City = B1:B1000
    > Sold = Total Number Sold, C1:C1000
    > TotalInv = Total Remaining in Stock, D1:D1000
    >
    > =SUM(IF(City="New York",Sold,0))/SUM(IF(City="New York",TotalInv,0))


  2. #2
    Max
    Guest

    Re: #div/0 Error

    Sorry, forgot to mention that both formulas should be array-entered, i.e.
    press CTRL+SHIFT+ENTER (instead of just pressing ENTER)
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----



  3. #3
    Andy
    Guest

    RE: #div/0 Error

    DUKE, YOU ROCK!!!!!!!!!

    Thanks a bunch. And I actually think I get how you did it as well.

    "Duke Carey" wrote:

    > Perhaps should have read your question a little more closely
    >
    > How about
    >
    > =IF(SUMIF(City,"New York",TotalInv)=0,0,SUMIF(City,"New
    > York",Sold)/SUMIF(City,"New York",TotalInv))
    >
    > "Andy" wrote:
    >
    > > Like a previous post, trying to avoid the #div/0 error. I tried to get
    > > something from the previous post, but have no ideas? Any help?
    > >
    > > Named Ranges:
    > > City = B1:B1000
    > > Sold = Total Number Sold, C1:C1000
    > > TotalInv = Total Remaining in Stock, D1:D1000
    > >
    > > =SUM(IF(City="New York",Sold,0))/SUM(IF(City="New York",TotalInv,0))


  4. #4
    Max
    Guest

    Re: #div/0 Error

    Try this amended version of Duke's suggestion:

    =IF(SUM(IF(City="New York",TotalInv,0))=0,0,SUM(IF(City="New
    York",Sold,0))/SUM(IF(City="New York",TotalInv,0)))

    Alternatively, perhaps a slightly shorter version
    which returns blank: "" instead of zero:

    =IF(SUM(IF(City="New York",TotalInv))=0,"",SUM(IF(City="New
    York",Sold))/SUM(IF(City="New York",TotalInv)))
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----



  5. #5
    Duke Carey
    Guest

    RE: #div/0 Error

    Perhaps should have read your question a little more closely

    How about

    =IF(SUMIF(City,"New York",TotalInv)=0,0,SUMIF(City,"New
    York",Sold)/SUMIF(City,"New York",TotalInv))

    "Andy" wrote:

    > Like a previous post, trying to avoid the #div/0 error. I tried to get
    > something from the previous post, but have no ideas? Any help?
    >
    > Named Ranges:
    > City = B1:B1000
    > Sold = Total Number Sold, C1:C1000
    > TotalInv = Total Remaining in Stock, D1:D1000
    >
    > =SUM(IF(City="New York",Sold,0))/SUM(IF(City="New York",TotalInv,0))


  6. #6
    Andy
    Guest

    RE: #div/0 Error

    Duke,
    Thanks for this. Unfortunately I'm getting an error that I'm missing a
    parenthesis in my formula.
    Andy

    "Duke Carey" wrote:

    > Maybe..
    >
    > =IF(SUM(IF(City="New York",TotalInv,0)=0,0,SUM(IF(City="New
    > York",Sold,0))/SUM(IF(City="New York",TotalInv,0)))
    >
    > "Andy" wrote:
    >
    > > Like a previous post, trying to avoid the #div/0 error. I tried to get
    > > something from the previous post, but have no ideas? Any help?
    > >
    > > Named Ranges:
    > > City = B1:B1000
    > > Sold = Total Number Sold, C1:C1000
    > > TotalInv = Total Remaining in Stock, D1:D1000
    > >
    > > =SUM(IF(City="New York",Sold,0))/SUM(IF(City="New York",TotalInv,0))


  7. #7
    Duke Carey
    Guest

    RE: #div/0 Error

    Maybe..

    =IF(SUM(IF(City="New York",TotalInv,0)=0,0,SUM(IF(City="New
    York",Sold,0))/SUM(IF(City="New York",TotalInv,0)))

    "Andy" wrote:

    > Like a previous post, trying to avoid the #div/0 error. I tried to get
    > something from the previous post, but have no ideas? Any help?
    >
    > Named Ranges:
    > City = B1:B1000
    > Sold = Total Number Sold, C1:C1000
    > TotalInv = Total Remaining in Stock, D1:D1000
    >
    > =SUM(IF(City="New York",Sold,0))/SUM(IF(City="New York",TotalInv,0))


  8. #8
    Andy
    Guest

    RE: #div/0 Error

    Duke,
    Thanks for this. Unfortunately I'm getting an error that I'm missing a
    parenthesis in my formula.
    Andy

    "Duke Carey" wrote:

    > Maybe..
    >
    > =IF(SUM(IF(City="New York",TotalInv,0)=0,0,SUM(IF(City="New
    > York",Sold,0))/SUM(IF(City="New York",TotalInv,0)))
    >
    > "Andy" wrote:
    >
    > > Like a previous post, trying to avoid the #div/0 error. I tried to get
    > > something from the previous post, but have no ideas? Any help?
    > >
    > > Named Ranges:
    > > City = B1:B1000
    > > Sold = Total Number Sold, C1:C1000
    > > TotalInv = Total Remaining in Stock, D1:D1000
    > >
    > > =SUM(IF(City="New York",Sold,0))/SUM(IF(City="New York",TotalInv,0))


  9. #9
    Duke Carey
    Guest

    RE: #div/0 Error

    Perhaps should have read your question a little more closely

    How about

    =IF(SUMIF(City,"New York",TotalInv)=0,0,SUMIF(City,"New
    York",Sold)/SUMIF(City,"New York",TotalInv))

    "Andy" wrote:

    > Like a previous post, trying to avoid the #div/0 error. I tried to get
    > something from the previous post, but have no ideas? Any help?
    >
    > Named Ranges:
    > City = B1:B1000
    > Sold = Total Number Sold, C1:C1000
    > TotalInv = Total Remaining in Stock, D1:D1000
    >
    > =SUM(IF(City="New York",Sold,0))/SUM(IF(City="New York",TotalInv,0))


  10. #10
    Max
    Guest

    Re: #div/0 Error

    Try this amended version of Duke's suggestion:

    =IF(SUM(IF(City="New York",TotalInv,0))=0,0,SUM(IF(City="New
    York",Sold,0))/SUM(IF(City="New York",TotalInv,0)))

    Alternatively, perhaps a slightly shorter version
    which returns blank: "" instead of zero:

    =IF(SUM(IF(City="New York",TotalInv))=0,"",SUM(IF(City="New
    York",Sold))/SUM(IF(City="New York",TotalInv)))
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----



  11. #11
    Andy
    Guest

    RE: #div/0 Error

    DUKE, YOU ROCK!!!!!!!!!

    Thanks a bunch. And I actually think I get how you did it as well.

    "Duke Carey" wrote:

    > Perhaps should have read your question a little more closely
    >
    > How about
    >
    > =IF(SUMIF(City,"New York",TotalInv)=0,0,SUMIF(City,"New
    > York",Sold)/SUMIF(City,"New York",TotalInv))
    >
    > "Andy" wrote:
    >
    > > Like a previous post, trying to avoid the #div/0 error. I tried to get
    > > something from the previous post, but have no ideas? Any help?
    > >
    > > Named Ranges:
    > > City = B1:B1000
    > > Sold = Total Number Sold, C1:C1000
    > > TotalInv = Total Remaining in Stock, D1:D1000
    > >
    > > =SUM(IF(City="New York",Sold,0))/SUM(IF(City="New York",TotalInv,0))


  12. #12
    Max
    Guest

    Re: #div/0 Error

    Sorry, forgot to mention that both formulas should be array-entered, i.e.
    press CTRL+SHIFT+ENTER (instead of just pressing ENTER)
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----



  13. #13
    Duke Carey
    Guest

    RE: #div/0 Error

    Perhaps should have read your question a little more closely

    How about

    =IF(SUMIF(City,"New York",TotalInv)=0,0,SUMIF(City,"New
    York",Sold)/SUMIF(City,"New York",TotalInv))

    "Andy" wrote:

    > Like a previous post, trying to avoid the #div/0 error. I tried to get
    > something from the previous post, but have no ideas? Any help?
    >
    > Named Ranges:
    > City = B1:B1000
    > Sold = Total Number Sold, C1:C1000
    > TotalInv = Total Remaining in Stock, D1:D1000
    >
    > =SUM(IF(City="New York",Sold,0))/SUM(IF(City="New York",TotalInv,0))


  14. #14
    Andy
    Guest

    RE: #div/0 Error

    Duke,
    Thanks for this. Unfortunately I'm getting an error that I'm missing a
    parenthesis in my formula.
    Andy

    "Duke Carey" wrote:

    > Maybe..
    >
    > =IF(SUM(IF(City="New York",TotalInv,0)=0,0,SUM(IF(City="New
    > York",Sold,0))/SUM(IF(City="New York",TotalInv,0)))
    >
    > "Andy" wrote:
    >
    > > Like a previous post, trying to avoid the #div/0 error. I tried to get
    > > something from the previous post, but have no ideas? Any help?
    > >
    > > Named Ranges:
    > > City = B1:B1000
    > > Sold = Total Number Sold, C1:C1000
    > > TotalInv = Total Remaining in Stock, D1:D1000
    > >
    > > =SUM(IF(City="New York",Sold,0))/SUM(IF(City="New York",TotalInv,0))


  15. #15
    Max
    Guest

    Re: #div/0 Error

    Try this amended version of Duke's suggestion:

    =IF(SUM(IF(City="New York",TotalInv,0))=0,0,SUM(IF(City="New
    York",Sold,0))/SUM(IF(City="New York",TotalInv,0)))

    Alternatively, perhaps a slightly shorter version
    which returns blank: "" instead of zero:

    =IF(SUM(IF(City="New York",TotalInv))=0,"",SUM(IF(City="New
    York",Sold))/SUM(IF(City="New York",TotalInv)))
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----



  16. #16
    Duke Carey
    Guest

    RE: #div/0 Error

    Maybe..

    =IF(SUM(IF(City="New York",TotalInv,0)=0,0,SUM(IF(City="New
    York",Sold,0))/SUM(IF(City="New York",TotalInv,0)))

    "Andy" wrote:

    > Like a previous post, trying to avoid the #div/0 error. I tried to get
    > something from the previous post, but have no ideas? Any help?
    >
    > Named Ranges:
    > City = B1:B1000
    > Sold = Total Number Sold, C1:C1000
    > TotalInv = Total Remaining in Stock, D1:D1000
    >
    > =SUM(IF(City="New York",Sold,0))/SUM(IF(City="New York",TotalInv,0))


  17. #17
    Andy
    Guest

    RE: #div/0 Error

    DUKE, YOU ROCK!!!!!!!!!

    Thanks a bunch. And I actually think I get how you did it as well.

    "Duke Carey" wrote:

    > Perhaps should have read your question a little more closely
    >
    > How about
    >
    > =IF(SUMIF(City,"New York",TotalInv)=0,0,SUMIF(City,"New
    > York",Sold)/SUMIF(City,"New York",TotalInv))
    >
    > "Andy" wrote:
    >
    > > Like a previous post, trying to avoid the #div/0 error. I tried to get
    > > something from the previous post, but have no ideas? Any help?
    > >
    > > Named Ranges:
    > > City = B1:B1000
    > > Sold = Total Number Sold, C1:C1000
    > > TotalInv = Total Remaining in Stock, D1:D1000
    > >
    > > =SUM(IF(City="New York",Sold,0))/SUM(IF(City="New York",TotalInv,0))


  18. #18
    Andy
    Guest

    #div/0 Error

    Like a previous post, trying to avoid the #div/0 error. I tried to get
    something from the previous post, but have no ideas? Any help?

    Named Ranges:
    City = B1:B1000
    Sold = Total Number Sold, C1:C1000
    TotalInv = Total Remaining in Stock, D1:D1000

    =SUM(IF(City="New York",Sold,0))/SUM(IF(City="New York",TotalInv,0))

  19. #19
    Max
    Guest

    Re: #div/0 Error

    Sorry, forgot to mention that both formulas should be array-entered, i.e.
    press CTRL+SHIFT+ENTER (instead of just pressing ENTER)
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----



+ 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