Calculate percent variance
To calculate a percent variance, subtract the original (baseline) number from the new number, then divide that result by the original. In the example shown, the formula in E5, copied down, is:
The results in column E are decimal values with the percentage number format applied. The same formula can be used to calculate things like variance between this year and last year, variance between a budget and actual values, and so on.
In this example, the goal is to calculate the variance between a Forecast (column C) and Actual (column D) as a percentage. For example, with a Forecast value of 100,000 and an Actual value of 112,000, we want to return a variance of 12%.
The concept of variance requires a baseline value and a "new" value. The baseline value is subtracted from the new value and the result is divided by the baseline value. The general formula, where "x" is the variance, is:
x=(new-baseline)/baseline x=(112,000-100,000)/100,000 x=12,000/100,000 x=0.12
After converting to an Excel formula with cell references, the formula in E5, copied down, is:
=(D5-C5)/C5 =(112,000-100,000)/100,000 =12,000/100,000 =0.12 =12%
As the formula is copied down, it returns a decimal number for each item in the list. When these numbers are formatted with the Percentage number format, they are displayed as percentages.
Formatting percentages in Excel
In mathematics, a percentage is a number expressed as a fraction of 100. For example, 25% is read as "Twenty-five percent" and is equivalent to 25/100 or 0.25. Accordingly, the values in column E are decimal values, with the Percentage number format applied. To convert these values to a whole number like 12, multiply by 100:
If you have a negative value for the original number, the above formula won't work and can be adjusted by adding the ABS function:
ABS stands for "absolute value", and it converts negative values to positive values. In this case, the ABS function ensures the original value is positive when the variance is calculated.
Note: be aware that results negative values can be misleading, as explained by Jon Acampora in his detailed article on the topic.