Ready To Use Excel Formulas :how to Calculate Percent Variance with Negative Values

In the previous post, “how to Calculate Percent Variance,” you discovered how to calculate a percent variance. That formula works beautifully in most cases. However, when the benchmark value is a negative value, the formula breaks down.

For example, imagine that you’re starting a business and expect to take a loss the first year. So you give yourself a budget of negative $10,000. Now imagine that after your first year, you actually made money, earning $12,000. Calculating the percent variance between your actual revenue and budgeted revenue would give you -220%. You can try it on a calculator. 12,000 minus -10,000 divided by -10,000 equals -220%.

How can you say that your percent variance is -220% when you clearly made money? Well, the problem is that when your benchmark value is a negative number, the math inverts the results, causing numbers to look awkward. 

This is a real problem in the corporate world where budgets can often be negative values.

The fix is to leverage the ABS function to negate the negative benchmark value:

=(C4-B4)/ABS(B4)

The screenshot below uses this formula in cell E4, illustrating the different results you get when

using the standard percent variance formula and the improved percent variance formula.

 Using the ABS function 

How it works

Excel’s ABS function returns the absolute value for any number you pass to it.

Entering =ABS(-100) into cell A1 would return 100. 

The ABS function essentially makes any number a non-negative number. Using ABS in this formula negates the effect of the negative benchmark (the negative 10,000 budget in the example) and returns the correct percent variance.

Read Also Ready To Use Excel Formulas: how to Calculate Percent Variance