Training

Warning: Avoid These Pitfalls When Calculating Deltas

A delta calculates the difference between two values. Deltas help us compare data, such as how revenue last year compares to this year, or how projected costs compare to actual costs. While calculating a delta appears easy, there are pitfalls you need to avoid and tricks that can help you. This article covers best practices for using deltas across different scenarios.

Absolute vs Relative

Deltas can be absolute or relative. An absolute delta simply calculates the difference between two values:
B1 - A1
Where A1 is the base value, such as last year’s revenue or the projected cost, and B1 is the value being compared, such as this year’s revenue or the actual cost.

Absolute deltas are useful when looking for differences that have bottom-line impact. That is, when the amount of difference itself is relevant regardless of the base value. For instance, a drop of $1,000,000 in revenue impacts the bottom line regardless of whether that drop came from a product making $100M a year or one making $5M a year.

Relative deltas calculate the difference as a percent of the base value. So, a drop of $1M in revenue from a $100M product is only a 1% drop, while for a $5M product, it’s 20%. The relative difference between the two is huge, even if the absolute difference is the same. Relative deltas are used when looking at rates of change or relative magnitudes.

The basic formula for calculating a relative delta is:
(B1 - A1) / A1
Relative deltas are usually formatted as percentages.

Pitfall: Zero Values

The basic relative delta formula works as long as none of your base values are zero. If a base value is zero, you’ll get a divide-by-zero error in Excel. How you decide to treat these cases depends on the type of analysis you are doing.

Solution: Use a blank as a null value

If the delta value is meaningless when the base value is zero, such as when you incurred actual costs when no costs were projected, the best approach is to use a blank to represent null. Thus, the formula becomes:
IF(A1 <> 0, (B1 - A1) / A1, "")

Solution: Use a fixed value

If the value is important to your analysis, and you want to prevent it from being filtered out, you can set the value to a specific number such as 100%. For instance, if you are looking at which products had the fastest growth since last year, and a new product was recently introduced, you may not want to exclude this product. The formula then becomes:
IF(A1 <> 0, (B1 - A1) / A1, 1.0)

Solution: Use the maximum of the range

Setting the value to 100% though can skew your analysis, especially if all of your other products are in the 0-20% range. An advanced approach is to set the value to the maximum growth rate for those products which don’t have a base value of zero.

Because Excel can’t calculate the maximum value of a formula applied across a range, you need to create two columns. The first column calculates the delta, setting any values with a base of zero to blank. The second column then grabs the maximum value of the first column and uses it whenever the base value is zero.

The formulas for the two columns then become:
Column C: IF(A1 <> 0, (B1 - A1) / A1, "")
Column D: IF(C1 <> "", C1, MAX(C:C))

For an Excel report, you can simply hide column C. If importing into Heat Map Explorer or another tool for further analysis, move column C to the far right of your data (eg: column AA). Then when you import into Heat Map Explorer, exclude this column, and any another other intermediary columns you’ve created, from the range you’re importing.

Pitfall: Negative Values

Negative values can cause problems when calculating relative deltas. If last year you lost $20M and this year you only lost $5M, your deltas would be:
Absolute: B1 - A1 : -5 - (-20) = 15
Relative: (B1 - A1) / A1 : (-5 - (-20)) / -20 = -75%

While the absolute delta of 15 makes sense, saying that the change in your profit was -75% can be misleading. There are several ways to solve this problem.

Solution: Use blanks when one value is negative

The easiest solution is to treat the delta as invalid by using a blank field to represent null if either value is negative. Our formula then becomes:
IF(A1 > 0, IF(B1 >= 0, (B1 - A1) / A1 * SIGN(A1), ""), "")
The problem with this solution is that sometimes it’s useful to analyze deltas when one or both values are negative.

Solution: Reverse sign

If we want to treat an increase in value as a positive delta and a decrease in value as a negative delta, the solution is to reverse the sign. It turns out we only need to do this when the base value is negative. Thus our adjusted relative delta formula becomes:
(B1 - A1) / A1 * SIGN(A1)
Or if we are handling zero values:
IF(A1 <> 0, (B1 - A1) / A1 * SIGN(A1), "")
This works regardless of whether both values are positive, both are negative or one is negative and one is positive.

While this fixes the problem of the sign being wrong, it introduces another problem: smaller changes appear bigger. For instance, take going from a loss of $20M to a profit of $5M. Our absolute delta is $25M and our relative delta is now 125%. Sounds good. But what if we went from a loss of $5M to a profit of $5M. Our absolute delta would be $10M, but our relative delta would be 200%. Less of a change, but a larger relative delta. Unfortunately, there’s no good solution to this problem.

Using Deltas in Heat Map Explorer

Heat Map Showing % Change

Heat Map Showing % Change Delta

Once you’ve created your delta column in Excel, you’ll want to import your data into Heat Map Explorer so you can analyze it visually.

Delta values are best analyzed using color in heat maps. After creating your map, switch the Color drop-down on the Data tab to your delta field.

Next, select an appropriate color scheme. If your delta contains both positive and negative values, I recommend using the “+/-: Red-Green” color scheme (or, for the color blind, “+/-: Blue-Yellow”). All positive deltas will be green and all negative deltas will be red, with the brightness of the color indicating the magnitude of the change.

To divide your delta into ranges, use the Progress color schemes like Progress: Stoplight or Progress: Quartile. Then use the color slider to adjust the thresholds of what values determine each color.

Finally, change the rollup algorithm used to calculate the aggregate value of your delta for groups. Go to the Data > Fields menu, select your delta field and switch the Rollup Formula under the Aggregation section to “Average”. This ensures that when you hover over a group, you get the average deltas for all the elements in the group rather than the sum.

You’re now all ready to analyze your deltas in Heat Map Explorer.