Chapter 10: Using Formulas for Common Mathematical Operations . . . . . . . . . . . . . . .
10
Calculating percent variance
A variance is an indicator of the difference between one number and another. To under-
stand this, imagine that you sold 120 widgets one day, and on the next day, you sold 150
widgets. The difference in sales in actual terms is easy to see; you sold 30 more widgets on
the second day. 150 widgets minus 120 widgets gives you a unit variance of +30.So, what is a percent variance? This is essentially the percentage difference between the
benchmark number (120) and the new number (150). You calculate the percent variance by
subtracting the benchmark number from the new number and then dividing that result by
the benchmark number. In this example, (150-120)/120 = 25%. The percent variance tells us
that you sold 25% more widgets than on the previous day.Figure 10.3 demonstrates how to translate this into a formula. The formula in E4 calculates
the percent variance between current year sales and previous year sales.
=(D4-C4)/C4FIGURE 10.3
Calculating the percent variance between current year sales and previous year salesThe one thing to note about this formula is the use of parentheses. By default, Excel’s order
of operations states that division must be done before subtraction. But if we let that hap-
pen, we would get an erroneous result. Wrapping the first part of the formula in parenthe-
ses ensures that Excel performs subtraction before the division.You can simply enter the formula one time in the first row (cell E4 in this case) and then
copy that formula down to every other row in your table.See Chapter 9, “Introducing Formulas and Functions,” for a detailed explanation of the order of opera-
tor precedence.An alternative formula for calculating percent variance is simply to divide the current year
sales by the previous year sales and then subtract 1. Because Excel performs division opera-
tions before subtraction, you don’t have to use parentheses with this alternative formula.
=D4/C4-1