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.
In this example, the goal is to sum or count a set of variances in different ways. Variances are listed in D5:D15, which is also the named rangevariance. The first formula in F5 simply sums all variances with the SUM function.
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. You can use SUM instead, but you'll need to enter with control + shift + enter unless you are using Excel 365 where array behavior is native and no special treatment is necessary.
Count non-zero variance
The formula in F7 counts the number of absolute variances that are greater than zero (0):
In the example as 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 is a pretty standard use of the SUMIFS function. In this case, we need to sum amounts based on two criteria: type (forecast or actual) and group. To sum by type, the range/criteria pair is: type , G$4 where type is the named range D5:D14, and...
In this example, the goal is to calculate the variance between a Forecast (column C) and Actual (column D) as a percentage. For example, with a Forecast value of 100,000 and an Actual value of 112,000, we want to return a variance of 12%. The...
The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.
The Excel ABS function returns the absolute value of a number. ABS converts negative numbers to positive numbers, and positive numbers are unaffected.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.