| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Making 'True' =1 "false" =0
Is there a setting that will automatically apply these at the moment I have
to apply an if statement before I can get any calculations done. =SUMPRODUCT($C$4:$I$4=C4,$C$5:$I$50) gives a result of 0 even though it shows =SUMPRODUCT({TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FAL SE},{TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE}) whereas if I do =SUMPRODUCT(IF($C$4:$I$4=C4,1,0),IF($C$5:$I$50,1, 0)) gives the results of 1 with the array of =SUMPRODUCT({1,0,0,0,0,1,0},{1,1,1,1,1,0,1}) It seems to treat the trues and falses as text rather than values. I do not want to have to put the if statement in. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making 'True' =1 "false" =0
Try it like this:
=SUMPRODUCT(--($C$4:$I$4=C4),--($C$5:$I$50)) -- Biff Microsoft Excel MVP "ASA" wrote in message ... Is there a setting that will automatically apply these at the moment I have to apply an if statement before I can get any calculations done. =SUMPRODUCT($C$4:$I$4=C4,$C$5:$I$50) gives a result of 0 even though it shows =SUMPRODUCT({TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FAL SE},{TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE}) whereas if I do =SUMPRODUCT(IF($C$4:$I$4=C4,1,0),IF($C$5:$I$50,1, 0)) gives the results of 1 with the array of =SUMPRODUCT({1,0,0,0,0,1,0},{1,1,1,1,1,0,1}) It seems to treat the trues and falses as text rather than values. I do not want to have to put the if statement in. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making 'True' =1 "false" =0
On Sun, 11 Nov 2007 20:38:01 -0800, ASA wrote:
Is there a setting that will automatically apply these at the moment I have to apply an if statement before I can get any calculations done. =SUMPRODUCT($C$4:$I$4=C4,$C$5:$I$50) gives a result of 0 even though it shows =SUMPRODUCT({TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FA LSE},{TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE}) whereas if I do =SUMPRODUCT(IF($C$4:$I$4=C4,1,0),IF($C$5:$I$50,1, 0)) gives the results of 1 with the array of =SUMPRODUCT({1,0,0,0,0,1,0},{1,1,1,1,1,0,1}) It seems to treat the trues and falses as text rather than values. I do not want to have to put the if statement in. =SUMPRODUCT(($C$4:$I$4=C4)*($C$5:$I$50)) or =SUMPRODUCT(--($C$4:$I$4=C4),--($C$5:$I$50)) --ron |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making 'True' =1 "false" =0
Thank you , I assume that the first - somehow multiplies by - 1 and the
second one reverses it to make it positive again. "T. Valko" wrote: Try it like this: =SUMPRODUCT(--($C$4:$I$4=C4),--($C$5:$I$50)) -- Biff Microsoft Excel MVP "ASA" wrote in message ... Is there a setting that will automatically apply these at the moment I have to apply an if statement before I can get any calculations done. =SUMPRODUCT($C$4:$I$4=C4,$C$5:$I$50) gives a result of 0 even though it shows =SUMPRODUCT({TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FAL SE},{TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE}) whereas if I do =SUMPRODUCT(IF($C$4:$I$4=C4,1,0),IF($C$5:$I$50,1, 0)) gives the results of 1 with the array of =SUMPRODUCT({1,0,0,0,0,1,0},{1,1,1,1,1,0,1}) It seems to treat the trues and falses as text rather than values. I do not want to have to put the if statement in. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making 'True' =1 "false" =0
I assume that the first - somehow multiplies by - 1
I don't know how Excel does this internally but it "coerces" the booleans first to negative numbers and then to positive numbers. Does it multiply by - 1, that I don't know. -- Biff Microsoft Excel MVP "ASA" wrote in message ... Thank you , I assume that the first - somehow multiplies by - 1 and the second one reverses it to make it positive again. "T. Valko" wrote: Try it like this: =SUMPRODUCT(--($C$4:$I$4=C4),--($C$5:$I$50)) -- Biff Microsoft Excel MVP "ASA" wrote in message ... Is there a setting that will automatically apply these at the moment I have to apply an if statement before I can get any calculations done. =SUMPRODUCT($C$4:$I$4=C4,$C$5:$I$50) gives a result of 0 even though it shows =SUMPRODUCT({TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FAL SE},{TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE}) whereas if I do =SUMPRODUCT(IF($C$4:$I$4=C4,1,0),IF($C$5:$I$50,1, 0)) gives the results of 1 with the array of =SUMPRODUCT({1,0,0,0,0,1,0},{1,1,1,1,1,0,1}) It seems to treat the trues and falses as text rather than values. I do not want to have to put the if statement in. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making 'True' =1 "false" =0
Yes.
-- David Biddulph "ASA" wrote in message ... Thank you , I assume that the first - somehow multiplies by - 1 and the second one reverses it to make it positive again. "T. Valko" wrote: Try it like this: =SUMPRODUCT(--($C$4:$I$4=C4),--($C$5:$I$50)) "ASA" wrote in message ... Is there a setting that will automatically apply these at the moment I have to apply an if statement before I can get any calculations done. =SUMPRODUCT($C$4:$I$4=C4,$C$5:$I$50) gives a result of 0 even though it shows =SUMPRODUCT({TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FAL SE},{TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE}) whereas if I do =SUMPRODUCT(IF($C$4:$I$4=C4,1,0),IF($C$5:$I$50,1, 0)) gives the results of 1 with the array of =SUMPRODUCT({1,0,0,0,0,1,0},{1,1,1,1,1,0,1}) It seems to treat the trues and falses as text rather than values. I do not want to have to put the if statement in. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making 'True' =1 "false" =0
Unlikely that it multiplies by -1, but it does negate the value, which has
the same effect, and the second negates again, a negative-negative is a positive. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "ASA" wrote in message ... Thank you , I assume that the first - somehow multiplies by - 1 and the second one reverses it to make it positive again. "T. Valko" wrote: Try it like this: =SUMPRODUCT(--($C$4:$I$4=C4),--($C$5:$I$50)) -- Biff Microsoft Excel MVP "ASA" wrote in message ... Is there a setting that will automatically apply these at the moment I have to apply an if statement before I can get any calculations done. =SUMPRODUCT($C$4:$I$4=C4,$C$5:$I$50) gives a result of 0 even though it shows =SUMPRODUCT({TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FAL SE},{TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE}) whereas if I do =SUMPRODUCT(IF($C$4:$I$4=C4,1,0),IF($C$5:$I$50,1, 0)) gives the results of 1 with the array of =SUMPRODUCT({1,0,0,0,0,1,0},{1,1,1,1,1,0,1}) It seems to treat the trues and falses as text rather than values. I do not want to have to put the if statement in. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Check if cells contain the word "Thailand", return "TRUE" | Excel Worksheet Functions | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| Typing "true" excel 2007 change it to "TRUE" | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| Can you replace "TRUE" with " " in an exact formula? | Excel Worksheet Functions | |||
Linear Mode
