Summary

To calculate forecast versus actual variance based on a set of data, you can use can use the SUMIFS function to gather up totals, and basic other formulas to calculate variance and variance percentage. In the example shown, the formula in G5 is:

=SUMIFS(amount,type,G$4,group,$F5)

where amount is the named range C5:C14, and type is the named range D5:D14, and group is the named range B5:B14.

Explanation 

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 G4 is a mixed reference with the row locked in order to match the column header in row 4 when the formula is copied down.

To sum by group, the range/criteria pair is:

group,$F5

where group is the named range B5:B14, and F5 is a mixed reference with the column locked in order to match group names in column F when the formula is copied across.

Variance formulas

To calculate a variance amount in column I, the formula simply subtracts the forecast from the actual:

=G5-H5

The variance percentage formula in column J is:

=(G5-H5)/H5

with percentage number format applied.

Notes

  1. The data shown here would work well in an Excel Table, which would automatically expand to include new data. We are using named ranges here to keep the formulas as simple as possible.
  2. Pivot tables can also be used to calculate variance. Formulas provide more flexibility and control at the cost of more complexity.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.