where amount is the named range C5:C14, and type is the named range D5:D14, and group is the named range B5:B14. 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: 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: The variance percentage formula in column J is: with percentage number format applied.
Notes
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.
