Showing the direction a value is moving

[JenniferMurphy]

I sometimes create sheets to track the standings of sports teams. Some of these sheets contains rankings. Over time, the rankings change. In the past, I have calculated the change in rank by subtracting the old ranking from the new one (=NewRank-OldRank). This works, but can be a little misleading. If a team goes up 3 positions in ranking, from 10 to 7, the new-old formula results in a negative value (7 - 10 = -3). This is correct, but misleading. I then changed the formula be reversing the terms (=OldRank-NewRank). Now we get "+3", but this is still somewhat confusing because 7-10 does not equal "+3".

I have what I think is a better solution. Since we are interested in the movement up and down in the rankings, I have replaced the plus and minus signs with up and down arrows. For a positive value, which represents a lower ranking, the "+" sign is replaced by a down arrow ("↓"). for a negative value, which represents a higher ranking, the "-" is replaced by an up arrow ("↑").

This is easily achieved with this custom format: ↓0;↑0;"="

Here is a sample table showing my Awesome Ants moving up from 4th to 1st.

I couldn't figure out a way to draw a border around part of the table, so I highlighted the three sections in different colors.

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="colspan: 3, align: center"]3/28/19
[/TD]
[TD="colspan: 3, align: center"]3/24/19
[/TD]
[TD="colspan: 3, align: center"]3/20/19
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Team[/TD]
[TD="align: center"]Rank[/TD]
[TD="align: center"]Δ↕
[/TD]
[TD="align: center"]Δ±[/TD]
[TD="align: center"]Rank[/TD]
[TD="align: center"]Δ↕[/TD]
[TD="align: center"]Δ±[/TD]
[TD="align: center"]Rank[/TD]
[TD="align: center"]Δ↕[/TD]
[TD="align: center"]Δ±[/TD]
[TD="colspan: 2, align: center"]Formulas[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Ants[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]↑2[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]↑1[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]--[/TD]
[TD="align: center"]--[/TD]
[TD="align: center"]H6: =G6-J6[/TD]
[TD="align: center"]I6: =G6-J6[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Tigers[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]=[/TD]
[TD="align: center"]=[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]↓1
[/TD]
[TD="align: center"]+1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]--[/TD]
[TD="align: center"]--[/TD]
[TD="align: center"]H7: =G7-J7[/TD]
[TD="align: center"]I7: =G7-J7[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Bears[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]↓2[/TD]
[TD="align: center"]+2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]↑1[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]--[/TD]
[TD="align: center"]--[/TD]
[TD="align: center"]H8: =G8-J8[/TD]
[TD="align: center"]I8: =G8-J8[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]Slugs[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]↓1[/TD]
[TD="align: center"]+1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]=[/TD]
[TD="align: center"]=[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]--[/TD]
[TD="align: center"]--[/TD]
[TD="align: center"]H9: =G9-J9[/TD]
[TD="align: center"]I9: =G9-J9[/TD]
[/TR]
</tbody>[/TABLE]

I offer this in case it might be useful for anyone else, but I would welcome any suggestions to improve it.

Note: There are a couple of alternatives to this solution:

1. Reverse the terms in the difference formula (=old-new). This makes higher rankings show a positive delta, but I still think this is misleading and confusing.
2. Swap characters before each section of the custom format string (-0;+0;"=") or (↑0;↓0;"="). The former is the same as reversing the terms. The latter is the equivalent of the table above.

 

[JenniferMurphy]

PS: Be sure to use the arrow characters from the from the standard (Calibri) font, not one of the symbol or dingbat fonts. The method I use is:

• Select an unused cell.
• Insert the symbol using Insert | Symbol and then making sure that the font is Calabri.
• Scroll down to the last page of characters.
• Select the arrow symbol and click Insert & Close.
• Edit the cell, copy the symbol to the clipboard.
• Edit the custom format & paste the symbol where you need it.

The symbol can also be gotten into the clipboard using Word, which is a little simpler.