| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct with boolean criteria returns unexpected 0
Hi all -
My sumproduct formula is measuring 2 criteria -Values < 0? -Period = P1 But it is returning a #N/A error Can anyone give me a nudge in the right direction? =SUMPRODUCT((Data!$D$2:$D$20000)*(Data!$E$2:$E$20 00=Summary!$K$2))/ 1000 Thanks. Best regards, markc |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct with boolean criteria returns unexpected 0
It works for me. Have a look at the data itself, has it been imported from
somewhere? It's hard to say without looking at it but your formula is sound. Regards, Alan. "goss" wrote in message oups.com... Hi all - My sumproduct formula is measuring 2 criteria -Values < 0? -Period = P1 But it is returning a #N/A error Can anyone give me a nudge in the right direction? =SUMPRODUCT((Data!$D$2:$D$20000)*(Data!$E$2:$E$20 00=Summary!$K$2))/ 1000 Thanks. Best regards, markc |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct with boolean criteria returns unexpected 0
Hmmm...
Well, your subject line says you're getting a result of 0 and your post says you're getting a result of #N/A. If the result is 0: -Values < 0? (Data!$D$2:$D$20000) It may be due to use of the wrong operator. or < ? If the result is #N/A: There's nothing wrong with the formula itself. Are there any #N/A errors in either of the ranges? -- Biff Microsoft Excel MVP "goss" wrote in message oups.com... Hi all - My sumproduct formula is measuring 2 criteria -Values < 0? -Period = P1 But it is returning a #N/A error Can anyone give me a nudge in the right direction? =SUMPRODUCT((Data!$D$2:$D$20000)*(Data!$E$2:$E$20 00=Summary!$K$2))/ 1000 Thanks. Best regards, markc |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct with boolean criteria returns unexpected 0
On Oct 27, 6:17 pm, "T. Valko" wrote:
Hmmm... Well, your subject line says you're getting a result of 0 and your post says you're getting a result of #N/A. If the result is 0: -Values < 0? (Data!$D$2:$D$20000) It may be due to use of the wrong operator. or < ? If the result is #N/A: There's nothing wrong with the formula itself. Are there any #N/A errors in either of the ranges? -- Biff Microsoft Excel MVP "goss" wrote in message oups.com... Hi all - My sumproduct formula is measuring 2 criteria -Values < 0? -Period = P1 But it is returning a #N/A error Can anyone give me a nudge in the right direction? =SUMPRODUCT((Data!$D$2:$D$20000)*(Data!$E$2:$E$20 00=Summary!$K$2))/ 1000 Thanks. Best regards, markc- Hide quoted text - - Show quoted text - Apolgies on confusion with subect and body Originally I was getting #N/A, then relized the second argument in the formula did not use $2000 rows only $45 rows I change to $2000 and now I receive a 0 Alan - The data has been copy pasted to Excel from HTML then data..text..columns I also ran a macro to clean all extra spaces and another macro to bring any trailing negatives to the fron of the number I tested the value column with =ISNUMBER(). All returned TRUE. I then picked a single period of the dataset and switched to a very simple sumif formula: =SUMIF(Data!$D$24:$D$45,"<0")/1000 This returned a value of 2.0 (K's) I then manually added all items in the range < 0. I received a result of $2,011.65. All that said, I believe the data is good to go. Has to be something with the formula Best regards, -markc |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct with boolean criteria returns unexpected 0
"goss" wrote in message
ps.com... On Oct 27, 6:17 pm, "T. Valko" wrote: Hmmm... Well, your subject line says you're getting a result of 0 and your post says you're getting a result of #N/A. If the result is 0: -Values < 0? (Data!$D$2:$D$20000) It may be due to use of the wrong operator. or < ? If the result is #N/A: There's nothing wrong with the formula itself. Are there any #N/A errors in either of the ranges? -- Biff Microsoft Excel MVP "goss" wrote in message oups.com... Hi all - My sumproduct formula is measuring 2 criteria -Values < 0? -Period = P1 But it is returning a #N/A error Can anyone give me a nudge in the right direction? =SUMPRODUCT((Data!$D$2:$D$20000)*(Data!$E$2:$E$20 00=Summary!$K$2))/ 1000 Thanks. Best regards, markc- Hide quoted text - - Show quoted text - Apolgies on confusion with subect and body Originally I was getting #N/A, then relized the second argument in the formula did not use $2000 rows only $45 rows I change to $2000 and now I receive a 0 Alan - The data has been copy pasted to Excel from HTML then data..text..columns I also ran a macro to clean all extra spaces and another macro to bring any trailing negatives to the fron of the number I tested the value column with =ISNUMBER(). All returned TRUE. I then picked a single period of the dataset and switched to a very simple sumif formula: =SUMIF(Data!$D$24:$D$45,"<0")/1000 This returned a value of 2.0 (K's) I then manually added all items in the range < 0. I received a result of $2,011.65. All that said, I believe the data is good to go. Has to be something with the formula Best regards, -markc The formula is syntactically correct so it has to be a data problem. If the data in Data!$D$2:$D$2000 all checked out OK to be numbers then what about the other range Data!$E$2:$E$2000 and Summary!$K$2. You said you ran a macro to "clean all extra spaces". Does that macro clean the char 160 non breaking spaces commonly found in html? There is a macro at this location that cleans those char 160's: http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Biff Microsoft Excel MVP |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct with boolean criteria returns unexpected 0
On Oct 27, 7:24 pm, "T. Valko" wrote:
"goss" wrote in message ps.com... On Oct 27, 6:17 pm, "T. Valko" wrote: Hmmm... Well, your subject line says you're getting a result of 0 and your post says you're getting a result of #N/A. If the result is 0: -Values < 0? (Data!$D$2:$D$20000) It may be due to use of the wrong operator. or < ? If the result is #N/A: There's nothing wrong with the formula itself. Are there any #N/A errors in either of the ranges? -- Biff Microsoft Excel MVP "goss" wrote in message groups.com... Hi all - My sumproduct formula is measuring 2 criteria -Values < 0? -Period = P1 But it is returning a #N/A error Can anyone give me a nudge in the right direction? =SUMPRODUCT((Data!$D$2:$D$20000)*(Data!$E$2:$E$20 00=Summary!$K$2))/ 1000 Thanks. Best regards, markc- Hide quoted text - - Show quoted text - Apolgies on confusion with subect and body Originally I was getting #N/A, then relized the second argument in the formula did not use $2000 rows only $45 rows I change to $2000 and now I receive a 0 Alan - The data has been copy pasted to Excel from HTML then data..text..columns I also ran a macro to clean all extra spaces and another macro to bring any trailing negatives to the fron of the number I tested the value column with =ISNUMBER(). All returned TRUE. I then picked a single period of the dataset and switched to a very simple sumif formula: =SUMIF(Data!$D$24:$D$45,"<0")/1000 This returned a value of 2.0 (K's) I then manually added all items in the range < 0. I received a result of $2,011.65. All that said, I believe the data is good to go. Has to be something with the formula Best regards, -markc The formula is syntactically correct so it has to be a data problem. If the data in Data!$D$2:$D$2000 all checked out OK to be numbers then what about the other range Data!$E$2:$E$2000 and Summary!$K$2. You said you ran a macro to "clean all extra spaces". Does that macro clean the char 160 non breaking spaces commonly found in html? There is a macro at this location that cleans those char 160's: http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Biff Microsoft Excel MVP- Hide quoted text - - Show quoted text - Thanks Biff, It is Dave McRitchie;s Trimall macro I use after converting data from HTML,.txt, and .pdf Originally the formula was a sumif formula, I edited into the sumproduct formula. I went to the data sheet below the data and wrote a sumproduct formula from scratch which worked I went back to the summary sheet and wrote a sumproduct formula from scratch which worked. I guess the lesson learned is if you don't trust the results, start over from scratch Thanks Best regards, -markc |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct with boolean criteria returns unexpected 0
"goss" wrote in message
ps.com... On Oct 27, 7:24 pm, "T. Valko" wrote: "goss" wrote in message ps.com... On Oct 27, 6:17 pm, "T. Valko" wrote: Hmmm... Well, your subject line says you're getting a result of 0 and your post says you're getting a result of #N/A. If the result is 0: -Values < 0? (Data!$D$2:$D$20000) It may be due to use of the wrong operator. or < ? If the result is #N/A: There's nothing wrong with the formula itself. Are there any #N/A errors in either of the ranges? -- Biff Microsoft Excel MVP "goss" wrote in message groups.com... Hi all - My sumproduct formula is measuring 2 criteria -Values < 0? -Period = P1 But it is returning a #N/A error Can anyone give me a nudge in the right direction? =SUMPRODUCT((Data!$D$2:$D$20000)*(Data!$E$2:$E$20 00=Summary!$K$2))/ 1000 Thanks. Best regards, markc- Hide quoted text - - Show quoted text - Apolgies on confusion with subect and body Originally I was getting #N/A, then relized the second argument in the formula did not use $2000 rows only $45 rows I change to $2000 and now I receive a 0 Alan - The data has been copy pasted to Excel from HTML then data..text..columns I also ran a macro to clean all extra spaces and another macro to bring any trailing negatives to the fron of the number I tested the value column with =ISNUMBER(). All returned TRUE. I then picked a single period of the dataset and switched to a very simple sumif formula: =SUMIF(Data!$D$24:$D$45,"<0")/1000 This returned a value of 2.0 (K's) I then manually added all items in the range < 0. I received a result of $2,011.65. All that said, I believe the data is good to go. Has to be something with the formula Best regards, -markc The formula is syntactically correct so it has to be a data problem. If the data in Data!$D$2:$D$2000 all checked out OK to be numbers then what about the other range Data!$E$2:$E$2000 and Summary!$K$2. You said you ran a macro to "clean all extra spaces". Does that macro clean the char 160 non breaking spaces commonly found in html? There is a macro at this location that cleans those char 160's: http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Biff Microsoft Excel MVP- Hide quoted text - - Show quoted text - Thanks Biff, It is Dave McRitchie;s Trimall macro I use after converting data from HTML,.txt, and .pdf Originally the formula was a sumif formula, I edited into the sumproduct formula. I went to the data sheet below the data and wrote a sumproduct formula from scratch which worked I went back to the summary sheet and wrote a sumproduct formula from scratch which worked. I guess the lesson learned is if you don't trust the results, start over from scratch Thanks Best regards, -markc Glad to hear you got it straightened out. Thanks for the feedback! -- Biff Microsoft Excel MVP |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using booleans in sumproduct formulas to extract boolean range | Excel Worksheet Functions | |||
| Database Function Criteria Boolean Operations | Excel Worksheet Functions | |||
| SumProduct Returns Zero | Excel Worksheet Functions | |||
| sumproduct returns zero | Excel Worksheet Functions | |||
| boolean find criteria in Excel | Excel Discussion (Misc queries) | |||
![goss[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
