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:

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.

 

[szita2000]

Hi baidya91.

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

	A	B	C
1	18240		18500

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

Sheet1​

Worksheet Formulas Cell Formula C1 =ROUNDUP((A1*2),-3)/2 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[baidya91]

Hi baidya91.

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

	A	B	C
1	18240		18500

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

Sheet1​

Worksheet Formulas Cell Formula 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....