Avoid #div/0!

H

Harsh Bahal

Hi !

I want enter a sumproduct formula involving three ranges. However if any of
the range does not have a value the formula returns a #div/0! value. I want
to avoid that. Pl help.
 
P

Pecoflyer

Harsh said:
Hi !

I want enter a sumproduct formula involving three ranges. However i
any of
the range does not have a value the formula returns a #div/0! value.
want
to avoid that. Pl help.

I don't see how a sumproduct function could cause a #div/0! error?
Is there something in your formula where a division is involved

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste
 
H

Harsh Bahal

Suppose col A1 to A5 is rate, Col B1 to B5 is cases and col C1 to C5 case
weight.In cell C6 i have entered a formula
=(SUMPRODUCT(B1:B5,A1:A5)/(SUMPRODUCT(A1:A5,$C1:$C5)))

to get the per kilogram ( or pound) rate. Which does give the correct
answer, but if there are no figures entered in col A,B or C then the formula
returns #div/0!, which is i want avoid. I am working on 100 rows like this.
 
M

Mike H

Hi,

You could do this

=IF(ISERROR(SUMPRODUCT(B1:B5,A1:A5)/(SUMPRODUCT(A1:A5,$C1:$C5))),"Incomplete
Data",SUMPRODUCT(B1:B5,A1:A5)/(SUMPRODUCT(A1:A5,$C1:$C5)))

Mike
 
H

Harsh Bahal

Yes it did work Thanks a ton.
I forgot to mention that this formula is again multiplied by a constant
(cell reference), which I have done.
 
P

Pecoflyer

Harsh said:
Suppose col A1 to A5 is rate, Col B1 to B5 is cases and col C1 to C
case
weight.In cell C6 i have entered a formula
=(SUMPRODUCT(B1:B5,A1:A5)/(SUMPRODUCT(A1:A5,$C1:$C5)))

to get the per kilogram ( or pound) rate. Which does give the correct
answer, but if there are no figures entered in col A,B or C then th
formula
returns #div/0!, which is i want avoid. I am working on 100 rows lik
this.

It is the division that returns the error, not the function, then

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste
 
T

T. Valko

=(SUMPRODUCT(B1:B5,A1:A5)/(SUMPRODUCT(A1:A5,$C1:$C5)))

If you get a #DIV/0! error it's because this portion evaluates to 0:

SUMPRODUCT(A1:A5,$C1:$C5)

Try this:

=IF(SUMPRODUCT(A1:A5,$C1:$C5),SUMPRODUCT(B1:B5,A1:A5)/(SUMPRODUCT(A1:A5,$C1:$C5),"")
 
A

Aladin Akyurek

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,SUMPRODUCT(B1:B5,A1:A5)/SUMPRODUCT(A1:A5,$C1:$C5)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top