Results 1 to 3 of 3

Thread: Excel formula to auto-select a figure on certain conditions
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2016
    Location
    India
    Posts
    114
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Angry Excel formula to auto-select a figure on certain conditions

    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


    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.

  2. #2
    New Member
    Join Date
    Apr 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel formula to auto-select a figure on certain conditions

    Hi baidya91.

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

    A B C
    1 18240 18500
    Sheet1

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

  3. #3
    Board Regular
    Join Date
    Jun 2016
    Location
    India
    Posts
    114
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel formula to auto-select a figure on certain conditions

    Quote Originally Posted by szita2000 View Post
    Hi baidya91.

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

    A B C
    1 18240 18500
    Sheet1

    Worksheet Formulas
    Cell Formula
    C1 =ROUNDUP((A1*2),-3)/2
    Sir, this doesn't apply to other figures. For example, it does not prroduce 33400 in case of 12750. It produces 33000 instead....

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •