Calculate percent variance

Calculate percent variance

Generic formula 

=(new-original)/original

Related formulas 

Percent of goal

Get percent change

Explanation

If you need to calculate percent variance you can use a simple formula that pides the difference between new number and the original number by the original number.

This formula can be used to calculate things like variance between this year and last year, variance between a budgeted amount with an actual amount, and so on.

How this formula works

The concept of variance requires a baseline value and a "new" value. Once you calculate the difference between the two numbers, you only need to pide by the original value.

In the example, we are calculating a variance from forecast, so we need subtract the fForecast in column C from the Actual in column D, then pide that by the original number in Column C. The result in column E must be formatted using the Percentage number format.

In cell E5, the formula is:

=(D6-C6)/C6

In the example, the difference between actual and forecast is 34,000, so the formula reduces to:

=(359000-325000)/325000=34000/325000=0.1046=10%

When you format this result using the Percentage number format, Excel will automatically display the decimal result as a percentage, there is no need to multiply by 100.

Negative numbers

If 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 makes negative numbers positive and in this case ensures the original value is positive when the variance is calculated. However, the results you get when with negative values can be misleading, as explained by Jon Acampora in his detailed article on the topic.

Calculate percent variance

Generic formula 

=(new-original)/original

Related formulas 

Percent of goal

Get percent change

Explanation

If you need to calculate percent variance you can use a simple formula that pides the difference between new number and the original number by the original number.

This formula can be used to calculate things like variance between this year and last year, variance between a budgeted amount with an actual amount, and so on.

How this formula works

The concept of variance requires a baseline value and a "new" value. Once you calculate the difference between the two numbers, you only need to pide by the original value.

In the example, we are calculating a variance from forecast, so we need subtract the fForecast in column C from the Actual in column D, then pide that by the original number in Column C. The result in column E must be formatted using the Percentage number format.

In cell E5, the formula is:

=(D6-C6)/C6

In the example, the difference between actual and forecast is 34,000, so the formula reduces to:

=(359000-325000)/325000=34000/325000=0.1046=10%

When you format this result using the Percentage number format, Excel will automatically display the decimal result as a percentage, there is no need to multiply by 100.

Negative numbers

If 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 makes negative numbers positive and in this case ensures the original value is positive when the variance is calculated. However, the results you get when with negative values can be misleading, as explained by Jon Acampora in his detailed article on the topic.