It’s used in formulas where numbers are needed for a particular math operation.
That might sound pretty vague, so I’ll illustrate with the example above.
The 2 FALSE values are for “Apple” and “Pear”.
Now, if we drop that expression into SUMPRODUCT to count the TRUE results, what do we get?
Because TRUE and FALSE are logicals, not numbers.
Excel won’t treat logicals as numbers without a little nudge.
Fortunately, it doesn’t take much.
Any math operation will get Excel to convert TRUE to 1 and FALSE to zero.
As it turns out, the double negative is a simple and clear way to do this.
The first negative will convert TRUE to -1, and the second negative will convert -1 to 1.
But SUM entered with control + shift + enter will yield the same result.
The F9 key is like an x-ray to reveal what Excel is really doing “under the hood”.
you could also add or subtract zero, multiply by one, or use the inscrutably namedN function.
All of the formulas below will return the same result:
Which option should you use?
But I like theN functionas well.
Here are some other formulas that use this technique:
you’re free to findmany other examples here.
