Hello, I am trying to get some help with a small project.
In short, I would like a sort of "records" page, where I can keep track of what team has scored the most points and when. All records should be distinct; in other words, if the same number of points were scored on different occasions (whether by a different team, different week, same week, etc. etc.)
Currently, this involves me finding the nth largest value within a 2D range, as well as its index (so I can find the correspondent week and team). This works fine when there are no duplicates, as my formula uses a sum-product matching to the nth largest value to locate its row and column. However, it fails for duplicates for obvious reasons.
In short, I would like a sort of "records" page, where I can keep track of what team has scored the most points and when. All records should be distinct; in other words, if the same number of points were scored on different occasions (whether by a different team, different week, same week, etc. etc.)
Currently, this involves me finding the nth largest value within a 2D range, as well as its index (so I can find the correspondent week and team). This works fine when there are no duplicates, as my formula uses a sum-product matching to the nth largest value to locate its row and column. However, it fails for duplicates for obvious reasons.
Fantasy Ball '21.xlsx | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
1 | Team | Week 1 | Week 2 | Week 3 | Week 4 | Week 5 | Week 6 | Week 7 | Week 8 | Week 9 | Week 10 | Week 11 | Week 12 | Week 13 | Week 14 | Week 15 | Week 16 | ||
2 | A | 63 | 466 | 577 | 85 | 378 | 340 | 774 | 747 | 548 | 549 | ||||||||
3 | B | 259 | 501 | 325 | 264 | 321 | 586 | 416 | 463 | 332 | 328 | ||||||||
4 | C | 23 | 509 | 538 | 469 | 450 | 621 | 747 | 568 | 600 | 382 | ||||||||
5 | D | 121 | 465 | 617 | 383 | 568 | 409 | 630 | 477 | 201 | 566 | ||||||||
6 | E | 99 | 102 | 778 | 291 | 461 | 429 | 353 | 714 | 590 | 406 | ||||||||
7 | F | 94 | 441 | 606 | 329 | 301 | 247 | 691 | 502 | 151 | 153 | ||||||||
8 | G | 46 | 551 | 671 | 263 | 540 | 539 | 597 | 492 | 474 | 391 | ||||||||
9 | H | 92 | 626 | 474 | 316 | 544 | 697 | 689 | 708 | 614 | 391 | ||||||||
10 | I | 183 | 606 | 523 | 255 | 161 | 338 | 583 | 544 | 392 | 223 | ||||||||
11 | J | 148 | 450 | 476 | 76 | 410 | 506 | 295 | 468 | 441 | 204 | ||||||||
Raw PF |
Fantasy Ball '21.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Most PF | Row ind (no h) | Col ind (no h) | Team | Week | |||
2 | 1 | 778 | 5 | 3 | E | Week 3 | ||
3 | 2 | 774 | 1 | 7 | A | Week 7 | ||
4 | 3 | 747 | 5 | 16 | E | Week 16 | ||
5 | 4 | 747 | 5 | 16 | E | Week 16 | ||
6 | 5 | 714 | 5 | 8 | E | Week 8 | ||
7 | 6 | 708 | 8 | 8 | H | Week 8 | ||
8 | 7 | 697 | 8 | 6 | H | Week 6 | ||
9 | 8 | 691 | 6 | 7 | F | Week 7 | ||
10 | 9 | 689 | 8 | 7 | H | Week 7 | ||
11 | 10 | 671 | 7 | 3 | G | Week 3 | ||
12 | 11 | 630 | 4 | 7 | D | Week 7 | ||
13 | 12 | 626 | 8 | 2 | H | Week 2 | ||
14 | 13 | 621 | 3 | 6 | C | Week 6 | ||
15 | 14 | 617 | 4 | 3 | D | Week 3 | ||
16 | 15 | 614 | 8 | 9 | H | Week 9 | ||
17 | 16 | 606 | 16 | 6 | #REF! | Week 6 | ||
18 | 17 | 606 | 16 | 6 | #REF! | Week 6 | ||
19 | 18 | 600 | 3 | 9 | C | Week 9 | ||
20 | 19 | 597 | 7 | 7 | G | Week 7 | ||
21 | 20 | 590 | 5 | 9 | E | Week 9 | ||
Stats |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2:B21 | B2 | =LARGE('Raw PF'!$B$2:$Q$11,A2) |
C2:C21 | C2 | =SUMPRODUCT(('Raw PF'!$B$2:$Q$11=B2)*ROW('Raw PF'!$B$2:$Q$11))-ROW('Raw PF'!$B$2:$Q$11)+1 |
D2:D21 | D2 | =SUMPRODUCT(('Raw PF'!$B$2:$Q$11=LARGE('Raw PF'!$B$2:$Q$11,A2))*COLUMN('Raw PF'!$B$2:$Q$11))-COLUMN('Raw PF'!$B$2:$Q$11)+1 |
E2:E21 | E2 | =INDEX('Raw PF'!$A$1:$Q$11,C2+1,1) |
F2:F21 | F2 | =INDEX('Raw PF'!$A$1:$Q$11,1,D2+1) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
'Raw PF'!_FilterDatabase | ='Raw PF'!$A$1:$S$11 | E2:F21 |