Formula Excel : Calculating Percent Variance with Negative Values

In the previous section, “Formula 2: Calculating 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 wacky. 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)

Image 2-4 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.

Image 2-4: Using the ABS function gives you an accurate percent variance when
dealing with negative values.

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.

Note : You can safely use this formula for all your percent variance needs; it
works with any combination of positive and negative numbers.

 

Categories: Formula