where variance is the named range D5:D15. In other words, the result is the sum of the values in D5:D15 converted to absolute values. See below for details about the other formulas that appear in this example.

Sum absolute variances

The formula in F6 calculates the sum of absolute variances with the ABS function together with the SUMPRODUCT function: In this formula, ABS returns the absolute value of variances to SUMPRODUCT in a single array: SUMPRODUCT then returns the sum, 975. Note: we use the SUMPRODUCT function here instead of the SUM function because SUMPRODUCT can handle many array operations natively without entering the formula in a special way. This means it will work in any version of Excel without special handling. See Why SUMPRODUCT for more information.

Count non-zero variance

The formula in F7 counts the number of absolute variances that are greater than zero (0): In this formula, ABS returns the absolute values for all variances in an array as explained above: A logical expression is used to check for variances greater than zero: This returns an array of TRUE and FALSE values: The double negative (–) converts the TRUE and FALSE values to 1s and 0s and the result is delivered directly to the SUMPRODUCT function: which returns a final result of 10.

Count positive and negative variances

The formula in F8 counts the number of positive variances: The formula in F9 counts negative variances:

Count absolute variance greater than 100

Finally, the formula in F10 counts absolute variances greater than 100:

Direct array operation

In the example shown, the variances in column D act as a helper column. However, you can calculate the variances directly in an array operation if needed with the same results. For example, to count positive variances, the formula in F8 is: This formula can be rewritten to calculate variance internally like this: The named range variance can be replaced with C5:C15-B5:B15 in all formulas above.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.