Avoid #div/0!
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. |
Avoid #div/0!
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. |
Avoid #div/0!
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 |
Avoid #div/0!
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. |
Avoid #div/0!
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. |
Avoid #div/0!
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. |
Avoid #div/0!
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 |
Avoid #div/0!
=(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. |
Avoid #div/0!
=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. |
All times are GMT +1. The time now is 04:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com