Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT- double unary vs. *
This is continuation from another thread.
Following is an excerpt from Ken's post. ************************************************** ************************************************** ******** With the following data in A1:B5 abc def 1 2 3 4 2 3 4 5 Try each of the following formulas:- =SUMPRODUCT(A1:A5*B1:B5) =SUMPRODUCT(A1:A5,B1:B5) First one fails, second one doesn't. ************************************************** ************************************************** ********* My new post: Ken's above example has both text and numbers in the data set and at least double unary/comma works ...... I also have text and numbers but this time neither double unary/comma nor * works. =SUMPRODUCT(--(A2:A6="AA"),--(C2:C6)) =SUMPRODUCT((A2:A6="AA")*(C2:C6)) Both returned #VALUE!. If I remove the text from the data set, both formulae work fine. I fix the error with the following formula:- =SUMPRODUCT((A2:A6="AA")*(C2:C6<""),C2:C6) To my surprise, checking for <"" takes care of *all text* and not just null. The formula seems to do well regardless of whether the text in column C corresponds to AA in column A or not. I did evaluate formula, but couldn't explain why =SUMPRODUCT((A2:A6="AA")*(C2:C6)) won't work and =SUMPRODUCT((A2:A6="AA")*(C2:C6<""),C2:C6) works. One set of TRUE, FALSE ..... gives an error while multiplying two sets of TRUE, FALSE solves the problem. I am missing something here. Please explain. Epinn |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT- double unary vs. *
One set of TRUE, FALSE ..... gives an error while multiplying two sets of TRUE, FALSE solves the problem. <<
So, is coercing in play here and it solves the problem? I am not convinced. I did try to insert double unary before the argument checking for "AA" but I still got the error. I had to check for null. Epinn "Epinn" wrote in message ... This is continuation from another thread. Following is an excerpt from Ken's post. ************************************************** ************************************************** ******** With the following data in A1:B5 abc def 1 2 3 4 2 3 4 5 Try each of the following formulas:- =SUMPRODUCT(A1:A5*B1:B5) =SUMPRODUCT(A1:A5,B1:B5) First one fails, second one doesn't. ************************************************** ************************************************** ********* My new post: Ken's above example has both text and numbers in the data set and at least double unary/comma works ...... I also have text and numbers but this time neither double unary/comma nor * works. =SUMPRODUCT(--(A2:A6="AA"),--(C2:C6)) =SUMPRODUCT((A2:A6="AA")*(C2:C6)) Both returned #VALUE!. If I remove the text from the data set, both formulae work fine. I fix the error with the following formula:- =SUMPRODUCT((A2:A6="AA")*(C2:C6<""),C2:C6) To my surprise, checking for <"" takes care of *all text* and not just null. The formula seems to do well regardless of whether the text in column C corresponds to AA in column A or not. I did evaluate formula, but couldn't explain why =SUMPRODUCT((A2:A6="AA")*(C2:C6)) won't work and =SUMPRODUCT((A2:A6="AA")*(C2:C6<""),C2:C6) works. One set of TRUE, FALSE ..... gives an error while multiplying two sets of TRUE, FALSE solves the problem. I am missing something here. Please explain. Epinn |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT- double unary vs. *
I did try to insert double unary before the argument checking for "AA" but I still got the error. <<
This is because I didn't change the * to , (comma). Thanks to Roger, the following formula works with null. =SUMPRODUCT(--(A2:A6="AA"),C2:C6) This brings back Ken's point of * doesn't work and , works. The more I play with -- , * the more confused I am and don't know when to use which. It is trial an error. Still don't understand why these two =SUMPRODUCT(--(A2:A6="AA"),--(C2:C6)) =SUMPRODUCT((A2:A6="AA")*(C2:C6)) won't work with null and why I have to write the formula this way: =SUMPRODUCT(--(A2:A6="AA"),C2:C6) Epinn "Epinn" wrote in message ... One set of TRUE, FALSE ..... gives an error while multiplying two sets of TRUE, FALSE solves the problem. << So, is coercing in play here and it solves the problem? I am not convinced. I did try to insert double unary before the argument checking for "AA" but I still got the error. I had to check for null. Epinn "Epinn" wrote in message ... This is continuation from another thread. Following is an excerpt from Ken's post. ************************************************** ************************************************** ******** With the following data in A1:B5 abc def 1 2 3 4 2 3 4 5 Try each of the following formulas:- =SUMPRODUCT(A1:A5*B1:B5) =SUMPRODUCT(A1:A5,B1:B5) First one fails, second one doesn't. ************************************************** ************************************************** ********* My new post: Ken's above example has both text and numbers in the data set and at least double unary/comma works ...... I also have text and numbers but this time neither double unary/comma nor * works. =SUMPRODUCT(--(A2:A6="AA"),--(C2:C6)) =SUMPRODUCT((A2:A6="AA")*(C2:C6)) Both returned #VALUE!. If I remove the text from the data set, both formulae work fine. I fix the error with the following formula:- =SUMPRODUCT((A2:A6="AA")*(C2:C6<""),C2:C6) To my surprise, checking for <"" takes care of *all text* and not just null. The formula seems to do well regardless of whether the text in column C corresponds to AA in column A or not. I did evaluate formula, but couldn't explain why =SUMPRODUCT((A2:A6="AA")*(C2:C6)) won't work and =SUMPRODUCT((A2:A6="AA")*(C2:C6<""),C2:C6) works. One set of TRUE, FALSE ..... gives an error while multiplying two sets of TRUE, FALSE solves the problem. I am missing something here. Please explain. Epinn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add excel horizontal & vertical ruler | Excel Worksheet Functions | |||
Excel CSV file: How to preserve double quotation mark on Unix ftp? | Excel Discussion (Misc queries) | |||
double unary | Excel Discussion (Misc queries) | |||
Double clicking in a Pivot Table, Please HELP | Excel Discussion (Misc queries) | |||
adding two sumproduct formulas together | Excel Worksheet Functions |