Exceljet

Quick, clean, and to the point

Calculate percent variance

Excel formula: Calculate percent variance
Generic formula 
=(new-baseline)/baseline
Summary 

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:

=(D5-C5)/C5

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.

Explanation 

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.