#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))

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))

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))

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))

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 <atyahoo<dotcom
----

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))

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 <atyahoo<dotcom
----