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!

 

[byorj]

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))

Hey,

That does work, but how would I match it to the unique ID's?

Thanks,

 

[jasonb75]

Like this?

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1	UNIQUE ID	AGENT NAME	DATE	ADHERENCE	PICK UP %	FOLLOW UP	Result
2	Agent 144200	Agent 1	44200	4		02:16:37	1.00
3	Agent 244200	Agent 2	44200	4		00:29:04	5.00
4	Agent 344200	Agent 3	44200	4		00:10:08	5.00
5	Agent 444200	Agent 4	44200
6	Agent 544200	Agent 5	44200	2		00:33:10	4.00
7	Agent 644200	Agent 6	44200
8	Agent 744200	Agent 7	44200	3		00:26:00	5.00
9	Agent 844200	Agent 8	44200	4		00:30:30	4.00
10	Agent 944200	Agent 9	44200	5		00:37:57	4.00
11	Agent 1044200	Agent 10	44200	5		00:13:06	5.00
12	Agent 1144200	Agent 11	44200
13	Agent 1244200	Agent 12	44200	4		00:00:00	5.00
14	Agent 1344200	Agent 13	44200	5		01:01:51	3.00
15	Agent 1444200	Agent 14	44200
16	Agent 1544200	Agent 15	44200	5		00:00:00	5.00
17	Agent 1644200	Agent 16	44200	5		00:00:00	5.00
18	Agent 1744200	Agent 17	44200
19	Agent 1844200	Agent 18	44200	5		00:10:26	5.00
20	Agent 1944200	Agent 19	44200
21	Agent 2044200	Agent 20	44200	4		01:01:11	3.00
22	Agent 2144200	Agent 21	44200
23	Agent 2244200	Agent 22	44200			00:00:00	5.00
24	Agent 2344200	Agent 23	44200	4		00:32:34	4.00
25	Agent 2444200	Agent 24	44200			00:00:00	5.00
26	Agent 2544200	Agent 25	44200	5		00:30:58	4.00
27	Agent 2644200	Agent 26	44200
28	Agent 2744200	Agent 27	44200
29	Agent 2844200	Agent 28	44200	4		00:17:32	5.00
30	Agent 2944200	Agent 29	44200	3		00:59:48	4.00
31	Agent 3044200	Agent 30	44200	5		00:08:41	5.00
32	Agent 3144200	Agent 31	44200	3		02:09:08	1.00
33	Agent 3244200	Agent 32	44200	2		00:39:33	4.00
34	Agent 3344200	Agent 33	44200	5		00:00:00	5.00
Sheet4

Cell Formulas
Range	Formula
F2:F34	F2	=IFERROR(--(XLOOKUP(A2,Data!$A$2:$A$34,Data!$N$2:$N$34,"")&""),"")
G2:G34	G2	=IF(F2="","",MEDIAN(5-INT((F2-"0:00:01")/"00:30"),1,5))
A2:A34	A2	=CONCATENATE(B2, C2)
D2:D34	D2	=IFERROR(MATCH(XLOOKUP(A2,Data!$A$2:$A$34,Data!$S$2:$S$34,0),{40,50,60,70,85}%),"")

 

[byorj]

That worked perfect! Thank you so much for your help!

Could you help me to understand the time ranges a little better? Just incase I need to change these?

 

[jasonb75]

Sure, breaking it down, 1 and 5 are the upper and lower limits of result scale.

=IF(F2="","",MEDIAN(5-INT((F2-"0:00:01")/"00:30"),1,5))

The subtracted second is so that the upper limit of each 30 minute window is included in the time range, without this you would get 2 as the result for 00:30:00 to 00:59:59 instead of 00:30:01 to 01:00:00, with the same difference affecting all of the time ranges.

=IF(F2="","",MEDIAN(5-INT((F2-"0:00:01")/"00:30"),1,5))

Finally, "00:30" is the time increment required for the scale.

Hopefully I didn't miss anything.

 

[byorj]

Sure, breaking it down, 1 and 5 are the upper and lower limits of result scale.

=IF(F2="","",MEDIAN(5-INT((F2-"0:00:01")/"00:30"),1,5))

The subtracted second is so that the upper limit of each 30 minute window is included in the time range, without this you would get 2 as the result for 00:30:00 to 00:59:59 instead of 00:30:01 to 01:00:00, with the same difference affecting all of the time ranges.

=IF(F2="","",MEDIAN(5-INT((F2-"0:00:01")/"00:30"),1,5))

Finally, "00:30" is the time increment required for the scale.

Hopefully I didn't miss anything.

That's great. So the ranges would be:

00:00:01-00:30:00 = 5
00:30:00-01:00:00 = 4
01:00:00-01:30:00 = 3
01:30:00-02:00:00 = 2
02:00:00-02:30:00 = 1

Would I be able to increase these? I tried editing the "00:30" to "01:00:00" in the following formula but it didn't change anything =IF(F2="","",MEDIAN(5-INT((F2-"0:00:01")/"00:30"),1,5))

This would be the perfect range:

00:00:01-01:00:00 = 5
01:00:00-02:00:00 = 4
02:00:00-02:30:00 = 3
02:30:00-03:00:00 = 2
03:00:00+ = 1

 

[jasonb75]

Your edit to the formula should work as far as I can see, just to be sure the edited version should be

=IF(F2="","",MEDIAN(5-INT((F2-"0:00:01")/"01:00:00"),1,5))

Are you saying that it was giving the same results as the formula with the 30 minute time ranges?

A slight correction on the ranges though,

00:00:01-01:00:00 = 5
01:00:01-02:00:00 = 4
02:00:01-02:30:00 = 3
02:30:01-03:00:00 = 2
03:00:01+ = 1

 

[byorj]

Your edit to the formula should work as far as I can see, just to be sure the edited version should be

=IF(F2="","",MEDIAN(5-INT((F2-"0:00:01")/"01:00:00"),1,5))

Are you saying that it was giving the same results as the formula with the 30 minute time ranges?

A slight correction on the ranges though,

00:00:01-01:00:00 = 5
01:00:01-02:00:00 = 4
02:00:01-02:30:00 = 3
02:30:01-03:00:00 = 2
03:00:01+ = 1

Thank you! That has made some changes now, I must have been missing something.

These ranges don't seem to fall right though:

01:00:01-02:00:00 = 4
02:00:01-02:30:00 = 3
02:30:01-03:00:00 = 2

For example, the following is showing up:

02:44:40 = 3 instead of 2
02:57:41 = 3 instead of 2

 

[jasonb75]

I must have been missing something.

No, that was me

I looked too quickly and missed that it was a mix of 1 hour and 30 minute ranges, the formula would only work with one or the other, not both.
Taking a different approach to it, this one looks like it works, although I have no idea how to explain the arrays that I've used in it ?

=IF(F2="","",LOOKUP(F2,TIME({0,1,2,2,3},{0,0,0,30,0},{0,1,1,1,1}),{5,4,3,2,1}))

See if this makes sense without a descriptive explanation to confuse things.
{0,1,2,2,3} Hour
{0,0,0,30,0} Minute
{0,1,1,1,1} Second
{5,4,3,2,1} Result

 

[byorj]

No, that was me

I looked too quickly and missed that it was a mix of 1 hour and 30 minute ranges, the formula would only work with one or the other, not both.
Taking a different approach to it, this one looks like it works, although I have no idea how to explain the arrays that I've used in it ?

=IF(F2="","",LOOKUP(F2,TIME({0,1,2,2,3},{0,0,0,30,0},{0,1,1,1,1}),{5,4,3,2,1}))

See if this makes sense without a descriptive explanation to confuse things.
{0,1,2,2,3} Hour
{0,0,0,30,0} Minute
{0,1,1,1,1} Second
{5,4,3,2,1} Result

That worked perfectly thank you! I think I understand your description as well.

If I wanted to change it to the following for example:

00:00:01-01:30:00 = 5
01:30:01-02:00:00 = 4
02:00:01-02:30:00 = 3
02:30:01-03:00:00 = 2
03:00:01+ = 1

=IF(F2="","",LOOKUP(F2,TIME({0,1,2,2,3},{0,30,0,30,0},{0,1,1,1,1}),{5,4,3,2,1}))

 

[jasonb75]

Absolutely spot on

Just remember that the first one always needs to start at 00:00:00 in order to avoid errors.