Share via

Excel Formula Help!!!

Anonymous
2022-03-09T20:40:49+00:00

Hello!

I have been combing my memory banks and google searches to no avail. I am looking to take information from one section of an Excel sheet to provide a piece of the formula and then have the formula then compare that data to a scale and output a value.

No matter how I try I can't seem to get it to look at the information exactly as I need.

Example:

I'm trying to use a tenure to determine which scale to use for performance reviews and based on the performance review result then produce a final compensation value.

So, Employee A has been with us since 8/2/20. (1 year and 6 months for this example)

Based on this, and their performance on a scale of 1-5 for this employee is say the following:

Avg Monthly Score < 1 Year > 1 Year but < 2 Years 2 Years +
1 $ - $ - $ -
2 $ - $ - $ -
3 $ 0.75 $ 1.00 COLA
4 $ 1.00 $ 1.50 COLA*150%
5 $ 1.25 $ 2.00 COLA*175%

I need a formula that will look at Tenure, then the scale, then output the value based on the level of performance.

i.e - 1 year and 6 month tenure, Performance rating of 3. Formula should look at middle column and present a value of $1.00 in the cell I'm trying to get it to report to.

I've tried various VLOOKUP, IFTHEN, IFAND, and other googled formulas to no avail.

Any help would be AMAZING as I am at my wits end! I am fairly certain it is something so simple, I'll kick myself once someone says "Have you tried this?"

Thanks!
RP

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points
    2022-03-09T22:30:12+00:00

    We cannot compare text such as "1 years, 6 months" to text values such as "> 1 Year but < 2 Years".

    The formula that I posted gets around that problem.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2022-03-09T21:44:12+00:00

    Hi

    My name is André. I am an independent consultant.

    I believe what you are looking for is something like this:

    =IF(DATEDIF(I6;TODAY();"y")<1;VLOOKUP(H6;A2:D6;2;0);IF(DATEDIF(I6;TODAY();"y")<2;VLOOKUP(H6;A2:D6;3;0);VLOOKUP(H6;A2:D6;4;0)))

    (Remembering that I'm using a semicolon as a separator)

    Answer here so I can continue helping you.

    André.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points
    2022-03-09T21:34:51+00:00

    Like this:

    Formula in I2: =INDEX($B$2:$D$6,H2,DATEDIF(G2,TODAY(),"Y")+1)

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-03-09T22:18:38+00:00

    André-

    Thanks again to you! I appreciate the assist. Also a good look, but trying to do this:

    Looking to take D7 to look at E7:H11 to determine which scale to use after looking at B5 and have that entered into B7.

    I truly appreciate your insight!

    RP

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-03-09T22:15:35+00:00

    HansV-

    Thank you for the quick response! This is one way, but not exactly what I was looking for. Maybe this'll illustrate better what I'm trying to get to. Looking to take D7 to look at E7:H11 to determine which scale to use after looking at B5 and have that entered into B7.

    Thank you so much for the assist!

    Was this answer helpful?

    0 comments