6
1
I found (--) this to add criteria in a sum Function, can you please tell me its exact use. And is this function(--) (whatever it is called by) can be used in any other formula?
6
1
I found (--) this to add criteria in a sum Function, can you please tell me its exact use. And is this function(--) (whatever it is called by) can be used in any other formula?
7
It is not a standalone function, just two minus signs next to each other.
It is used to convert boolean (TRUE / FALSE) values to number, as those not always evaluate correctly in formulas.
First -
converts TRUE to -1
, and second one changes it to 1
.
First -
converts FALSE to 0
(, and second one doesn't change it).
Can You also tell me the list of formula in which it can be used. Like one of them is SUMPRODUCT (I know only this one formula).
--
is just two operators next to each other, "in which it can be used" question isn't really applicable to it, the right question would be to which data type it can be applied:
--TRUE
) it convert it to a number as explain above. Applying it on boolean it has a same output as IF(<input>,1,0)
, but it's just shorter--5
) it returns the original number--"abc"
) it returns #VALUE
error (like all other arithmetic operators)SUMPRODUCT
is not an example "where it can be used", but an example, "where it's practical to use", you can use it in any functions, but either you generally don't need it, either people use IF
instead.
One example for its use within SUM
to count even numbers in a range:
=SUM(--(MOD(E1:E4,2)=0))
(note that it's an array formula, so press CTRL+SHIFT+ENTER when you've entered it.)
0
Thanx Juhasz for your answer. I get what I asked for. Can You also tell me the list of formula in which it can be used. Like one of them is SUMPRODUCT(I know only this one formula). Please update me from other formulas. – Sarthak Gupta – 2016-06-16T11:00:52.543
1@SarthakGupta please see my update – Máté Juhász – 2016-06-17T05:09:03.440