Hi,
I am trying to use index and match formulas to determine the salary percentile in table 1 below. I need to match the person's group and position name in Table 1 to those in Table 2 and then determine the closest percentile to that person's salary.
For example: Joe an analyst in red group makes 37,000. The correct return in table 1 would be the 12th percentile. Any help on this is greatly appreciated! I'm not sure how to attach/link to the Excel file, so the tables are below. Thanks in advance!
<tbody>
</tbody>
I am trying to use index and match formulas to determine the salary percentile in table 1 below. I need to match the person's group and position name in Table 1 to those in Table 2 and then determine the closest percentile to that person's salary.
For example: Joe an analyst in red group makes 37,000. The correct return in table 1 would be the 12th percentile. Any help on this is greatly appreciated! I'm not sure how to attach/link to the Excel file, so the tables are below. Thanks in advance!
A | B | C | D | E | F | G | H | |
1 | Table | 1 | ||||||
2 | ||||||||
3 | Name | Group | Position Name | Salary | Salary Percentile | |||
4 | Joe | Red | Analyst | 37000 | ? | |||
5 | Bob | Green | Associate | 309000 | ? | |||
6 | Sally | Blue | Assistant | 156000 | ? | |||
7 | John | Red | Chief | 142000 | ? | |||
8 | ||||||||
9 | Table | 2 | ||||||
10 | ||||||||
11 | Group | Position Name | 10th %tile | 11th %tile | 12th %tile | 13th %tile | 14th %tile | 15th %tile |
12 | Red | Analyst | 30,000 | 33,000 | 36,000 | 39,000 | 42,000 | 45,000 |
13 | Red | Associate | 100,800 | 110,880 | 120,960 | 131,040 | 141,120 | 151,200 |
14 | Red | Assistant | 125,600 | 138,160 | 150,720 | 163,280 | 175,840 | 188,400 |
15 | Red | Chief | 130,800 | 143,880 | 156,960 | 170,040 | 183,120 | 196,200 |
16 | Green | Analyst | 163,600 | 179,960 | 196,320 | 212,680 | 229,040 | 245,400 |
17 | Green | Associate | 220,400 | 242,440 | 264,480 | 286,520 | 308,560 | 330,600 |
18 | Green | Assistant | 28,000 | 30,800 | 33,600 | 36,400 | 39,200 | 42,000 |
19 | Green | Chief | 92,000 | 101,200 | 110,400 | 119,600 | 128,800 | 138,000 |
20 | Blue | Analyst | 110,000 | 121,000 | 132,000 | 143,000 | 154,000 | 165,000 |
21 | Blue | Associate | 112,800 | 124,080 | 135,360 | 146,640 | 157,920 | 169,200 |
22 | Blue | Assistant | 158,400 | 174,240 | 190,080 | 205,920 | 221,760 | 237,600 |
23 | Blue | Chief | 204,400 | 224,840 | 245,280 | 265,720 | 286,160 | 306,600 |
<tbody>
</tbody>