Note the only change to the formula is the logical operator. To change the formula to count numbers with decimal values, we only need to change the logical operator in the MOD snippet from an equal sign (=) to the not equal to () operator. With just one array to process, SUMPRODUCT returns the sum of all items in the array, 7, which is the count of whole numbers in the range C5:C15. This array is returned directly to the SUMPRODUCT function: This is exactly what we need to count whole numbers. This operation returns a numeric array composed only of 1s and 0s: To do this, we use a double-negative (–): Next, we need to convert the TRUE and FALSE values to 1s and 0s. In this array, TRUE values represent a whole number, and FALSE values represent a decimal number. Working from the inside out, we first run all values through the MOD test shown above:īecause there are eleven values in shares (C5:C15), we get eleven results in an array like this: In the example shown, this is what the formula in G5 does: Boolean logic is a technique for building formulas that take advantage of the fact that TRUE = 1, and FALSE = 0 in math operations. Instead, we need a way to work with the array directly with Boolean logic.
0 Comments
Leave a Reply. |