IFS formula with TIME

[Jewells0905]

I am having issues getting this to generate, I was getting an error, but am now getting a NAME error. I am trying to generate labels (A,B,C,D) for a group of racers based on their completion time. There are four groups A, B, C, and D with times <=1:45:00, <= 2:00:00, <= 2:15:00, and > 2:15:00, respectively. The formula I am currently using is below. =IFS(C2TIME(1, 45, 0), "A", C2TIME(2, 0, 0), "B", C2TIME(2, 15, 0), "C", "TRUE", C2TIME(2, 15, 0), "D") the racers times are in column C. Any help is appreciated, please let me know if you need more info. I tried to upload a mini sheet and a photo but for some reason, it won't allow me to. Below is a snip of what I am attempting to solve

First Name	Last Name	Time	Time behind winner	Group	Winning time	Average time	Slowest time
George	Adams	2:16:47	0:46:44	#NAME?	1:30:04	1:59:37	2:29:04
Lucia	Adams	1:51:52	0:21:48		1:30:04	1:59:37	2:29:04
Byron	Alexander	2:20:00	0:49:57
Audrey	Alexander	1:48:50	0:18:46
Olivia	Armstrong	2:16:12	0:46:09
Cadie	Armstrong	1:39:48	0:09:44
Byron	Bailey	2:03:36	0:33:33
Walter	Baker	2:19:22	0:49:18
Lydia	Baker	1:39:42	0:09:39

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E
1	First Name	Last Name	Time	Time behind winner	Group
2	George	Adams	02:16:47	00:46:44	D
3	Lucia	Adams	01:51:52	00:21:48	B
4	Byron	Alexander	02:20:00	00:49:57	D
5	Audrey	Alexander	01:48:50	00:18:46	B
6	Olivia	Armstrong	02:16:12	00:46:09	D
7	Cadie	Armstrong	01:39:48	00:09:44	A
8	Byron	Bailey	02:03:36	00:33:33	C
9	Walter	Baker	02:19:22	00:49:18	D
10	Lydia	Baker	01:39:42	00:09:39	A
Data

Cell Formulas
Range	Formula
E2:E10	E2	=IF(C2<=TIME(1, 45, 0), "A",IF(C2<=TIME(2, 0, 0),"B",IF(C2<=TIME(2, 15, 0),"C", "D")))

 

[Jewells0905]

=IF(C2<=TIME(1, 45, 0), "A",IF(C2<=TIME(2, 0, 0),"B",IF(C2<=TIME(2, 15, 0),"C", "D")))

Hi,

Thank you so much for your response, that formula did generate the labels I am needing, however, I am required to use the IFS function instead of just the IF function.

 

[Fluff]

I am required to use the IFS function

Is this some sort of homework?

 

[Jewells0905]

It is for a data literacy course I'm taking to learn data analysis.

 

[Fluff]

Personally I don't use the IFS function as it's less efficient than using nested IF functions.
That said, you can just use the construct I showed but using IFS instead

 

[Jewells0905]

I agree completely about the IF functions being more efficient, I did try to update the formula you provided with the IFS function and am getting an error. I attempted it a couple of different ways just to see if anything would work.=IFS(C3<=TIME(1, 45, 0), "A",IF(C3<=TIME(2, 0, 0),"B",IF(C3<=TIME(2, 15, 0),"C", "D"))) is giving this error ErrorIFS expects all arguments after position 0 to be in pairs. And this formula =IFS(C4<=TIME(1, 45, 0), "A",(C4<=TIME(2, 0, 0),"B",(C4<=TIME(2, 15, 0),"C", "D"))) is giving the parse error.

 

[Fluff]

In the 2nd one remove the ( from before the 2nd & 3rd C4 & add a true part ate the end like the formula you posted.

 

[Jewells0905]

In the 2nd one remove the ( from before the 2nd & 3rd C4 & add a true part ate the end like the formula you posted.

You are amazing! That worked! I've been trying to get this figured out for about 6 hours! Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.