The formula for calculating percent variance within Excel 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)
The figure 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.
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.
You can safely use this formula for all your percent variance needs; it works with any combination of positive and negative numbers.