Part II: Working with Formulas and Functions
Calculating percent variance with negative values
In the previous section, “Calculating percent variance,” you discovered how to calculate
a percent variance. This works beautifully in most cases. However, when the benchmark
value is a zero or less, the formula breaks down.
For example, imagine you’re starting a business and expect to take a loss during 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 calcu-
lator. 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)
Figure 10.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.
FIGURE 10.4
Using the ABS function will give you an accurate percent variance when dealing with nega-
tive values.
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 num-
ber a non-negative number. Using ABS in this formula negates the effect of the negative
benchmark (the negative 10,000 budget in our example) and returns the correct percent
variance.
You can use safely use this formula for all of your percent variance needs because it works with any combination of
positive and negative numbers.
