Formula Excel : Calculating Percent Variance

A variance is an indicator of the difference between one number and another. To understand this, imagine that you sold 120 widgets one day, and on the next day, you sold 150. The difference in sales in actual terms is easy to see; you sold 30 more widgets. Subtracting 120 widgets from 150 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, the calculation looks like this:
(150-120)/120 = 25%. The Percent variance tells you that you sold 25 percent more widgets than yesterday.

Image 2-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)/C4

Image 2-3. Calculate Percent Variance

How it works

The 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 you let that happen, you would get an erroneous result. Wrapping the first part of the formula in parentheses ensures that Excel performs the 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.

Ref : For more information on the order of operator precedence see article Tutorial Excel : Using Formula Operators.

Alternative: Simplified percent variance calculation

An alternative formula for calculating percent variance is to simply divide the current year sales by the previous year sales and then subtract 1. Because Excel performs division operations before subtraction, you don’t have to use parentheses with this alternative formula.

=D4/C4-1

Categories: Formula