Top 25% of times

[ultra vires]

Morning I hope you can help,

I have a long list of times and I need to know the time which is the start of the 25% latest times. I also need to know the value of the end of the earliest 25%.

After trying things like quartile I have run out of ideas.

Here is the data...
MON TUE WED
18:18:00 17:26:00 16:20:00
18:10:00 15:00:00 17:32:00
17:58:00 16:38:00 15:45:00
16:46:00 15:10:00 15:10:00
19:07:00 18:29:00 16:49:00
15:10:00 13:35:00 16:09:00
17:18:00 16:43:00 16:17:00
18:52:00 16:36:00 15:37:00
17:30:00 15:14:00 16:09:00
17:43:00 16:59:00 15:17:00
16:20:00 16:33:00 16:45:00
18:10:00 15:53:00 17:23:00
17:20:00 15:08:00 16:35:00
15:58:00 17:52:00 16:15:00
17:15:00 16:08:00 14:22:00
16:40:00 16:21:00 17:05:00
17:53:00 16:52:00 16:18:00
18:28:00 17:17:00 16:00:00
18:56:00 09:49:00 16:15:00

[NBVC]

Assuming your data is located in A2:C20, try the following:

For start of 25% latest times: =SMALL($A$2:$C$20,COUNT($A$2:$C$20)*0.75)

For end of 25% earliest times: =SMALL($A$2:$C$20,COUNT($A$2:$C$20)*0.25)

I think these should do...

[MSP77079]

How are you using MON, TUE, WED?

If I ignore MON, TUE, WED, quartile seems to work fine for me. I start running into problems if I attempt to turn the time values into date+time values.

If I replace MON, TUE, WED with actual dates, then create 3 new columns that are the addition of the dates with the times, again the quartile function seems to work fine and give the expected results.

What is not working for me (not yet, anyway) is attempting to skip the step of creating the 3 new columns and do the addition within an array formula. It works for MIN and MAX because they accept multiple input ranges. Quartile, on the other hand, gets confused. It will accept the union of ranges. It will accept being put inside an array formula where I am adding a single cell for date with multiple cells for time. But, it will NOT accept the same thing if I attempt to create the union of 2 or more such ranges.

[ultra vires]

Thanks for the help, I had put the headings in the group, and left it as a date time field - doh.