Excel formula to auto-select a figure on certain conditions

[baidya91]

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:

[TABLE="width: 539"]
<colgroup><col><col span="11"></colgroup><tbody>[TR]
[TD]Pay Band[/TD]
[TD="colspan: 2"]P.B I
4900‐16200[/TD]
[TD="colspan: 5"]P.B. 2 5400‐25200[/TD]
[TD="colspan: 4"]P.B.3 7100‐37600[/TD]
[/TR]
[TR]
[TD]Grade Pay[/TD]
[TD]1700[/TD]
[TD]1800[/TD]
[TD]1900[/TD]
[TD]2100[/TD]
[TD]2300[/TD]
[TD]2600[/TD]
[TD]2900[/TD]
[TD]3200[/TD]
[TD]3600[/TD]
[TD]3900[/TD]
[TD]4100[/TD]
[/TR]
[TR]
[TD]Old Entry
Pay[/TD]
[TD]6600[/TD]
[TD]6830[/TD]
[TD]7300[/TD]
[TD]7680[/TD]
[TD]8160[/TD]
[TD]8840[/TD]
[TD]9600[/TD]
[TD]10300[/TD]
[TD]11040[/TD]
[TD]12270[/TD]
[TD]12750[/TD]
[/TR]
[TR]
[TD]Level[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]17000[/TD]
[TD]17600[/TD]
[TD]18800[/TD]
[TD]19700[/TD]
[TD]21000[/TD]
[TD]22700[/TD]
[TD]24700[/TD]
[TD]27000[/TD]
[TD]28900[/TD]
[TD]32100[/TD]
[TD]33400[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]17500[/TD]
[TD]18100[/TD]
[TD]19400[/TD]
[TD]20300[/TD]
[TD]21600[/TD]
[TD]23400[/TD]
[TD]25400[/TD]
[TD]27800[/TD]
[TD]29800[/TD]
[TD]33100[/TD]
[TD]34400[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]18000[/TD]
[TD]18600[/TD]
[TD]20000[/TD]
[TD]20900[/TD]
[TD]22200[/TD]
[TD]24100[/TD]
[TD]26200[/TD]
[TD]28600[/TD]
[TD]30700[/TD]
[TD]34100[/TD]
[TD]35400[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]18500[/TD]
[TD]19200[/TD]
[TD]20600[/TD]
[TD]21500[/TD]
[TD]22900[/TD]
[TD]24800[/TD]
[TD]27000[/TD]
[TD]29500[/TD]
[TD]31600[/TD]
[TD]35100[/TD]
[TD]36500[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]19100[/TD]
[TD]19800[/TD]
[TD]21200[/TD]
[TD]22100[/TD]
[TD]23600[/TD]
[TD]25500[/TD]
[TD]27800[/TD]
[TD]30400[/TD]
[TD]32500[/TD]
[TD]36200[/TD]
[TD]37600[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]19700[/TD]
[TD]20400[/TD]
[TD]21800[/TD]
[TD]22800[/TD]
[TD]24300[/TD]
[TD]26300[/TD]
[TD]28600[/TD]
[TD]31300[/TD]
[TD]33500[/TD]
[TD]37300[/TD]
[TD]38700[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]20300[/TD]
[TD]21000[/TD]
[TD]22500[/TD]
[TD]23500[/TD]
[TD]25000[/TD]
[TD]27100[/TD]
[TD]29500[/TD]
[TD]32200[/TD]
[TD]34500[/TD]
[TD]38400[/TD]
[TD]39900[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]20900[/TD]
[TD]21600[/TD]
[TD]23200[/TD]
[TD]24200[/TD]
[TD]25800[/TD]
[TD]27900[/TD]
[TD]30400[/TD]
[TD]33200[/TD]
[TD]35500[/TD]
[TD]39600[/TD]
[TD]41100[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]21500[/TD]
[TD]22200[/TD]
[TD]23900[/TD]
[TD]24900[/TD]
[TD]26600[/TD]
[TD]28700[/TD]
[TD]31300[/TD]
[TD]34200[/TD]
[TD]36600[/TD]
[TD]40800[/TD]
[TD]42300[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]22100[/TD]
[TD]22900[/TD]
[TD]24600[/TD]
[TD]25600[/TD]
[TD]27400[/TD]
[TD]29600[/TD]
[TD]32200[/TD]
[TD]35200[/TD]
[TD]37700[/TD]
[TD]42000[/TD]
[TD]43600[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]22800[/TD]
[TD]23600[/TD]
[TD]25300[/TD]
[TD]26400[/TD]
[TD]28200[/TD]
[TD]30500[/TD]
[TD]33200[/TD]
[TD]36300[/TD]
[TD]38800[/TD]
[TD]43300[/TD]
[TD]44900[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]23500[/TD]
[TD]24300[/TD]
[TD]26100[/TD]
[TD]27200[/TD]
[TD]29000[/TD]
[TD]31400[/TD]
[TD]34200[/TD]
[TD]37400[/TD]
[TD]40000[/TD]
[TD]44600[/TD]
[TD]46200[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]24200[/TD]
[TD]25000[/TD]
[TD]26900[/TD]
[TD]28000[/TD]
[TD]29900[/TD]
[TD]32300[/TD]
[TD]35200[/TD]
[TD]38500[/TD]
[TD]41200[/TD]
[TD]45900[/TD]
[TD]47600[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]24900[/TD]
[TD]25800[/TD]
[TD]27700[/TD]
[TD]28800[/TD]
[TD]30800[/TD]
[TD]33300[/TD]
[TD]36300[/TD]
[TD]39700[/TD]
[TD]42400[/TD]
[TD]47300[/TD]
[TD]49000[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]25600[/TD]
[TD]26600[/TD]
[TD]28500[/TD]
[TD]29700[/TD]
[TD]31700[/TD]
[TD]34300[/TD]
[TD]37400[/TD]
[TD]40900[/TD]
[TD]43700[/TD]
[TD]48700[/TD]
[TD]50500[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]26400[/TD]
[TD]27400[/TD]
[TD]29400[/TD]
[TD]30600[/TD]
[TD]32700[/TD]
[TD]35300[/TD]
[TD]38500[/TD]
[TD]42100[/TD]
[TD]45000[/TD]
[TD]50200[/TD]
[TD]52000[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]27200[/TD]
[TD]28200[/TD]
[TD]30300[/TD]
[TD]31500[/TD]
[TD]33700[/TD]
[TD]36400[/TD]
[TD]39700[/TD]
[TD]43400[/TD]
[TD]46400[/TD]
[TD]51700[/TD]
[TD]53600[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]28000[/TD]
[TD]29000[/TD]
[TD]31200[/TD]
[TD]32400[/TD]
[TD]34700[/TD]
[TD]37500[/TD]
[TD]40900[/TD]
[TD]44700[/TD]
[TD]47800[/TD]
[TD]53300[/TD]
[TD]55200[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]28800[/TD]
[TD]29900[/TD]
[TD]32100[/TD]
[TD]33400[/TD]
[TD]35700[/TD]
[TD]38600[/TD]
[TD]42100[/TD]
[TD]46000[/TD]
[TD]49200[/TD]
[TD]54900[/TD]
[TD]56900[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]29700[/TD]
[TD]30800[/TD]
[TD]33100[/TD]
[TD]34400[/TD]
[TD]36800[/TD]
[TD]39800[/TD]
[TD]43400[/TD]
[TD]47400[/TD]
[TD]50700[/TD]
[TD]56500[/TD]
[TD]58600[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]30600[/TD]
[TD]31700[/TD]
[TD]34100[/TD]
[TD]35400[/TD]
[TD]37900[/TD]
[TD]41000[/TD]
[TD]44700[/TD]
[TD]48800[/TD]
[TD]52200[/TD]
[TD]58200[/TD]
[TD]60400[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]31500[/TD]
[TD]32700[/TD]
[TD]35100[/TD]
[TD]36500[/TD]
[TD]39000[/TD]
[TD]42200[/TD]
[TD]46000[/TD]
[TD]50300[/TD]
[TD]53800[/TD]
[TD]59900[/TD]
[TD]62200[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]32400[/TD]
[TD]33700[/TD]
[TD]36200[/TD]
[TD]37600[/TD]
[TD]40200[/TD]
[TD]43500[/TD]
[TD]47400[/TD]
[TD]51800[/TD]
[TD]55400[/TD]
[TD]61700[/TD]
[TD]64100[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]33400[/TD]
[TD]34700[/TD]
[TD]37300[/TD]
[TD]38700[/TD]
[TD]41400[/TD]
[TD]44800[/TD]
[TD]48800[/TD]
[TD]53400[/TD]
[TD]57100[/TD]
[TD]63600[/TD]
[TD]66000[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]34400[/TD]
[TD]35700[/TD]
[TD]38400[/TD]
[TD]39900[/TD]
[TD]42600[/TD]
[TD]46100[/TD]
[TD]50300[/TD]
[TD]55000[/TD]
[TD]58800[/TD]
[TD]65500[/TD]
[TD]68000[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]35400[/TD]
[TD]36800[/TD]
[TD]39600[/TD]
[TD]41100[/TD]
[TD]43900[/TD]
[TD]47500[/TD]
[TD]51800[/TD]
[TD]56700[/TD]
[TD]60600[/TD]
[TD]67500[/TD]
[TD]70000[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]36500[/TD]
[TD]37900[/TD]
[TD]40800[/TD]
[TD]42300[/TD]
[TD]45200[/TD]
[TD]48900[/TD]
[TD]53400[/TD]
[TD]58400[/TD]
[TD]62400[/TD]
[TD]69500[/TD]
[TD]72100[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]37600[/TD]
[TD]39000[/TD]
[TD]42000[/TD]
[TD]43600[/TD]
[TD]46600[/TD]
[TD]50400[/TD]
[TD]55000[/TD]
[TD]60200[/TD]
[TD]64300[/TD]
[TD]71600[/TD]
[TD]74300[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]38700[/TD]
[TD]40200[/TD]
[TD]43300[/TD]
[TD]44900[/TD]
[TD]48000[/TD]
[TD]51900[/TD]
[TD]56700[/TD]
[TD]62000[/TD]
[TD]66200[/TD]
[TD]73700[/TD]
[TD]76500[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]39900[/TD]
[TD]41400[/TD]
[TD]44600[/TD]
[TD]46200[/TD]
[TD]49400[/TD]
[TD]53500[/TD]
[TD]58400[/TD]
[TD]63900[/TD]
[TD]68200[/TD]
[TD]75900[/TD]
[TD]78800[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]41100[/TD]
[TD]42600[/TD]
[TD]45900[/TD]
[TD]47600[/TD]
[TD]50900[/TD]
[TD]55100[/TD]
[TD]60200[/TD]
[TD]65800[/TD]
[TD]70200[/TD]
[TD]78200[/TD]
[TD]81200[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]42300[/TD]
[TD]43900[/TD]
[TD]47300[/TD]
[TD]49000[/TD]
[TD]52400[/TD]
[TD]56800[/TD]
[TD]62000[/TD]
[TD]67800[/TD]
[TD]72300[/TD]
[TD]80500[/TD]
[TD]83600[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]43600[/TD]
[TD]45200[/TD]
[TD]48700[/TD]
[TD]50500[/TD]
[TD]54000[/TD]
[TD]58500[/TD]
[TD]63900[/TD]
[TD]69800[/TD]
[TD]74500[/TD]
[TD]82900[/TD]
[TD]86100[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[szita2000]

Hi baidya91.

Could you not use roundup to round to the nearest 500?

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]18240[/TD]
[TD="align: right"][/TD]
[TD="align: right"]18500[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=ROUNDUP((A1*2),-3)/2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[baidya91]

Hi baidya91.

Could you not use roundup to round to the nearest 500?

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]18240[/TD]
[TD="align: right"][/TD]
[TD="align: right"]18500[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=ROUNDUP((A1*2),-3)/2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Sir, this doesn't apply to other figures. For example, it does not prroduce 33400 in case of 12750. It produces 33000 instead....