Excel formula to auto-select a figure on certain conditions

baidya91

Board Regular
Joined
Jun 1, 2016
Messages
115
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:

Pay BandP.B I
4900‐16200
P.B. 2 5400‐25200P.B.3 7100‐37600
Grade Pay17001800190021002300260029003200360039004100
Old Entry
Pay
660068307300768081608840960010300110401227012750
Level1234567891011
11700017600188001970021000227002470027000289003210033400
21750018100194002030021600234002540027800298003310034400
31800018600200002090022200241002620028600307003410035400
41850019200206002150022900248002700029500316003510036500
51910019800212002210023600255002780030400325003620037600
61970020400218002280024300263002860031300335003730038700
72030021000225002350025000271002950032200345003840039900
82090021600232002420025800279003040033200355003960041100
92150022200239002490026600287003130034200366004080042300
102210022900246002560027400296003220035200377004200043600
112280023600253002640028200305003320036300388004330044900
122350024300261002720029000314003420037400400004460046200
132420025000269002800029900323003520038500412004590047600
142490025800277002880030800333003630039700424004730049000
152560026600285002970031700343003740040900437004870050500
162640027400294003060032700353003850042100450005020052000
172720028200303003150033700364003970043400464005170053600
182800029000312003240034700375004090044700478005330055200
192880029900321003340035700386004210046000492005490056900
202970030800331003440036800398004340047400507005650058600
213060031700341003540037900410004470048800522005820060400
223150032700351003650039000422004600050300538005990062200
233240033700362003760040200435004740051800554006170064100
243340034700373003870041400448004880053400571006360066000
253440035700384003990042600461005030055000588006550068000
263540036800396004110043900475005180056700606006750070000
273650037900408004230045200489005340058400624006950072100
283760039000420004360046600504005500060200643007160074300
293870040200433004490048000519005670062000662007370076500
303990041400446004620049400535005840063900682007590078800
314110042600459004760050900551006020065800702007820081200
324230043900473004900052400568006200067800723008050083600
334360045200487005050054000585006390069800745008290086100

<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.
 

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
52
Hi baidya91.

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

ABC
11824018500

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C1=ROUNDUP((A1*2),-3)/2

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

baidya91

Board Regular
Joined
Jun 1, 2016
Messages
115
Hi baidya91.

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

ABC
11824018500

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C1=ROUNDUP((A1*2),-3)/2

<thead>
</thead><tbody>
</tbody>

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

Forum statistics

Threads
1,078,394
Messages
5,339,939
Members
399,340
Latest member
JasonT903

Some videos you may like

This Week's Hot Topics

Top