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

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

>=(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" <(E-Mail Removed)> wrote in message
news:1B12B509-4A3C-4B77-A6A3-(E-Mail Removed)...
> 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,SUMPRODUCT(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.
>>>