IF formula based on a value between 2 numbers, and also matching specific criteria
Results 1 to 4 of 4

Thread: IF formula based on a value between 2 numbers, and also matching specific criteria

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF formula based on a value between 2 numbers, and also matching specific criteria

    Hello,

    I have a group of employees that have results for various metrics. I want to assign a Score for each of these metrics based on specific values that their result is between. For example, one metric might be quality, and I might have scores 1-5 where 5 is anything greater than 98%, 4 is anything greater than or equal to 94% and less than 98%, 3 is anything greater than or equal to 91% and less than 94% and so on.

    Additionally, depending on how long that employee has been working, the values for each score might change. For example the above scores might be for employees that have been here longer than 1 month. Employees that have been here less than 1 month might have the following: 5 is anything greater than 95%, 4 is anything greater than or equal to 90% and less than 95%, and so on.

    I track each metric for each employee monthly, and each month I assign them a setting based on how long they've been an employee. So I have one Excel row for July 2019 for Jane Smith who is New Hire Month 1 - Quality, and a 2nd row for August 2019 for Jane Smith who is New Hire Month 2 - Quality.

    I'm trying set up an excel sheet for another group to manage, where they have to do as little manual entry as possible. I'd like for them to simply be able to select New Hire Month 1 or Month 2, enter the Quality result for that agent that month, and have a Score field that can look up the appropriate score using the appropriate ranges based on the New Hire Month 1 or Month 2 selection. I'm failing miserably at accomplishing this any help is appreciated!

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,248
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: IF formula based on a value between 2 numbers, and also matching specific criteria

    Hi,

    Try this:

    ABCDEFGHIJKLMNO
    1012345
    2Month 10%75%80%85%90%95%NameMonthAssignMetric ResultScore
    3Month 20%85%88%91%94%98%Jane SmithJulyNew Hire Month 184%2
    4Jane SmithAugustMonth 284%0

    Sheet1



    Worksheet Formulas
    CellFormula
    O3=LOOKUP(N3,(INDEX($B$2:$G$3,MATCH(RIGHT(M3,7),$A$2:$A$3,0),0)),$B$1:$G$1)
    O4=LOOKUP(N4,(INDEX($B$2:$G$3,MATCH(RIGHT(M4,7),$A$2:$A$3,0),0)),$B$1:$G$1)

    Last edited by jorismoerings; Aug 15th, 2019 at 01:54 AM.
    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF formula based on a value between 2 numbers, and also matching specific criteria

    This is exactly what I needed, thank you!

    I have one new question now....for one of my metrics (efficiency) a higher score is given for a lower time (example below), so this formula isn't assigning the proper score for this metric. Thoughts on how can I adjust it so that for the majority of my metrics a higher score is given for a higher result, but for this one metric a higher score is given for a lower result?

    0 1 2 3 4 5
    Month 1 12:00 7:00 6:30 6:00 5:30 5:00
    Month 2 12:00 6:00 5:30 5:00 4:30 4:00

  4. #4
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,248
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: IF formula based on a value between 2 numbers, and also matching specific criteria

    Hi,

    Something like this:
    ABCDEFGHIJKLM
    1012345NameMonthAssignMetric ResultScore
    2Month 10%75%80%85%90%95%Jane SmithJulyNew Hire Month 184%2
    3Month 20%85%88%91%94%98%Jane SmithAugustMonth 284%0
    4
    5
    6
    7543210NameMonthAssignMetric ResultScore
    8Month 100:0005:0005:3006:0006:3007:00Jane SmithJulyNew Hire Month 105:154
    9Month 200:0004:0004:3005:0005:3006:00Jane SmithAugustMonth 206:150

    Sheet2



    Worksheet Formulas
    CellFormula
    M2=LOOKUP(L2,(INDEX($B$2:$G$3,MATCH(RIGHT(K2,7),$A$2:$A$3,0),0)),$B$1:$G$1)
    M3=LOOKUP(L3,(INDEX($B$2:$G$3,MATCH(RIGHT(K3,7),$A$2:$A$3,0),0)),$B$1:$G$1)
    M8=LOOKUP(L8,(INDEX($B$8:$G$9,MATCH(RIGHT(K8,7),$A$8:$A$9,0),0)),$B$7:$G$7)
    M9=LOOKUP(L9,(INDEX($B$8:$G$9,MATCH(RIGHT(K9,7),$A$8:$A$9,0),0)),$B$7:$G$7)

    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

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
  •