How to Calculate Percent Variance in Excel


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’s 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 diving the result by the benchmark number.

In this example, the calculation looks like this: (150-120)/120 = 25%. This calculation can be simplified as 150/120-1 = 25%.

In some scenarios, the benchmark value is negative, under such situation, The above formula breaks down. To fix this, you have to use ABS function to negate the negative benchmark value. The correct formula is (D6-C6)/ABS(C6).


Comments are closed.