I am trying to calculate the percentrank of each annual salary by salary grade level but need to exclude for instance departments that start with "IT ". For those who are in the IT department I would like to see blank and make sure their number is not part of the percentrank calculation. So, I need a conditional formula to calculate column E. Below is the table I am trying to achieve. My column D is easy. This is the formula for column D is =PERCENTRANK(IF($B$2:$B$13=B2,$C$2:$C$13),C2) which appears to work. Any ideas?
A | B | C | D | E | |
| Dept | Salary Grade | Annual Salary | Percent Rank (All) | Percent Rank (Excl. IT *) | |
| 2 | Finance | 12 | $16,753 | 0% | 0% |
| 3 | Finance | 12 | $20,933 | 27% | 38% |
| 4 | Finance | 12 | $38,334 | 55% | 63% |
| 5 | Human Resources | 12 | $18,657 | 18% | 25% |
| 6 | Human Resources | 12 | $21,472 | 36% | 50% |
| 7 | Human Resources | 12 | $48,015 | 73% | 75% |
| 8 | Marketing | 12 | $17,553 | 9% | 13% |
| 9 | Marketing | 12 | $51,916 | 82% | 88% |
| 10 | Marketing | 12 | $53,690 | 91% | 100% |
| 11 | IT Application | 12 | $31,797 | 45% | |
| 12 | IT Development | 12 | $39,716 | 64% | |
| 13 | IT Operations | 12 | $54,950 | 100% |
