Using If statement with a unique ID

[byorj]

Hello,

I am having trouble setting up a document. I am currently using Concatenate with an agents name and date to form a unique ID for that week. I am then trying to bring that data into a new sheet which marks the percentage 1-5 based on ranges.

I have attached an example sheet but I can quite work out my 1-5 if statement formula with Unique IDs if anyone has any ideas, I would really appreciate the help.

Data sheet:

EXAMPLE SHEET.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Unique ID	Ticket Assignee	# Unsatisfied	# Satisfied	# Solved Tickets	Date	Week Num	Staffed Time	Pick Up %	Total Calls	Dial Attempts	Inbound	Outbound	Follow Up	Away	On Call	ACW	AVG Speed to Answer	Adherence	Month	Year
2	Agent 144200	Agent 1	1	26	123	04/01/2021	2	38:40:42	100.00%	34	34	33	1	02:16:37	00:49:59	04:04:35	02:19:33	00:00:06	82.40%	Jan	2021
3	Agent 244200	Agent 2	4	25	191	04/01/2021	2	35:41:09	100.00%	33	35	32	1	00:29:04	02:33:47	03:29:15	01:01:48	00:00:07	73.04%	Jan	2021
4	Agent 344200	Agent 3	1	12	109	04/01/2021	2	21:43:23	100.00%	11	12	11	0	00:10:08	00:31:53	01:00:18	00:19:56	00:00:06	83.33%	Jan	2021
5	Agent 444200	Agent 4	0	2	25	04/01/2021	2	00:00:00												Jan	2021
6	Agent 544200	Agent 5	1	18	182	04/01/2021	2	31:02:34	94.12%	18	17	16	2	00:33:10	01:16:12	01:13:09	00:46:16	00:00:06	59.56%	Jan	2021
7	Agent 644200	Agent 6	0	6	66	04/01/2021	2	00:00:00												Jan	2021
8	Agent 744200	Agent 7	0	36	431	04/01/2021	2	52:59:33	100.00%	19	19	19	0	00:26:00	00:42:36	01:16:00	01:01:59	00:00:04	68.94%	Jan	2021
9	Agent 844200	Agent 8	0	26	174	04/01/2021	2	36:00:13	97.78%	56	45	44	12	00:30:30	03:13:49	04:13:26	01:48:02	00:00:03	82.03%	Jan	2021
10	Agent 944200	Agent 9	1	26	145	04/01/2021	2	40:22:02	100.00%	43	36	33	10	00:37:57	01:54:44	04:39:05	01:55:12	00:00:03	87.68%	Jan	2021
11	Agent 1044200	Agent 10	1	16	180	04/01/2021	2	38:54:31	100.00%	34	30	30	4	00:13:06	00:51:00	02:30:18	00:51:39	00:00:03	94.70%	Jan	2021
12	Agent 1144200	Agent 11	1	28	183	04/01/2021	2	00:00:00												Jan	2021
13	Agent 1244200	Agent 12	0	15	94	04/01/2021	2	05:43:02	100.00%	11	11	11	0	00:00:00	00:00:00	02:57:08	00:16:10	00:00:05	80.87%	Jan	2021
14	Agent 1344200	Agent 13	3	30	154	04/01/2021	2	38:00:23	100.00%	37	39	37	0	01:01:51	01:22:13	03:38:25	01:23:35	00:00:08	85.09%	Jan	2021
15	Agent 1444200	Agent 14	0	0	4	04/01/2021	2	00:00:00												Jan	2021
16	Agent 1544200	Agent 15	0	30	310	04/01/2021	2	47:34:59	100.00%	47	47	45	2	00:00:00	00:07:28	02:26:13	01:24:59	00:00:06	92.77%	Jan	2021
17	Agent 1644200	Agent 16	1	11	97	04/01/2021	2	37:30:53	100.00%	27	27	26	1	00:00:00	02:46:00	02:55:25	03:28:41	00:00:07	91.28%	Jan	2021
18	Agent 1744200	Agent 17	0	1	23	04/01/2021	2	00:00:00												Jan	2021
19	Agent 1844200	Agent 18	0	14	129	04/01/2021	2	16:03:40	100.00%	9	9	9	0	00:10:26	00:05:41	01:15:20	00:45:18	00:00:06	93.90%	Jan	2021
20	Agent 1944200	Agent 19	0	1	12	04/01/2021	2	00:00:00												Jan	2021
21	Agent 2044200	Agent 20	2	25	189	04/01/2021	2	35:14:23	100.00%	17	12	12	5	01:01:11	00:57:08	01:24:45	00:36:29	00:00:07	70.04%	Jan	2021
22	Agent 2144200	Agent 21	0	1	10	04/01/2021	2	00:00:00												Jan	2021
23	Agent 2244200	Agent 22	0	10	95	04/01/2021	2	00:09:22	0.00%	1	0	0	1	00:00:00	00:00:00	00:09:17	00:00:03	00:00:00		Jan	2021
24	Agent 2344200	Agent 23	0	3	77	04/01/2021	2	14:41:06	100.00%	20	15	15	5	00:32:34	00:04:23	01:16:12	00:41:12	00:00:06	81.30%	Jan	2021
25	Agent 2444200	Agent 24	0	4	53	04/01/2021	2	10:03:05	91.67%	12	12	11	1	00:00:00	00:12:11	01:08:47	00:23:32	00:00:05		Jan	2021
26	Agent 2544200	Agent 25	1	41	139	04/01/2021	2	40:52:47	100.00%	25	24	23	2	00:30:58	00:20:09	02:41:39	03:56:17	00:00:10	92.22%	Jan	2021
27	Agent 2644200	Agent 26	0	0	8	04/01/2021	2	00:00:00												Jan	2021
28	Agent 2744200	Agent 27	0	0	9	04/01/2021	2	00:00:00												Jan	2021
29	Agent 2844200	Agent 28	1	17	174	04/01/2021	2	33:43:20	100.00%	23	22	22	1	00:17:32	01:04:44	02:19:35	00:37:44	00:00:06	75.53%	Jan	2021
30	Agent 2944200	Agent 29	0	5	38	04/01/2021	2	14:34:52	100.00%	14	14	14	0	00:59:48	01:24:25	01:31:45	01:53:03	00:00:05	62.65%	Jan	2021
31	Agent 3044200	Agent 30	0	6	54	04/01/2021	2	19:18:02	100.00%	15	14	14	1	00:08:41	00:34:51	02:08:58	00:23:16	00:00:06	86.95%	Jan	2021
32	Agent 3144200	Agent 31	1	36	193	04/01/2021	2	32:16:36	92.00%	23	25	23	0	02:09:08	02:40:02	02:27:48	01:31:06	00:00:09	64.15%	Jan	2021
33	Agent 3244200	Agent 32	0	21	151	04/01/2021	2	31:08:07	96.43%	27	28	27	0	00:39:33	00:55:58	01:38:25	02:03:57	00:00:04	58.86%	Jan	2021
34	Agent 3344200	Agent 33	2	20	129	04/01/2021	2	38:05:12	80.00%	27	35	27	0	00:00:00	00:05:25	04:10:20	01:15:38	00:00:12	87.62%	Jan	2021
DATA

Cell Formulas
Range	Formula
A2:A34	A2	=CONCATENATE(B2, F2)

What I am looking for but based on unique IDs:

EXAMPLE SHEET.xlsx
	A	B	C	D	E	F
1	UNIQUE ID	AGENT NAME	DATE	ADHERENCE	PICK UP %	FOLLOW UP
2	Agent 144200	Agent 1	04/01/2021	4
3	Agent 244200	Agent 2	04/01/2021	4
4	Agent 344200	Agent 3	04/01/2021	4
5	Agent 444200	Agent 4	04/01/2021
6	Agent 544200	Agent 5	04/01/2021	2
7	Agent 644200	Agent 6	04/01/2021
8	Agent 744200	Agent 7	04/01/2021	3
9	Agent 844200	Agent 8	04/01/2021	4
10	Agent 944200	Agent 9	04/01/2021	5
11	Agent 1044200	Agent 10	04/01/2021	5
12	Agent 1144200	Agent 11	04/01/2021
13	Agent 1244200	Agent 12	04/01/2021	4
14	Agent 1344200	Agent 13	04/01/2021	5
15	Agent 1444200	Agent 14	04/01/2021
16	Agent 1544200	Agent 15	04/01/2021	5
17	Agent 1644200	Agent 16	04/01/2021	5
18	Agent 1744200	Agent 17	04/01/2021
19	Agent 1844200	Agent 18	04/01/2021	5
20	Agent 1944200	Agent 19	04/01/2021
21	Agent 2044200	Agent 20	04/01/2021	4
22	Agent 2144200	Agent 21	04/01/2021
23	Agent 2244200	Agent 22	04/01/2021
24	Agent 2344200	Agent 23	04/01/2021	4
25	Agent 2444200	Agent 24	04/01/2021
26	Agent 2544200	Agent 25	04/01/2021	5
27	Agent 2644200	Agent 26	04/01/2021
28	Agent 2744200	Agent 27	04/01/2021
29	Agent 2844200	Agent 28	04/01/2021	4
30	Agent 2944200	Agent 29	04/01/2021	3
31	Agent 3044200	Agent 30	04/01/2021	5
32	Agent 3144200	Agent 31	04/01/2021	3
33	Agent 3244200	Agent 32	04/01/2021	2
34	Agent 3344200	Agent 33	04/01/2021	5
Sheet2

Cell Formulas
Range	Formula
A2:A34	A2	=CONCATENATE(B2, C2)
D2:D34	D2	=IF(DATA!S2>=85%,5,IF(DATA!S2>=70%,4,IF(DATA!S2>=60%,3,IF(DATA!S2>=50%,2,IF(DATA!S2>=40%,1,"")))))

Thank you!

 

[Fluff]

Do you have the new LET function?

 

[byorj]

Do you have the new LET function?

I do yes

 

[jasonb75]

Perhaps this will do what you need?

=IFERROR(MATCH(XLOOKUP(A2,Data!$A$2:$A$34,Data!$S$2:$S$34,0),{40,50,60,70,85}%),"")

 

[byorj]

Perhaps this will do what you need?

=IFERROR(MATCH(XLOOKUP(A2,Data!$A$2:$A$34,Data!$S$2:$S$34,0),{40,50,60,70,85}%),"")

That does work, but how do I edit the ranges to what shows 1-5 vs the percentage?

Thanks,

 

[jasonb75]

You can edit the percentage ranges with the numbers in this part

{40,50,60,70,85}

Note that the numbers are, and must be in ascending order, the first value on the left will always return 1, the 2nd value will return 2 and so on.
Anything that is not an exact match for one of the numbers will be rounded down. Anything less than the first value will cause an error and return a blank.

 

[byorj]

That's perfect thank you!

Is there a way to say if lower than 40, show 1? would <40,50,50,70,85 work? also, would this work with times? I have a part of the data that would be in hours and minutes.

 

[jasonb75]

No, you can't use the < symbol in this type of formula, it needs to be the lowest permissible number.
This would give you 1 for anything from 0 to 39.99 but it would mean that all of the other numbers increase by 1, e.g. 40-49% changes from 1 to 2, 85%+ changes from 5 to 6.

=IFERROR(MATCH(XLOOKUP(A2,Data!$A$2:$A$34,Data!$S$2:$S$34,0),{0,40,50,60,70,85}%),"")

It could work for times as well, but not quite so easily. Depending on the scale to be used it might be easier with a lookup table.

 

[byorj]

No, you can't use the < symbol in this type of formula, it needs to be the lowest permissible number.
This would give you 1 for anything from 0 to 39.99 but it would mean that all of the other numbers increase by 1, e.g. 40-49% changes from 1 to 2, 85%+ changes from 5 to 6.

=IFERROR(MATCH(XLOOKUP(A2,Data!$A$2:$A$34,Data!$S$2:$S$34,0),{0,40,50,60,70,85}%),"")

It could work for times as well, but not quite so easily. Depending on the scale to be used it might be easier with a lookup table.

Thank you that clears it up. With the times it would be for a duration doing a specific task. So 00:30:00 is 5, 01:00:00, 4 and so on.

EXAMPLE SHEET.xlsx
	P
1	Follow Up
2	02:16:37
3	00:29:04
4	00:10:08
5
6	00:33:10
7
8	00:26:00
9	00:30:30
10	00:37:57
11	00:13:06
12
13	00:00:00
14	01:01:51
15
16	00:00:00
17	00:00:00
18
19	00:10:26
20
21	01:01:11
22
23	00:00:00
24	00:32:34
25	00:00:00
26	00:30:58
27
28
29	00:17:32
30	00:59:48
31	00:08:41
32	02:09:08
33	00:39:33
34	00:00:00
35	00:26:41
36	01:15:54
37	01:33:45
38
39	00:30:53
40
41	00:20:17
42	00:30:40
43	00:42:57
44	00:03:17
45
46	00:41:15
47	00:57:12
48
49	00:04:16
50	00:00:00
51
52	00:00:00
53
54	01:34:40
55
56	00:00:00
57	00:41:10
58	00:00:00
59	03:18:00
60
61	00:15:16
62	00:35:28
63	02:26:41
64	00:38:11
65	01:17:39
66	01:45:41
67	00:10:50
DATA

 

[jasonb75]

Using a different approach, is this giving the correct results?
I've set it up so that 00:00:00 to 00:30:00 returns 1, 00:30:01 to 01:00:00 returns 2, etc in 30 minute intervals. Anything greater than 02:00:00 will return 1
This might be a bit harder to follow so I'll hold back on the 'how it works' until we know it does

Book1 (version 1).xlsb
	F	G
1	FOLLOW UP	Result
2	02:16:37	1.00
3	00:29:04	5.00
4	00:10:08	5.00
5
6	00:33:10	4.00
7
8	00:26:00	5.00
9	00:30:30	4.00
10	00:37:57	4.00
11	00:13:06	5.00
12
13	00:00:00	5.00
14	01:01:51	3.00
15
16	00:00:00	5.00
17	00:00:00	5.00
18
19	00:10:26	5.00
20
21	01:01:11	3.00
22
23	00:00:00	5.00
24	00:32:34	4.00
25	00:00:00	5.00
26	00:30:58	4.00
27
28
29	00:17:32	5.00
30	00:59:48	4.00
31	00:08:41	5.00
32	02:09:08	1.00
33	00:39:33	4.00
34	00:00:00	5.00
Sheet4

Cell Formulas
Range	Formula
G2:G34	G2	=IF(F2="","",MEDIAN(5-INT((F2-"0:00:01")/"00:30"),1,5))