JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,535
- Office Version
- 365
- Platform
- Windows
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.
<tbody>
</tbody>
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:
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.
R/C | C | D | E | F | G | H | I | J | K | L | M | N |
4 | 3/28/19 | 3/24/19 | 3/20/19 | |||||||||
5 | Team | Rank | Δ↕ | Δ± | Rank | Δ↕ | Δ± | Rank | Δ↕ | Δ± | Formulas | |
6 | Ants | 1 | ↑2 | -2 | 3 | ↑1 | -1 | 4 | -- | -- | H6: =G6-J6 | I6: =G6-J6 |
7 | Tigers | 2 | = | = | 2 | ↓1 | +1 | 1 | -- | -- | H7: =G7-J7 | I7: =G7-J7 |
8 | Bears | 3 | ↓2 | +2 | 1 | ↑1 | -1 | 2 | -- | -- | H8: =G8-J8 | I8: =G8-J8 |
9 | Slugs | 4 | ↓1 | +1 | 3 | = | = | 3 | -- | -- | H9: =G9-J9 | I9: =G9-J9 |
<tbody>
</tbody>
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:
- 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.
- 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.
Last edited: