Hi there, can anyone help me?
Why is Client G's value in Cell L10 always showing the same as Client P's value in L3? The correct value for cell L10 should be 123.
Why is Client G's value in Cell L10 always showing the same as Client P's value in L3? The correct value for cell L10 should be 123.
football table example of auto ranking data.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Score 1 | Score 2 | Total score | Client | result | rank | rank | Client | result | |||||
2 | Client | |||||||||||||
3 | Client A | 65 | -15 | 50 | Client A | 50 | 13 | 1 | Client P | 154 | ||||
4 | Client B | 21 | 57 | 78 | Client B | 78 | 12 | 2 | Client F | 150 | ||||
5 | Client C | 65 | 57 | 122 | Client C | 122 | 9 | 3 | Client D | 147 | ||||
6 | Client D | 79 | 68 | 147 | Client D | 147 | 3 | 4 | Client M | 146 | ||||
7 | Client E | 52 | 37 | 89 | Client E | 89 | 11 | 5 | Client N | 145 | ||||
8 | Client F | 74 | 76 | 150 | Client F | 150 | 2 | 6 | Client K | 130 | ||||
9 | Glient G | 32 | 91 | 123 | Glient G | 123 | 8 | 7 | Client J | 129 | ||||
10 | Client H | 92 | 22 | 114 | Client H | 114 | 10 | 8 | Glient G | 154 | ||||
11 | Client I | 43 | -45 | -2 | Client I | -2 | 14 | 9 | Client C | 122 | ||||
12 | Client J | 83 | 46 | 129 | Client J | 129 | 7 | 10 | Client H | 114 | ||||
13 | Client K | 73 | 57 | 130 | Client K | 130 | 6 | 11 | Client E | 89 | ||||
14 | Client L | 61 | -94 | -33 | Client L | -33 | 15 | 12 | Client B | 78 | ||||
15 | Client M | 82 | 64 | 146 | Client M | 146 | 4 | 13 | Client A | 50 | ||||
16 | Client N | 90 | 55 | 145 | Client N | 145 | 5 | 14 | Client I | -2 | ||||
17 | Client O | 2 | -64 | -62 | Client O | -62 | 16 | 15 | Client L | -33 | ||||
18 | Client P | 75 | 79 | 154 | Client P | 154 | 1 | 16 | Client O | -62 | ||||
19 | ||||||||||||||
20 | ||||||||||||||
21 | Why is Client G's value in cell L10 always the same as the Client P figure in L3? | |||||||||||||
22 | The value in Cell L10 should be 123 | |||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F3:F18 | F3 | =UNIQUE(A3:A18,FALSE,FALSE) |
G3:G18 | G3 | =D3 |
H3:H18 | H3 | =RANK.EQ(D3,$D$3:$D$18,0) |
K3:K18 | K3 | =OFFSET($F$2,MATCH(J3,$H$3:$H$18,0),0) |
L3:L18 | L3 | =VLOOKUP($K3,$F$3:$H$18,2) |
D3:D18 | D3 | =SUM(B3:C3) |
Dynamic array formulas. |