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.

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:

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.