Intra Quartile average with a conditional

[Dandada2602]

I need to calculate the average transaction time of 25% longest transactions (in minutes) for each day. It may seems a calculation with the 3rd quartile but I don't know how or if its possible. This is an extract of the sample data:

Thanks in advance for your help,

Regards,
Dandada26

 

[joeu2004]

@Dandada26.... Please confirm that you want the average of the largest 25% (above 3rd quartile), as stated in the body of your posting, not the average of the middle 50% (between 2nd and 3rd quartiles), which is the intraquartile (sic) as stated in the posting subject.

Assuming that is correct, try the following.

Book1
	A	B	C	D	E
1
2	10/5/2020	0:18:41
3	10/5/2020	0:48:26
4	10/5/2020	0:35:26
5	10/5/2020	0:16:20
6	10/5/2020	0:26:40			Q3 Average
7	10/5/2020	0:15:06		10/5/2020	0:52:42.667
8	10/5/2020	0:37:00		10/6/2020	1:09:17.250
9	10/5/2020	0:21:21
10	10/5/2020	1:00:16
11	10/5/2020	0:49:26
12	10/6/2020	1:35:02
13	10/6/2020	0:24:05
14	10/6/2020	0:58:36
15	10/6/2020	0:14:43
16	10/6/2020	1:01:18
17	10/6/2020	0:50:13
18	10/6/2020	1:02:13
19	10/6/2020	0:49:52
20	10/6/2020	0:11:26
21	10/6/2020	0:15:47
22	10/6/2020	0:54:06
23	10/6/2020	0:16:18
24	10/6/2020	0:29:22
25	10/6/2020	0:30:54
26	10/6/2020	0:30:23
Sheet1

Formulas:
E7: { =AVERAGEIFS($B$2:$B$26, $A$2:$A$26, D7, $B$2:$B$26, ">=" & QUARTILE(IF($A$2:$A$26=D7, $B$2:$B$26), 3)) }
E8: { =AVERAGEIFS($B$2:$B$26, $A$2:$A$26, D8, $B$2:$B$26, ">=" & QUARTILE(IF($A$2:$A$26=D8, $B$2:$B$26), 3)) }

The formuals are array-entered. Omit the curly braces, and press ctrl+shift+Enter instead of just Enter.

Format E7:E8 as Custom [h]:mm:ss.000 if you want millisecond precision.

PS.... I just noticed that you want the results "in minutes". In that case, format E7:E8 as Number and append *1440 at the end of the formula (still array-entered). For example:

{ =AVERAGEIFS($B$2:$B$26, $A$2:$A$26, D7, $B$2:$B$26, ">=" & QUARTILE(IF($A$2:$A$26=D7, $B$2:$B$26), 3))*1440 }

 

[Dandada2602]

@Dandada26.... Please confirm that you want the average of the largest 25% (above 3rd quartile), as stated in the body of your posting, not the average of the middle 50% (between 2nd and 3rd quartiles), which is the intraquartile (sic) as stated in the posting subject.

Assuming that is correct, try the following.

Book1
	A	B	C	D	E
1
2	10/5/2020	0:18:41
3	10/5/2020	0:48:26
4	10/5/2020	0:35:26
5	10/5/2020	0:16:20
6	10/5/2020	0:26:40			Q3 Average
7	10/5/2020	0:15:06		10/5/2020	0:52:42.667
8	10/5/2020	0:37:00		10/6/2020	1:09:17.250
9	10/5/2020	0:21:21
10	10/5/2020	1:00:16
11	10/5/2020	0:49:26
12	10/6/2020	1:35:02
13	10/6/2020	0:24:05
14	10/6/2020	0:58:36
15	10/6/2020	0:14:43
16	10/6/2020	1:01:18
17	10/6/2020	0:50:13
18	10/6/2020	1:02:13
19	10/6/2020	0:49:52
20	10/6/2020	0:11:26
21	10/6/2020	0:15:47
22	10/6/2020	0:54:06
23	10/6/2020	0:16:18
24	10/6/2020	0:29:22
25	10/6/2020	0:30:54
26	10/6/2020	0:30:23
Sheet1

Formulas:
E7: { =AVERAGEIFS($B$2:$B$26, $A$2:$A$26, D7, $B$2:$B$26, ">=" & QUARTILE(IF($A$2:$A$26=D7, $B$2:$B$26), 3)) }
E8: { =AVERAGEIFS($B$2:$B$26, $A$2:$A$26, D8, $B$2:$B$26, ">=" & QUARTILE(IF($A$2:$A$26=D8, $B$2:$B$26), 3)) }

The formuals are array-entered. Omit the curly braces, and press ctrl+shift+Enter instead of just Enter.

Format E7:E8 as Custom [h]:mm:ss.000 if you want millisecond precision.

PS.... I just noticed that you want the results "in minutes". In that case, format E7:E8 as Number and append *1440 at the end of the formula (still array-entered). For example:

{ =AVERAGEIFS($B$2:$B$26, $A$2:$A$26, D7, $B$2:$B$26, ">=" & QUARTILE(IF($A$2:$A$26=D7, $B$2:$B$26), 3))*1440 }

You nailed it!! I wanted the average above 3rd quartile.

Thanks joeu2004!

 

[joeu2004]

You nailed it!! I wanted the average above 3rd quartile. Thanks joeu2004!

You're welcome. And just for the record, the intraquartile (middle 50%) is between the __1st__ and 3rd quartiles, not the 2nd (sic) and 3rd quartiles, as I had written. My bad!