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.

Post your sumproduct formula and a description of what your doing

Mike

"Harsh Bahal" wrote:

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.

Harsh Bahal;258166 Wrote:
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.

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?


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=72081

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.

"Mike H" wrote:

Post your sumproduct formula and a description of what your doing

Mike

"Harsh Bahal" wrote:

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.

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

"Harsh Bahal" wrote:

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.

"Mike H" wrote:

Post your sumproduct formula and a description of what your doing

Mike

"Harsh Bahal" wrote:

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.

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.


"Mike H" wrote:

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

"Harsh Bahal" wrote:

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.

"Mike H" wrote:

Post your sumproduct formula and a description of what your doing

Mike

"Harsh Bahal" wrote:

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.

Harsh Bahal;258207 Wrote:
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.

"Mike H" wrote:

Post your sumproduct formula and a description of what your doing

Mike

"Harsh Bahal" wrote:

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.


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


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=72081

=(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),"")

--
Biff
Microsoft Excel MVP


"Harsh Bahal" wrote in message
...
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.

"Mike H" wrote:

Post your sumproduct formula and a description of what your doing

Mike

"Harsh Bahal" wrote:

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.

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

Harsh Bahal wrote:
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.

"Mike H" wrote:

Post your sumproduct formula and a description of what your doing

Mike

"Harsh Bahal" wrote:

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.