Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
What does -- mean in formulas
I have seen formulas that include two -'s. I am not sure what this does. Is
it just a way to add some spacing since two negatives equal a positive? Example: =SUMPRODUCT(--(A1:A100=1),--(B1:B100<"")) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
What does -- mean in formulas
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
-- Regards, Peo Sjoblom "illini_99" wrote in message ... I have seen formulas that include two -'s. I am not sure what this does. Is it just a way to add some spacing since two negatives equal a positive? Example: =SUMPRODUCT(--(A1:A100=1),--(B1:B100<"")) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
What does -- mean in formulas
On Nov 29, 7:23 am, illini_99
wrote: I have seen formulas that include two -'s. I am not sure what this does. Is it just a way to add some spacing since two negatives equal a positive? Example: =SUMPRODUCT(--(A1:A100=1),--(B1:B100<"")) Well, -A1 simply means "negative of A1"; so if A1 is 2, -A1 is -2. "-- A1" means "negative of negative of A1", which does not change the value of A1; for example --2 is 2. The question should be: why is that needed in the formula above? The answer is: in order to treat the boolean results as integers. For example, (A1:A100=1) results in the value TRUE or FALSE. Those are encoded as 1 and 0 respectively. But they are not recognized as integers unless they are used in arithmetic expression such as "-- expression". The "--" could have been avoided by coding the formula as follows, with the same effect: =SUMPRODUCT((A1:A100=1)*(B1:B100<"")) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
What does -- mean in formulas
"joeu2004" wrote in message ... On Nov 29, 7:23 am, illini_99 wrote: I have seen formulas that include two -'s. I am not sure what this does. Is it just a way to add some spacing since two negatives equal a positive? Example: =SUMPRODUCT(--(A1:A100=1),--(B1:B100<"")) Well, -A1 simply means "negative of A1"; so if A1 is 2, -A1 is -2. "-- A1" means "negative of negative of A1", which does not change the value of A1; for example --2 is 2. The question should be: why is that needed in the formula above? The answer is: in order to treat the boolean results as integers. For example, (A1:A100=1) results in the value TRUE or FALSE. Those are encoded as 1 and 0 respectively. But they are not recognized as integers unless they are used in arithmetic expression such as "-- expression". The "--" could have been avoided by coding the formula as follows, with the same effect: =SUMPRODUCT((A1:A100=1)*(B1:B100<"")) But there is a difference, the former uses SUMPRODUCT's built in way of dealing with the arrays thus if you for instance use =SUMPRODUCT((A1:A100=1)*(B1:B100<"")*(C1:C100)) vs. =SUMPRODUCT(--(A1:A100=1),--(B1:B100<""),C1:C100) to SUM what's in C and if the values in C can contain text like "" derived from formulas the former will throw an error while the latter will SUM the values ignoring any text blanks -- Regards, Peo Sjoblom |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
What does -- mean in formulas
On Nov 29, 8:20 am, "Peo Sjoblom" wrote:
"joeu2004" wrote in message The "--" could have been avoided by coding the formula as follows, with the same effect: =SUMPRODUCT((A1:A100=1)*(B1:B100<"")) But there is a difference, the former uses SUMPRODUCT's built in way of dealing with the arrays thus if you for instance use =SUMPRODUCT((A1:A100=1)*(B1:B100<"")*(C1:C100)) vs. =SUMPRODUCT(--(A1:A100=1),--(B1:B100<""),C1:C100) to SUM what's in C and if the values in C can contain text like "" derived from formulas the former will throw an error I should have written ``__in_this_case__ "--" could have been avoided``. And perhaps you should have written "__sometimes__ there is a difference". I don't believe there is a difference in the OP's example. And I would have written your counter-example correctly as: =SUMPRODUCT((A1:A100=1)*(B1:B100<""),C1:C100) Perhaps the counter-example you were struggling to think of is: =SUMPRODUCT(--(A1:A100=1),C1:C100) I agree that there are circumstances where it is incorrect to replace that with: =SUMPRODUCT((A1:A100=1)*C1:C100) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |