Double Dash Symbol in Excel Formulas
If you are using Excel formulas, you may have encountered the double dash symbol (–) in some functions like SUMPRODUCT. What does it do and why is it useful?
We will explain the meaning and purpose of the double dash symbol in Excel formulas.
The double dash symbol is also known as a double unary operator. It is used to convert TRUE and FALSE values to 1 and 0, respectively. This is useful when you want to perform arithmetic operations on logical expressions, such as comparisons or boolean functions.
For example, suppose you have a list of numbers in column A and you want to count how many of them are greater than 10. You can use the COUNTIF function like this:
=COUNTIF(A:A,”>10″)
But you can also use the SUMPRODUCT function with the double dash symbol like this:
=SUMPRODUCT(–(A:A>10))
How does this work? The expression A:A>10 returns an array of TRUE and FALSE values, depending on whether each cell in column A is greater than 10 or not. The double dash symbol then converts each TRUE value to 1 and each FALSE value to 0. The SUMPRODUCT function then adds up all the numbers in the array, which is equivalent to counting how many TRUE values there are.
The advantage of using the double dash symbol with SUMPRODUCT is that you can combine multiple logical expressions with arithmetic operators, such as +, -, *, /, etc. For example, if you want to count how many numbers in column A are between 5 and 15, you can use this formula:
=SUMPRODUCT(–(A:A>=5)*(A:A<=15)) This formula returns an array of 1s and 0s for each cell in column A that meets both conditions, and then sums them up. You can also use the double dash symbol with other functions that accept arrays as arguments, such as SUM, AVERAGE, MAX, MIN, etc. For example, if you want to calculate the average of the numbers in column A that are greater than 10, you can use this formula: =AVERAGE(–(A:A>10)*A:A)
This formula returns an array of numbers for each cell in column A that is greater than 10, and zeros for the rest. The AVERAGE function then ignores the zeros and calculates the average of the non-zero numbers.
The double dash symbol is a handy tool for working with logical expressions in Excel formulas. It can help you simplify your formulas and avoid using nested IF functions or helper columns. However, you should be careful not to use it with functions that already return numbers, such as COUNT or SUMIF, as it may cause errors or unexpected results.
