The pay of an employee in applicable level in the Pay Matrix will be fixed by multiplying the existing basic pay by a factor of 2.57, rounded off to the nearest rupee and the figure so arrived at will be located in that level in the Pay Matrix and if such an identical figure corresponds to any Cell in the applicable level in the pay matrix , the same shall be the pay , and if no such cell is available in the applicable level , the pay shall be fixed at the immediate next higher Cell in that applicable Level of the Pay Matrix.
The Pay Matrix is as follows:
<colgroup><col><col span="11"></colgroup><tbody>
</tbody>
Now the existing pay of an employee is Rs. 6800. Pay Level 1. If 6800 is multiplied by 2.57, the result will be 17476. Therefore his pay will be fixed at Rs. 17500 as per his level in the Pay Matrix because there is no figure equal to 17476 and the next higher level is 17500. My question is: Which excel formula can I use to auto-select the figure 17500 among others? Can anyone help me with this? Thanks in advance.
The Pay Matrix is as follows:
Pay Band | P.B I 4900‐16200 | P.B. 2 5400‐25200 | P.B.3 7100‐37600 | ||||||||
Grade Pay | 1700 | 1800 | 1900 | 2100 | 2300 | 2600 | 2900 | 3200 | 3600 | 3900 | 4100 |
Old Entry Pay | 6600 | 6830 | 7300 | 7680 | 8160 | 8840 | 9600 | 10300 | 11040 | 12270 | 12750 |
Level | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 |
1 | 17000 | 17600 | 18800 | 19700 | 21000 | 22700 | 24700 | 27000 | 28900 | 32100 | 33400 |
2 | 17500 | 18100 | 19400 | 20300 | 21600 | 23400 | 25400 | 27800 | 29800 | 33100 | 34400 |
3 | 18000 | 18600 | 20000 | 20900 | 22200 | 24100 | 26200 | 28600 | 30700 | 34100 | 35400 |
4 | 18500 | 19200 | 20600 | 21500 | 22900 | 24800 | 27000 | 29500 | 31600 | 35100 | 36500 |
5 | 19100 | 19800 | 21200 | 22100 | 23600 | 25500 | 27800 | 30400 | 32500 | 36200 | 37600 |
6 | 19700 | 20400 | 21800 | 22800 | 24300 | 26300 | 28600 | 31300 | 33500 | 37300 | 38700 |
7 | 20300 | 21000 | 22500 | 23500 | 25000 | 27100 | 29500 | 32200 | 34500 | 38400 | 39900 |
8 | 20900 | 21600 | 23200 | 24200 | 25800 | 27900 | 30400 | 33200 | 35500 | 39600 | 41100 |
9 | 21500 | 22200 | 23900 | 24900 | 26600 | 28700 | 31300 | 34200 | 36600 | 40800 | 42300 |
10 | 22100 | 22900 | 24600 | 25600 | 27400 | 29600 | 32200 | 35200 | 37700 | 42000 | 43600 |
11 | 22800 | 23600 | 25300 | 26400 | 28200 | 30500 | 33200 | 36300 | 38800 | 43300 | 44900 |
12 | 23500 | 24300 | 26100 | 27200 | 29000 | 31400 | 34200 | 37400 | 40000 | 44600 | 46200 |
13 | 24200 | 25000 | 26900 | 28000 | 29900 | 32300 | 35200 | 38500 | 41200 | 45900 | 47600 |
14 | 24900 | 25800 | 27700 | 28800 | 30800 | 33300 | 36300 | 39700 | 42400 | 47300 | 49000 |
15 | 25600 | 26600 | 28500 | 29700 | 31700 | 34300 | 37400 | 40900 | 43700 | 48700 | 50500 |
16 | 26400 | 27400 | 29400 | 30600 | 32700 | 35300 | 38500 | 42100 | 45000 | 50200 | 52000 |
17 | 27200 | 28200 | 30300 | 31500 | 33700 | 36400 | 39700 | 43400 | 46400 | 51700 | 53600 |
18 | 28000 | 29000 | 31200 | 32400 | 34700 | 37500 | 40900 | 44700 | 47800 | 53300 | 55200 |
19 | 28800 | 29900 | 32100 | 33400 | 35700 | 38600 | 42100 | 46000 | 49200 | 54900 | 56900 |
20 | 29700 | 30800 | 33100 | 34400 | 36800 | 39800 | 43400 | 47400 | 50700 | 56500 | 58600 |
21 | 30600 | 31700 | 34100 | 35400 | 37900 | 41000 | 44700 | 48800 | 52200 | 58200 | 60400 |
22 | 31500 | 32700 | 35100 | 36500 | 39000 | 42200 | 46000 | 50300 | 53800 | 59900 | 62200 |
23 | 32400 | 33700 | 36200 | 37600 | 40200 | 43500 | 47400 | 51800 | 55400 | 61700 | 64100 |
24 | 33400 | 34700 | 37300 | 38700 | 41400 | 44800 | 48800 | 53400 | 57100 | 63600 | 66000 |
25 | 34400 | 35700 | 38400 | 39900 | 42600 | 46100 | 50300 | 55000 | 58800 | 65500 | 68000 |
26 | 35400 | 36800 | 39600 | 41100 | 43900 | 47500 | 51800 | 56700 | 60600 | 67500 | 70000 |
27 | 36500 | 37900 | 40800 | 42300 | 45200 | 48900 | 53400 | 58400 | 62400 | 69500 | 72100 |
28 | 37600 | 39000 | 42000 | 43600 | 46600 | 50400 | 55000 | 60200 | 64300 | 71600 | 74300 |
29 | 38700 | 40200 | 43300 | 44900 | 48000 | 51900 | 56700 | 62000 | 66200 | 73700 | 76500 |
30 | 39900 | 41400 | 44600 | 46200 | 49400 | 53500 | 58400 | 63900 | 68200 | 75900 | 78800 |
31 | 41100 | 42600 | 45900 | 47600 | 50900 | 55100 | 60200 | 65800 | 70200 | 78200 | 81200 |
32 | 42300 | 43900 | 47300 | 49000 | 52400 | 56800 | 62000 | 67800 | 72300 | 80500 | 83600 |
33 | 43600 | 45200 | 48700 | 50500 | 54000 | 58500 | 63900 | 69800 | 74500 | 82900 | 86100 |
<colgroup><col><col span="11"></colgroup><tbody>
</tbody>
Now the existing pay of an employee is Rs. 6800. Pay Level 1. If 6800 is multiplied by 2.57, the result will be 17476. Therefore his pay will be fixed at Rs. 17500 as per his level in the Pay Matrix because there is no figure equal to 17476 and the next higher level is 17500. My question is: Which excel formula can I use to auto-select the figure 17500 among others? Can anyone help me with this? Thanks in advance.