A common metric in dashboards and reporting is variance calculation, i.e. comparing a particular data point against a target or previous data point to find the delta.

For example, if you have a target of 100, and an actual of 67, then the variance would be -33%. In other words, the Actual is 33% less than the Target.

The standard calculation for variance is:  

    \[ \frac{ \left( Actual - Target \right) }{Target}  \]

To prevent receiving a division by zero (#DIV/0!) error, wrap the calculation in the IFERROR function.

=IFERROR((Actual-Target)/Target,0)

However, the standard variance calculation just doesn’t handle negatives very well. So, if the Target is negative and the Actual is positive (or vice versa), the formula will give wrong answers.

Example 1

  A B C D
1 Target Actual Std Var Calc  
2 100 75 -25%  
3 50 75 50%  
4 0 75 0% << should be +100%
5 0 0 0%  
6 -100 200 -300% << should be +300%
7 -100 -100 0%  
8 -50 0 -100% << should be +100%
9 -100 -200 100% << should be -100%

Notice that the wrong answer is returned for several of the scenarios when Actual or Target is a negative number.

To get the correct answers, simply replace the denominator in the standard variance formula with this:

(Actual-Target)/ABS(Actual*(Target=0)+Target)

This new formula uses a conditional denominator. IF the Target value is 0, then the denominator will be the Absolute Actual. If the Target value is not 0, then the denominator will be the Absolute Target.

With this simple replacement, you’ll get the correct variance for any combination:

Example 2

  A B C
1 Target Actual Std Var Calc
2 100 75 =IFERROR((B2-A2)/ABS(B2*(A2=0)+a2),0)
3 50 75 50%
4 0 75 100%
5 0 0 0%
6 -100 200 300%
7 -100 -100 0%
8 -50 0 100%
9 -100 -200 -100%