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:

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% |