JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,535
- Office Version
- 365
- Platform
- Windows
This table tracks the odds and the rankings for several teams. These odds change every few days. The Δ columns show how the rankings changed since the last previous ranking.
I would like to combine the rank and Δ columns like this:
But when I put this equation in H7
it gets a circular reference error. Is there any way around this?
2022 NBA playoff odds.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
C | D | E | F | G | H | I | J | K | |||
6 | Team | 5/6 | Rank3 | Δ2-3 | 5/3 | Rank2 | Δ1-2 | 5/1 | Rank1 | ||
7 | A | 200 | 1 | +2 | 150 | 3 | -1 | 175 | 2 | ||
8 | B | 175 | 2 | -1 | 175 | 1 | +3 | 150 | 4 | ||
9 | C | 160 | 3 | =0 | 150 | 3 | -2 | 180 | 1 | ||
10 | D | 150 | 4 | -2 | 160 | 2 | +1 | 170 | 3 | ||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E7:E10 | E7 | =RANK.EQ([@[5/6]],[5/6]) |
F7:F10 | F7 | =[@Rank2]-[@Rank3] |
H7:H10 | H7 | =RANK.EQ([@[5/3]],[5/3]) |
I7:I10 | I7 | =[@Rank1]-[@Rank2] |
K7:K10 | K7 | =RANK.EQ([@[5/1]],[5/1]) |
I would like to combine the rank and Δ columns like this:
2022 NBA playoff odds.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
M | N | O | P | Q | R | S | |||
6 | Team | 5/6 | Rank3 | 5/3 | Rank2 | 5/1 | Rank1 | ||
7 | A | 200 | 1 (+2) | 150 | 3 (-1) | 175 | 2 | ||
8 | B | 175 | 2 (-1) | 175 | 1 (+3) | 150 | 4 | ||
9 | C | 160 | 3 (=0) | 150 | 3 (-2) | 180 | 1 | ||
10 | D | 150 | 4 (-2) | 160 | 2 (+1) | 170 | 3 | ||
Sheet3 |
But when I put this equation in H7
VBA Code:
=RANK.EQ([@[5/3]],[5/3]) & " (" & [Rank1]-[Rank2] & ")"
it gets a circular reference error. Is there any way around this?