It's also very commonly used in SUMPRODUCT().



SUMPRODUCT() can only process numbers; you'll often find that you have a situation where you have an array of booleans which need to be coerced into numbers so that SUMPRODUCT() can use them. This can be done by performing a simple arithmetic operation on them. For example:

TRUE+0 coerces TRUE to 1
TRUE-0 coerces TRUE to 1
TRUE*1 coerces TRUE to 1
TRUE/1 coerces TRUE to 1

(and many more).

You'll be familiar with addition, subtraction, multiplication and division as shown above. There's also negation which is achieved by applying the unary minus operator:

-TRUE coerces TRUE to -1
--1 gives 1

so

--TRUE coerces TRUE to 1



When people choose how to do the coercion they could use any of these options, but many people decide to use two unary minus operators. This is for 2 reasons:
(1) Unary minus sits high up on the operator precedent list
(2) Double negation is marginally faster to calculate than the other options


Some further reading for you:
SUMPRODUCT
Array Formulas (see particularly post #5)


Hope that helps...
Colin