+ Reply to Thread
Results 1 to 9 of 9

Next Highest Value Plus One

  1. #1
    Registered User
    Join Date
    03-05-2020
    Location
    Coeur d'Alene, ID
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    4

    Question Next Highest Value Plus One

    Hi everyone,

    I have a spreadsheet of employees that shows their current pay grade and their current salary. We have created a new pay system with 14 steps within each pay grade. We need a formula that will lookup the correct grade and calculate the step that is closest to their current salary (must be higher than their current salary) plus one. I have tried lookups, INDEX and MATCH formulas and even the LARGER formula, but all I get is errors. Can anyone help? I have attached some sample employee data (Employee List worksheet) and a copy of the pay grades and steps (Pay System and Steps worksheet).

    Thanks!!
    Nanci
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Next Highest Value Plus One

    Closest Step Plus One
    What does it mean?

  3. #3
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Next Highest Value Plus One

    LIKE THIS
    Copy paste below in F3 in Employee sheet and then hold control and shift together then hit enter to make it array formula. and drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Above will give you the salary that can be paid now

    If your are interested to see the revised step for the employee then in G3 Copy paste below in F3 in Employee sheet and then hold control and shift together then hit enter to make it array formula. and drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula in the book is updated in order to hide the N/A errors check the attachment
    Attached Files Attached Files
    Last edited by hemesh; 03-05-2020 at 03:30 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  4. #4
    Registered User
    Join Date
    03-05-2020
    Location
    Coeur d'Alene, ID
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    4

    Re: Next Highest Value Plus One

    If you look at row 3, the employee's current pay grade is Grade 6 and their salary is $37,934.21. In looking at the Pay System with Steps worksheet, the closest step to their current salary would be Step 3 which has a recommended salary of $38,277. I would like to move them one more step past that to $39,998. But we have over 800 employees and I don't want to do that manually for each one. I need a formula that will do that for me.

  5. #5
    Registered User
    Join Date
    03-05-2020
    Location
    Coeur d'Alene, ID
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    4

    Re: Next Highest Value Plus One

    hemesh,

    Thank you for your reply. When I inserted your formula, I got #N/A as the result

  6. #6
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Next Highest Value Plus One

    Check the attachment in post #3

    copy paste the formula then hold control and shift together then hit enter to make it array formula.
    when you follow above steps then your formula will be surrounded by curly braces({ }) but these cannot be entered manually it has to be done by following above steps

  7. #7
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Next Highest Value Plus One

    =AGGREGATE(15,6,'Pay System with Steps'!$B$5:$R$20/('Pay System with Steps'!$A$5:$A$20=C3)/('Pay System with Steps'!$B$5:$R$20>E3),1)

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Next Highest Value Plus One

    Hi,

    One way in F3 copied down

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  9. #9
    Registered User
    Join Date
    03-05-2020
    Location
    Coeur d'Alene, ID
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    4

    Re: Next Highest Value Plus One

    This worked perfectly!!! Thank you so much!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 05-27-2019, 06:19 AM
  2. Replies: 3
    Last Post: 06-09-2016, 12:51 PM
  3. [SOLVED] Need to select earliest five rows matching highest value, and next highest if not enough
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-05-2016, 11:26 AM
  4. [SOLVED] Max formula to return total of highest, second highest and third highest value
    By JonWilf in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-06-2016, 08:20 AM
  5. 2 columns of data matching highest with highest
    By ronaldchristie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-03-2012, 07:57 AM
  6. VBA code to find highest and 2nd highest number based in criteria
    By Michael007 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-25-2011, 08:38 AM
  7. Replies: 3
    Last Post: 08-10-2006, 11:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1