A | B | C | D | E |
GROUP | TARGET | ACTUAL | % ATTAINMENT | EXPECTED RANK |
A | 9 | 17 | 188.9% | 1 |
A | 7 | 13 | 185.7% | 2 |
A | 6 | 11 | 183.3% | 3 |
A | 12 | 18 | 150% | 4 |
A | 6 | 9 | 150% | 5 |
B | 1 | 11 | 1100% | 1 |
B | 3 | 9 | 300% | 2 |
B | 4 | 8 | 200% | 3 |
B | 1 | 2 | 200% | |
B | 6 | 6 | 100% | 4 |
C | 7 | 7 | 100% | 1 |
C | 6 | 6 | 100% | 2 |
C | 24 | 20 | 83.3% | |
C | 31 | 6 | 19.4% | |
D | 14 | 19 | 135.7% | 1 |
D | 34 | 37 | 108.8% | 2 |
D | 24 | 24 | 100% | 3 |
D | 26 | 13 | 50% |
<tbody>
</tbody>
Hello, I would like to rank a set of data by group and based on multiple criteria. I've given a sample below with the expected output in column E. For each group, I would like to rank by % Attainment (column D), and break ties with Actual (column C). Also, I would like the ranking to skip any line where the % Attainment is less than 100%, or where the Actual is less than 3. Any help would be greatly appreciated!