I have some elements that i am trying to rank by a quantity then sorting them but they can be ties. I want to return an array giving me all the tied elements. The elements list will always be 4. I'm showing you some examples and the expected returns in the following minisheet. Thanks in advance
nfl (1).xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
F | G | H | I | J | K | |||
1 | elements | quantity | rank quantity | sort | Sortby quantity | Expected return | ||
2 | A | 3 | 1 | 1 | A | A | ||
3 | B | 3 | 1 | 1 | B | B | ||
4 | D | 1 | 4 | 3 | C | |||
5 | C | 2 | 3 | 4 | D | |||
6 | ||||||||
7 | elements | quantity | rank quantity | sort | Sortby quantity | Expected return | ||
8 | A | 3 | 1 | 1 | A | A | ||
9 | B | 3 | 1 | 1 | B | B | ||
10 | D | 3 | 1 | 1 | D | D | ||
11 | C | 2 | 3 | 3 | C | |||
12 | ||||||||
13 | ||||||||
14 | elements | quantity | rank quantity | sort | Sortby quantity | Expected return | ||
15 | A | 2 | 3 | 1 | B | B | ||
16 | B | 3 | 1 | 1 | D | D | ||
17 | D | 3 | 1 | 3 | A | A | ||
18 | C | 2 | 3 | 3 | C | C | ||
19 | ||||||||
20 | elements | quantity | rank quantity | sort | Sortby quantity | Expected return | ||
21 | A | 2 | 3 | 1 | B | A | ||
22 | B | 3 | 1 | 3 | A | D | ||
23 | D | 2 | 3 | 3 | D | C | ||
24 | C | 2 | 3 | 3 | C | |||
25 | ||||||||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I2:I5,I21:I24,I15:I18,I8:I11 | I2 | =SORT(H2:H5) |
J2:J5,J21:J24,J15:J18,J8:J11 | J2 | =SORTBY(F2:F5,H2:H5) |
H2:H5,H21:H24,H15:H18,H8:H11 | H2 | =RANK(G2,$G$2:$G$5) |
Dynamic array formulas. |