Using If statement with a unique ID

byorj

New Member
Joined
Feb 28, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTU
1Unique IDTicket Assignee# Unsatisfied# Satisfied# Solved TicketsDateWeek NumStaffed TimePick Up %Total CallsDial AttemptsInboundOutboundFollow UpAwayOn CallACWAVG Speed to AnswerAdherenceMonthYear
2Agent 144200Agent 112612304/01/2021238:40:42100.00%343433102:16:3700:49:5904:04:3502:19:3300:00:0682.40%Jan2021
3Agent 244200Agent 242519104/01/2021235:41:09100.00%333532100:29:0402:33:4703:29:1501:01:4800:00:0773.04%Jan2021
4Agent 344200Agent 311210904/01/2021221:43:23100.00%111211000:10:0800:31:5301:00:1800:19:5600:00:0683.33%Jan2021
5Agent 444200Agent 4022504/01/2021200:00:00Jan2021
6Agent 544200Agent 511818204/01/2021231:02:3494.12%181716200:33:1001:16:1201:13:0900:46:1600:00:0659.56%Jan2021
7Agent 644200Agent 6066604/01/2021200:00:00Jan2021
8Agent 744200Agent 703643104/01/2021252:59:33100.00%191919000:26:0000:42:3601:16:0001:01:5900:00:0468.94%Jan2021
9Agent 844200Agent 802617404/01/2021236:00:1397.78%5645441200:30:3003:13:4904:13:2601:48:0200:00:0382.03%Jan2021
10Agent 944200Agent 912614504/01/2021240:22:02100.00%4336331000:37:5701:54:4404:39:0501:55:1200:00:0387.68%Jan2021
11Agent 1044200Agent 1011618004/01/2021238:54:31100.00%343030400:13:0600:51:0002:30:1800:51:3900:00:0394.70%Jan2021
12Agent 1144200Agent 1112818304/01/2021200:00:00Jan2021
13Agent 1244200Agent 120159404/01/2021205:43:02100.00%111111000:00:0000:00:0002:57:0800:16:1000:00:0580.87%Jan2021
14Agent 1344200Agent 1333015404/01/2021238:00:23100.00%373937001:01:5101:22:1303:38:2501:23:3500:00:0885.09%Jan2021
15Agent 1444200Agent 1400404/01/2021200:00:00Jan2021
16Agent 1544200Agent 1503031004/01/2021247:34:59100.00%474745200:00:0000:07:2802:26:1301:24:5900:00:0692.77%Jan2021
17Agent 1644200Agent 161119704/01/2021237:30:53100.00%272726100:00:0002:46:0002:55:2503:28:4100:00:0791.28%Jan2021
18Agent 1744200Agent 17012304/01/2021200:00:00Jan2021
19Agent 1844200Agent 1801412904/01/2021216:03:40100.00%999000:10:2600:05:4101:15:2000:45:1800:00:0693.90%Jan2021
20Agent 1944200Agent 19011204/01/2021200:00:00Jan2021
21Agent 2044200Agent 2022518904/01/2021235:14:23100.00%171212501:01:1100:57:0801:24:4500:36:2900:00:0770.04%Jan2021
22Agent 2144200Agent 21011004/01/2021200:00:00Jan2021
23Agent 2244200Agent 220109504/01/2021200:09:220.00%100100:00:0000:00:0000:09:1700:00:0300:00:00Jan2021
24Agent 2344200Agent 23037704/01/2021214:41:06100.00%201515500:32:3400:04:2301:16:1200:41:1200:00:0681.30%Jan2021
25Agent 2444200Agent 24045304/01/2021210:03:0591.67%121211100:00:0000:12:1101:08:4700:23:3200:00:05Jan2021
26Agent 2544200Agent 2514113904/01/2021240:52:47100.00%252423200:30:5800:20:0902:41:3903:56:1700:00:1092.22%Jan2021
27Agent 2644200Agent 2600804/01/2021200:00:00Jan2021
28Agent 2744200Agent 2700904/01/2021200:00:00Jan2021
29Agent 2844200Agent 2811717404/01/2021233:43:20100.00%232222100:17:3201:04:4402:19:3500:37:4400:00:0675.53%Jan2021
30Agent 2944200Agent 29053804/01/2021214:34:52100.00%141414000:59:4801:24:2501:31:4501:53:0300:00:0562.65%Jan2021
31Agent 3044200Agent 30065404/01/2021219:18:02100.00%151414100:08:4100:34:5102:08:5800:23:1600:00:0686.95%Jan2021
32Agent 3144200Agent 3113619304/01/2021232:16:3692.00%232523002:09:0802:40:0202:27:4801:31:0600:00:0964.15%Jan2021
33Agent 3244200Agent 3202115104/01/2021231:08:0796.43%272827000:39:3300:55:5801:38:2502:03:5700:00:0458.86%Jan2021
34Agent 3344200Agent 3322012904/01/2021238:05:1280.00%273527000:00:0000:05:2504:10:2001:15:3800:00:1287.62%Jan2021
DATA
Cell Formulas
RangeFormula
A2:A34A2=CONCATENATE(B2, F2)


What I am looking for but based on unique IDs:

EXAMPLE SHEET.xlsx
ABCDEF
1UNIQUE IDAGENT NAMEDATEADHERENCEPICK UP %FOLLOW UP
2Agent 144200Agent 104/01/20214
3Agent 244200Agent 204/01/20214
4Agent 344200Agent 304/01/20214
5Agent 444200Agent 404/01/2021 
6Agent 544200Agent 504/01/20212
7Agent 644200Agent 604/01/2021 
8Agent 744200Agent 704/01/20213
9Agent 844200Agent 804/01/20214
10Agent 944200Agent 904/01/20215
11Agent 1044200Agent 1004/01/20215
12Agent 1144200Agent 1104/01/2021 
13Agent 1244200Agent 1204/01/20214
14Agent 1344200Agent 1304/01/20215
15Agent 1444200Agent 1404/01/2021 
16Agent 1544200Agent 1504/01/20215
17Agent 1644200Agent 1604/01/20215
18Agent 1744200Agent 1704/01/2021 
19Agent 1844200Agent 1804/01/20215
20Agent 1944200Agent 1904/01/2021 
21Agent 2044200Agent 2004/01/20214
22Agent 2144200Agent 2104/01/2021 
23Agent 2244200Agent 2204/01/2021 
24Agent 2344200Agent 2304/01/20214
25Agent 2444200Agent 2404/01/2021 
26Agent 2544200Agent 2504/01/20215
27Agent 2644200Agent 2604/01/2021 
28Agent 2744200Agent 2704/01/2021 
29Agent 2844200Agent 2804/01/20214
30Agent 2944200Agent 2904/01/20213
31Agent 3044200Agent 3004/01/20215
32Agent 3144200Agent 3104/01/20213
33Agent 3244200Agent 3204/01/20212
34Agent 3344200Agent 3304/01/20215
Sheet2
Cell Formulas
RangeFormula
A2:A34A2=CONCATENATE(B2, C2)
D2:D34D2=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

New Member
Joined
Feb 28, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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
FG
1FOLLOW UPResult
202:16:371.00
300:29:045.00
400:10:085.00
5 
600:33:104.00
7 
800:26:005.00
900:30:304.00
1000:37:574.00
1100:13:065.00
12 
1300:00:005.00
1401:01:513.00
15 
1600:00:005.00
1700:00:005.00
18 
1900:10:265.00
20 
2101:01:113.00
22 
2300:00:005.00
2400:32:344.00
2500:00:005.00
2600:30:584.00
27 
28 
2900:17:325.00
3000:59:484.00
3100:08:415.00
3202:09:081.00
3300:39:334.00
3400:00:005.00
Sheet4
Cell Formulas
RangeFormula
G2:G34G2=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,
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,471
Office Version
  1. 365
Platform
  1. Windows
Like this?
Book1 (version 1).xlsb
ABCDEFG
1UNIQUE IDAGENT NAMEDATEADHERENCEPICK UP %FOLLOW UPResult
2Agent 144200Agent 144200402:16:371.00
3Agent 244200Agent 244200400:29:045.00
4Agent 344200Agent 344200400:10:085.00
5Agent 444200Agent 444200   
6Agent 544200Agent 544200200:33:104.00
7Agent 644200Agent 644200   
8Agent 744200Agent 744200300:26:005.00
9Agent 844200Agent 844200400:30:304.00
10Agent 944200Agent 944200500:37:574.00
11Agent 1044200Agent 1044200500:13:065.00
12Agent 1144200Agent 1144200   
13Agent 1244200Agent 1244200400:00:005.00
14Agent 1344200Agent 1344200501:01:513.00
15Agent 1444200Agent 1444200   
16Agent 1544200Agent 1544200500:00:005.00
17Agent 1644200Agent 1644200500:00:005.00
18Agent 1744200Agent 1744200   
19Agent 1844200Agent 1844200500:10:265.00
20Agent 1944200Agent 1944200   
21Agent 2044200Agent 2044200401:01:113.00
22Agent 2144200Agent 2144200   
23Agent 2244200Agent 2244200 00:00:005.00
24Agent 2344200Agent 2344200400:32:344.00
25Agent 2444200Agent 2444200 00:00:005.00
26Agent 2544200Agent 2544200500:30:584.00
27Agent 2644200Agent 2644200   
28Agent 2744200Agent 2744200   
29Agent 2844200Agent 2844200400:17:325.00
30Agent 2944200Agent 2944200300:59:484.00
31Agent 3044200Agent 3044200500:08:415.00
32Agent 3144200Agent 3144200302:09:081.00
33Agent 3244200Agent 3244200200:39:334.00
34Agent 3344200Agent 3344200500:00:005.00
Sheet4
Cell Formulas
RangeFormula
F2:F34F2=IFERROR(--(XLOOKUP(A2,Data!$A$2:$A$34,Data!$N$2:$N$34,"")&""),"")
G2:G34G2=IF(F2="","",MEDIAN(5-INT((F2-"0:00:01")/"00:30"),1,5))
A2:A34A2=CONCATENATE(B2, C2)
D2:D34D2=IFERROR(MATCH(XLOOKUP(A2,Data!$A$2:$A$34,Data!$S$2:$S$34,0),{40,50,60,70,85}%),"")
 

byorj

New Member
Joined
Feb 28, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,471
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 28, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
12,471
Office Version
  1. 365
Platform
  1. Windows
Your edit to the formula should work as far as I can see, just to be sure the edited version should be
Excel Formula:
=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

New Member
Joined
Feb 28, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Your edit to the formula should work as far as I can see, just to be sure the edited version should be
Excel Formula:
=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

Well-known Member
Joined
Dec 30, 2008
Messages
12,471
Office Version
  1. 365
Platform
  1. Windows
I must have been missing something.
No, that was me:oops:

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 🤪
Excel Formula:
=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

New Member
Joined
Feb 28, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
No, that was me:oops:

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 🤪
Excel Formula:
=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

Well-known Member
Joined
Dec 30, 2008
Messages
12,471
Office Version
  1. 365
Platform
  1. Windows
Absolutely spot on (y)

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

Watch MrExcel Video

Forum statistics

Threads
1,127,651
Messages
5,626,090
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top