Calculate percent variance

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:

=(D5-C5)/C5*100

Negative numbers

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:

=(new-original)/ABS(original)

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.