Exceljet

Quick, clean, and to the point

Forecast vs actual variance

Excel formula: Forecast vs actual variance
Explanation 

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.

    How this formula works

    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

    The variance formula in column I simply subtracts forecast from 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.
    Author 
    Dave Bruns

    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.