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

[jhainsworth]

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!

 

[jorismoerings]

Hi,

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		0	1	2	3	4	5
2	Month 1	0%	75%	80%	85%	90%	95%				Name	Month	Assign	Metric Result	Score
3	Month 2	0%	85%	88%	91%	94%	98%				Jane Smith	July	New Hire Month 1	84%	2
4											Jane Smith	August	Month 2	84%	0
Sheet1

Cell Formulas
Range	Formula
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)

 

[jhainsworth]

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

<tbody>
</tbody>

 

[jorismoerings]

Hi,

Something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		0	1	2	3	4	5		Name	Month	Assign	Metric Result	Score
2	Month 1	0%	75%	80%	85%	90%	95%		Jane Smith	July	New Hire Month 1	84%	2
3	Month 2	0%	85%	88%	91%	94%	98%		Jane Smith	August	Month 2	84%	0
4
5
6
7		5	4	3	2	1	0		Name	Month	Assign	Metric Result	Score
8	Month 1	00:00	05:00	05:30	06:00	06:30	07:00		Jane Smith	July	New Hire Month 1	05:15	4
9	Month 2	00:00	04:00	04:30	05:00	05:30	06:00		Jane Smith	August	Month 2	06:15	0
Sheet2

Cell Formulas
Range	Formula
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)