Percentile of n most recent values

[Fitzy22]

Hi,

Please see simplified minisheet of my data below.

I would like to calculate the 80th percentile of the most recent (i.e. dates in Column A) 24 values (i.e. values in Column C) for each site (i.e. x and y in Column B). Where there is no value for a particular date it means there is no value recorded and therefore needs to be ignored and doesn't from part of the percentile calculation. So only the 24 most recent "values" should be used.

I hope this adequately explains my requirements, but if there are any questions please ask.

Thanks in advance...

Percentile_calculation.xlsx
	A	B	C
1	Date	Site	Value
2	25/12/2006	x
3	21/12/2007	x	1
4	21/12/2007	x	2
5	18/11/2008	x
6	18/11/2008	x
7	28/11/2008	y
8	28/11/2008	y	1
9	28/11/2008	y	2
10	28/11/2008	y
11	28/11/2008	y
12	28/11/2008	y
13	28/11/2008	x
14	12/01/2009	x
15	12/01/2009	x	1
16	12/01/2009	y	1
17	12/01/2009	y	1
18	12/01/2009	y
19	12/01/2009	x
20	12/01/2009	y
21	12/01/2009	x
22	12/01/2009	y
23	12/01/2009	x
24	17/01/2009	x
25	17/01/2009	x	2
26	17/01/2009	y
27	17/01/2009	y
28	17/01/2009	y
29	17/01/2009	x
30	1/01/2010	x
31	1/01/2010	x	10
32	15/02/2010	x
33	15/02/2010	x
34	15/02/2010	y	3
35	18/02/2010	x
36	18/02/2010	x
37	18/02/2010	x
38	18/02/2010	x
39	18/02/2010	x
40	17/01/2011	x	4
41	9/03/2011	x	8
42	9/05/2011	x	3
43	7/03/2012	x	1
44	26/02/2013	x	2
45	7/03/2013	x	1
46	1/03/2014	x	2.1
47	4/12/2014	x	3.4
48	4/12/2014	y	2.4
49	7/12/2014	y	1.2
50	7/12/2014	y	1.5
51	14/12/2014	x	2
52	14/12/2014	x	1.8
53	29/12/2014	x	2.7
54	16/01/2015	x	2.3
55	7/11/2015	x
56	6/02/2016	x	2.3
57	13/05/2016	x	3.2
58	13/05/2016	x	2.5
59	27/06/2016	y	1.1
60	24/09/2016	y	1.4
61	24/09/2016	y	1
62	24/02/2017	x	1.1
63	3/03/2018	x	4.1
64	5/03/2018	x	9.1
65	5/03/2018	x	9
66	5/03/2018	x	7.8
67	6/03/2018	x	2.8
68	8/03/2018	x	5.3
69	8/03/2018	x	5.4
70	8/03/2018	x	3.3
71	9/03/2018	x	7.8
72	24/02/2019	x	1.6
73	24/02/2019	x	3.2
74	31/03/2019	y	1.6
75	31/03/2019	y	3.7
76	25/01/2020	y	1.9
77	27/01/2020	x	6.7
78	27/01/2020	x	2.7
79	27/01/2020	x	1.3
80	5/02/2020	x	4.6
81	8/03/2020	y	4.4
82	8/03/2020	x	0.8
83	26/09/2020	x	1.6
84	26/09/2020	x	2.2
85	25/12/2020	x	1.7
86	10/03/2021	x	14
87	23/03/2021	x	1.4
88	24/06/2021	x	2.4
89	29/10/2021	x	4.2
90	21/01/2022	x	7.5
91	24/01/2022	x	12
92	26/01/2022	x	2.2
Sheet1

 

[BSALV]

Map1
	A	B	C	D	E	F	G	H	I
1	Date	Site	Value	date'	count	62
2	25/12/2006	x			large 24	43167	68	<<<<< on row
3	21/12/2007	x	1	39437	large 24 with row incl	43167,01	69	<<<<< on row
4	21/12/2007	x	2	39437
5	18/11/2008	x
6	18/11/2008	x			your answeer	5,92
7	28/11/2008	y
8	28/11/2008	y	1	39780
9	28/11/2008	y	2	39780
10	28/11/2008	y
11	28/11/2008	y
12	28/11/2008	y
13	28/11/2008	x
14	12/01/2009	x
15	12/01/2009	x	1	39825
16	12/01/2009	y	1	39825
17	12/01/2009	y	1	39825
18	12/01/2009	y
19	12/01/2009	x
20	12/01/2009	y
21	12/01/2009	x
22	12/01/2009	y
23	12/01/2009	x
24	17/01/2009	x
25	17/01/2009	x	2	39830
26	17/01/2009	y
27	17/01/2009	y
28	17/01/2009	y
29	17/01/2009	x
30	1/01/2010	x
31	1/01/2010	x	10	40179
32	15/02/2010	x
33	15/02/2010	x
34	15/02/2010	y	3	40224
35	18/02/2010	x
36	18/02/2010	x
37	18/02/2010	x
38	18/02/2010	x
39	18/02/2010	x
40	17/01/2011	x	4	40560
41	9/03/2011	x	8	40611
42	9/05/2011	x	3	40672
43	7/03/2012	x	1	40975
44	26/02/2013	x	2	41331
45	7/03/2013	x	1	41340
46	1/03/2014	x	2,1	41699
47	4/12/2014	x	3,4	41977
48	4/12/2014	y	2,4	41977
49	7/12/2014	y	1,2	41980
50	7/12/2014	y	1,5	41980,01
51	14/12/2014	x	2	41987,01
52	14/12/2014	x	1,8	41987,01
53	29/12/2014	x	2,7	42002,01
54	16/01/2015	x	2,3	42020,01
55	7/11/2015	x
56	6/02/2016	x	2,3	42406,01
57	13/05/2016	x	3,2	42503,01
58	13/05/2016	x	2,5	42503,01
59	27/06/2016	y	1,1	42548,01
60	24/09/2016	y	1,4	42637,01
61	24/09/2016	y	1	42637,01
62	24/02/2017	x	1,1	42790,01
63	3/03/2018	x	4,1	43162,01
64	5/03/2018	x	9,1	43164,01
65	5/03/2018	x	9	43164,01
66	5/03/2018	x	7,8	43164,01
67	6/03/2018	x	2,8	43165,01
68	8/03/2018	x	5,3	43167,01
69	8/03/2018	x	5,4	43167,01
70	8/03/2018	x	3,3	43167,01
71	9/03/2018	x	7,8	43168,01
72	24/02/2019	x	1,6	43520,01
73	24/02/2019	x	3,2	43520,01
74	31/03/2019	y	1,6	43555,01
75	31/03/2019	y	3,7	43555,01
76	25/01/2020	y	1,9	43855,01
77	27/01/2020	x	6,7	43857,01
78	27/01/2020	x	2,7	43857,01
79	27/01/2020	x	1,3	43857,01
80	5/02/2020	x	4,6	43866,01
81	8/03/2020	y	4,4	43898,01
82	8/03/2020	x	0,8	43898,01
83	26/09/2020	x	1,6	44100,01
84	26/09/2020	x	2,2	44100,01
85	25/12/2020	x	1,7	44190,01
86	10/03/2021	x	14	44265,01
87	23/03/2021	x	1,4	44278,01
88	24/06/2021	x	2,4	44371,01
89	29/10/2021	x	4,2	44498,01
90	21/01/2022	x	7,5	44582,01
91	24/01/2022	x	12	44585,01
92	26/01/2022	x	2,2	44587,01
Blad1

Cell Formulas
Range	Formula
F1	F1	=COUNT(C:C)
F2	F2	=LARGE(A:A,24)
G2	G2	=MATCH(F2,A:A,0)
F3	F3	=LARGE(D2:D92,24)
G3	G3	=MATCH(F3,D:D,0)
F6	F6	=AGGREGATE(16,6,$C$2:$C$92/(($D$2:$D$92>=F3)*($C$2:$C$92<>"")),0.8)
D2:D92	D2	=IF(C2<>"",A2+ROW()/10000,"")

 

[BSALV]

EDIT : my previous answer was wrong, but i can't correct or remove it anymore
as you have 365, it can be done with a LET-formula, but that's for the LET-nerds

Map1
	A	B	C	D	E	F	G	H	I
1	Date	Site	Value	date'
2	25/12/2006	x
3	21/12/2007	x	1	39437
4	21/12/2007	x	2	39437
5	18/11/2008	x
6	18/11/2008	x
7	28/11/2008	y
8	28/11/2008	y	1	39780			100000
9	28/11/2008	y	2	39780			count	large 24	perc
10	28/11/2008	y				x	47	43164,0007	7,8
11	28/11/2008	y				y	15	not enough	not enough
12	28/11/2008	y
13	28/11/2008	x
14	12/01/2009	x
15	12/01/2009	x	1	39825
16	12/01/2009	y	1	39825
17	12/01/2009	y	1	39825
18	12/01/2009	y
19	12/01/2009	x
20	12/01/2009	y
21	12/01/2009	x
22	12/01/2009	y
23	12/01/2009	x
24	17/01/2009	x
25	17/01/2009	x	2	39830
26	17/01/2009	y
27	17/01/2009	y
28	17/01/2009	y
29	17/01/2009	x
30	1/01/2010	x
31	1/01/2010	x	10	40179
32	15/02/2010	x
33	15/02/2010	x
34	15/02/2010	y	3	40224
35	18/02/2010	x
36	18/02/2010	x
37	18/02/2010	x
38	18/02/2010	x
39	18/02/2010	x
40	17/01/2011	x	4	40560
41	9/03/2011	x	8	40611
42	9/05/2011	x	3	40672
43	7/03/2012	x	1	40975
44	26/02/2013	x	2	41331
45	7/03/2013	x	1	41340
46	1/03/2014	x	2,1	41699
47	4/12/2014	x	3,4	41977
48	4/12/2014	y	2,4	41977
49	7/12/2014	y	1,2	41980
50	7/12/2014	y	1,5	41980,01
51	14/12/2014	x	2	41987,01
52	14/12/2014	x	1,8	41987,01
53	29/12/2014	x	2,7	42002,01
54	16/01/2015	x	2,3	42020,01
55	7/11/2015	x
56	6/02/2016	x	2,3	42406,01
57	13/05/2016	x	3,2	42503,01
58	13/05/2016	x	2,5	42503,01
59	27/06/2016	y	1,1	42548,01
60	24/09/2016	y	1,4	42637,01
61	24/09/2016	y	1	42637,01
62	24/02/2017	x	1,1	42790,01
63	3/03/2018	x	4,1	43162,01
64	5/03/2018	x	9,1	43164,01
65	5/03/2018	x	9	43164,01
66	5/03/2018	x	7,8	43164,01
67	6/03/2018	x	2,8	43165,01
68	8/03/2018	x	5,3	43167,01
69	8/03/2018	x	5,4	43167,01
70	8/03/2018	x	3,3	43167,01
71	9/03/2018	x	7,8	43168,01
72	24/02/2019	x	1,6	43520,01
73	24/02/2019	x	3,2	43520,01
74	31/03/2019	y	1,6	43555,01
75	31/03/2019	y	3,7	43555,01
76	25/01/2020	y	1,9	43855,01
77	27/01/2020	x	6,7	43857,01
78	27/01/2020	x	2,7	43857,01
79	27/01/2020	x	1,3	43857,01
80	5/02/2020	x	4,6	43866,01
81	8/03/2020	y	4,4	43898,01
82	8/03/2020	x	0,8	43898,01
83	26/09/2020	x	1,6	44100,01
84	26/09/2020	x	2,2	44100,01
85	25/12/2020	x	1,7	44190,01
86	10/03/2021	x	14	44265,01
87	23/03/2021	x	1,4	44278,01
88	24/06/2021	x	2,4	44371,01
89	29/10/2021	x	4,2	44498,01
90	21/01/2022	x	7,5	44582,01
91	24/01/2022	x	12	44585,01
92	26/01/2022	x	2,2	44587,01
Blad1

Cell Formulas
Range	Formula
G10:G11	G10	=SUMPRODUCT(($B$2:$B$92=F10)*($C$2:$C$92<>""))
H10:H11	H10	=IF(G10>=24,AGGREGATE(14,6,($A$2:$A$92+ROW($A$2:$A$92)/$G$8)/(($B$2:$B$92=F10)*($C$2:$C$92<>"")),24),"not enough")
I10:I11	I10	=IF(ISNUMBER(H10),AGGREGATE(16,6,$C$2:$C$92/(($D$2:$D$92>=$H10)*($C$2:$C$92<>"")*($B$2:$B$92=$F10)),0.8),"not enough")
D2:D92	D2	=IF(C2<>"",A2+ROW()/10000,"")

 

[KRice]

Here is another idea using some other Excel 365 functions. Note: in some cases, what should happen if the number of values is smaller than 24? That is the case for Site "y", so I included a provision to use the minimum number of values between 24 and the actual number. The count of the values is shown in E1:F1. The 80th percentile (inclusive) for each site is in E3:F3.

Book3
	A	B	C	D	E	F	G
1	Date	Site	Value		47	15	<--Count
2	25/12/2006	x			x	y
3	21/12/2007	x	1		7.62	2.52	<---80th percentile
4	21/12/2007	x	2
5	18/11/2008	x
6	18/11/2008	x
7	28/11/2008	y
8	28/11/2008	y	1
9	28/11/2008	y	2
10	28/11/2008	y
11	28/11/2008	y
12	28/11/2008	y
13	28/11/2008	x
14	12/01/2009	x
15	12/01/2009	x	1
16	12/01/2009	y	1
17	12/01/2009	y	1
18	12/01/2009	y
19	12/01/2009	x
20	12/01/2009	y
21	12/01/2009	x
22	12/01/2009	y
23	12/01/2009	x
24	17/01/2009	x
25	17/01/2009	x	2
26	17/01/2009	y
27	17/01/2009	y
28	17/01/2009	y
29	17/01/2009	x
30	01/01/2010	x
31	01/01/2010	x	10
32	15/02/2010	x
33	15/02/2010	x
34	15/02/2010	y	3
35	18/02/2010	x
36	18/02/2010	x
37	18/02/2010	x
38	18/02/2010	x
39	18/02/2010	x
40	17/01/2011	x	4
41	09/03/2011	x	8
42	09/05/2011	x	3
43	07/03/2012	x	1
44	26/02/2013	x	2
45	07/03/2013	x	1
46	01/03/2014	x	2.1
47	04/12/2014	x	3.4
48	04/12/2014	y	2.4
49	07/12/2014	y	1.2
50	07/12/2014	y	1.5
51	14/12/2014	x	2
52	14/12/2014	x	1.8
53	29/12/2014	x	2.7
54	16/01/2015	x	2.3
55	07/11/2015	x
56	06/02/2016	x	2.3
57	13/05/2016	x	3.2
58	13/05/2016	x	2.5
59	27/06/2016	y	1.1
60	24/09/2016	y	1.4
61	24/09/2016	y	1
62	24/02/2017	x	1.1
63	03/03/2018	x	4.1
64	05/03/2018	x	9.1
65	05/03/2018	x	9
66	05/03/2018	x	7.8
67	06/03/2018	x	2.8
68	08/03/2018	x	5.3
69	08/03/2018	x	5.4
70	08/03/2018	x	3.3
71	09/03/2018	x	7.8
72	24/02/2019	x	1.6
73	24/02/2019	x	3.2
74	31/03/2019	y	1.6
75	31/03/2019	y	3.7
76	25/01/2020	y	1.9
77	27/01/2020	x	6.7
78	27/01/2020	x	2.7
79	27/01/2020	x	1.3
80	05/02/2020	x	4.6
81	08/03/2020	y	4.4
82	08/03/2020	x	0.8
83	26/09/2020	x	1.6
84	26/09/2020	x	2.2
85	25/12/2020	x	1.7
86	10/03/2021	x	14
87	23/03/2021	x	1.4
88	24/06/2021	x	2.4
89	29/10/2021	x	4.2
90	21/01/2022	x	7.5
91	24/01/2022	x	12
92	26/01/2022	x	2.2
Sheet1

Cell Formulas
Range	Formula
E1:F1	E1	=SUM(($B$2:$B$92=E$2)*($C$2:$C$92<>""))
E3:F3	E3	=PERCENTILE.INC(INDEX(SORT(FILTER(FILTER($A$2:$C$92,($B$2:$B$92=E$2)*($C$2:$C$92<>"")),{1,0,1}),1,-1),SEQUENCE(MIN(24,SUM(($B$2:$B$92=E$2)*($C$2:$C$92<>"")))),2),0.8)

 

[Fitzy22]

EDIT : my previous answer was wrong, but i can't correct or remove it anymore
as you have 365, it can be done with a LET-formula, but that's for the LET-nerds

Map1
	A	B	C	D	E	F	G	H	I
1	Date	Site	Value	date'
2	25/12/2006	x
3	21/12/2007	x	1	39437
4	21/12/2007	x	2	39437
5	18/11/2008	x
6	18/11/2008	x
7	28/11/2008	y
8	28/11/2008	y	1	39780			100000
9	28/11/2008	y	2	39780			count	large 24	perc
10	28/11/2008	y				x	47	43164,0007	7,8
11	28/11/2008	y				y	15	not enough	not enough
12	28/11/2008	y
13	28/11/2008	x
14	12/01/2009	x
15	12/01/2009	x	1	39825
16	12/01/2009	y	1	39825
17	12/01/2009	y	1	39825
18	12/01/2009	y
19	12/01/2009	x
20	12/01/2009	y
21	12/01/2009	x
22	12/01/2009	y
23	12/01/2009	x
24	17/01/2009	x
25	17/01/2009	x	2	39830
26	17/01/2009	y
27	17/01/2009	y
28	17/01/2009	y
29	17/01/2009	x
30	1/01/2010	x
31	1/01/2010	x	10	40179
32	15/02/2010	x
33	15/02/2010	x
34	15/02/2010	y	3	40224
35	18/02/2010	x
36	18/02/2010	x
37	18/02/2010	x
38	18/02/2010	x
39	18/02/2010	x
40	17/01/2011	x	4	40560
41	9/03/2011	x	8	40611
42	9/05/2011	x	3	40672
43	7/03/2012	x	1	40975
44	26/02/2013	x	2	41331
45	7/03/2013	x	1	41340
46	1/03/2014	x	2,1	41699
47	4/12/2014	x	3,4	41977
48	4/12/2014	y	2,4	41977
49	7/12/2014	y	1,2	41980
50	7/12/2014	y	1,5	41980,01
51	14/12/2014	x	2	41987,01
52	14/12/2014	x	1,8	41987,01
53	29/12/2014	x	2,7	42002,01
54	16/01/2015	x	2,3	42020,01
55	7/11/2015	x
56	6/02/2016	x	2,3	42406,01
57	13/05/2016	x	3,2	42503,01
58	13/05/2016	x	2,5	42503,01
59	27/06/2016	y	1,1	42548,01
60	24/09/2016	y	1,4	42637,01
61	24/09/2016	y	1	42637,01
62	24/02/2017	x	1,1	42790,01
63	3/03/2018	x	4,1	43162,01
64	5/03/2018	x	9,1	43164,01
65	5/03/2018	x	9	43164,01
66	5/03/2018	x	7,8	43164,01
67	6/03/2018	x	2,8	43165,01
68	8/03/2018	x	5,3	43167,01
69	8/03/2018	x	5,4	43167,01
70	8/03/2018	x	3,3	43167,01
71	9/03/2018	x	7,8	43168,01
72	24/02/2019	x	1,6	43520,01
73	24/02/2019	x	3,2	43520,01
74	31/03/2019	y	1,6	43555,01
75	31/03/2019	y	3,7	43555,01
76	25/01/2020	y	1,9	43855,01
77	27/01/2020	x	6,7	43857,01
78	27/01/2020	x	2,7	43857,01
79	27/01/2020	x	1,3	43857,01
80	5/02/2020	x	4,6	43866,01
81	8/03/2020	y	4,4	43898,01
82	8/03/2020	x	0,8	43898,01
83	26/09/2020	x	1,6	44100,01
84	26/09/2020	x	2,2	44100,01
85	25/12/2020	x	1,7	44190,01
86	10/03/2021	x	14	44265,01
87	23/03/2021	x	1,4	44278,01
88	24/06/2021	x	2,4	44371,01
89	29/10/2021	x	4,2	44498,01
90	21/01/2022	x	7,5	44582,01
91	24/01/2022	x	12	44585,01
92	26/01/2022	x	2,2	44587,01
Blad1

Cell Formulas
Range	Formula
G10:G11	G10	=SUMPRODUCT(($B$2:$B$92=F10)*($C$2:$C$92<>""))
H10:H11	H10	=IF(G10>=24,AGGREGATE(14,6,($A$2:$A$92+ROW($A$2:$A$92)/$G$8)/(($B$2:$B$92=F10)*($C$2:$C$92<>"")),24),"not enough")
I10:I11	I10	=IF(ISNUMBER(H10),AGGREGATE(16,6,$C$2:$C$92/(($D$2:$D$92>=$H10)*($C$2:$C$92<>"")*($B$2:$B$92=$F10)),0.8),"not enough")
D2:D92	D2	=IF(C2<>"",A2+ROW()/10000,"")

A huge thanks to BSALV and KRice!!

It may well have taken me days to come up with either of those solutions .

KRice, the formula you prepared is perfect for my spreadsheet as I don't have space for helper cells for the calculation. In fact, I only have one cell for each site. Will the formula in E3 work by itself without the need to reference E1? I do have a column containing each site which will be the equivalent to E$2.

Once again, so much appreciation for your help with this and love your excel skills.

Cheers, Fitzy.

 

[Fitzy22]

A huge thanks to BSALV and KRice!!

It may well have taken me days to come up with either of those solutions .

KRice, the formula you prepared is perfect for my spreadsheet as I don't have space for helper cells for the calculation. In fact, I only have one cell for each site. Will the formula in E3 work by itself without the need to reference E1? I do have a column containing each site which will be the equivalent to E$2.

Once again, so much appreciation for your help with this and love your excel skills.

Cheers, Fitzy.

 

[Fitzy22]

Here is another idea using some other Excel 365 functions. Note: in some cases, what should happen if the number of values is smaller than 24? That is the case for Site "y", so I included a provision to use the minimum number of values between 24 and the actual number. The count of the values is shown in E1:F1. The 80th percentile (inclusive) for each site is in E3:F3.

Book3
	A	B	C	D	E	F	G
1	Date	Site	Value		47	15	<--Count
2	25/12/2006	x			x	y
3	21/12/2007	x	1		7.62	2.52	<---80th percentile
4	21/12/2007	x	2
5	18/11/2008	x
6	18/11/2008	x
7	28/11/2008	y
8	28/11/2008	y	1
9	28/11/2008	y	2
10	28/11/2008	y
11	28/11/2008	y
12	28/11/2008	y
13	28/11/2008	x
14	12/01/2009	x
15	12/01/2009	x	1
16	12/01/2009	y	1
17	12/01/2009	y	1
18	12/01/2009	y
19	12/01/2009	x
20	12/01/2009	y
21	12/01/2009	x
22	12/01/2009	y
23	12/01/2009	x
24	17/01/2009	x
25	17/01/2009	x	2
26	17/01/2009	y
27	17/01/2009	y
28	17/01/2009	y
29	17/01/2009	x
30	01/01/2010	x
31	01/01/2010	x	10
32	15/02/2010	x
33	15/02/2010	x
34	15/02/2010	y	3
35	18/02/2010	x
36	18/02/2010	x
37	18/02/2010	x
38	18/02/2010	x
39	18/02/2010	x
40	17/01/2011	x	4
41	09/03/2011	x	8
42	09/05/2011	x	3
43	07/03/2012	x	1
44	26/02/2013	x	2
45	07/03/2013	x	1
46	01/03/2014	x	2.1
47	04/12/2014	x	3.4
48	04/12/2014	y	2.4
49	07/12/2014	y	1.2
50	07/12/2014	y	1.5
51	14/12/2014	x	2
52	14/12/2014	x	1.8
53	29/12/2014	x	2.7
54	16/01/2015	x	2.3
55	07/11/2015	x
56	06/02/2016	x	2.3
57	13/05/2016	x	3.2
58	13/05/2016	x	2.5
59	27/06/2016	y	1.1
60	24/09/2016	y	1.4
61	24/09/2016	y	1
62	24/02/2017	x	1.1
63	03/03/2018	x	4.1
64	05/03/2018	x	9.1
65	05/03/2018	x	9
66	05/03/2018	x	7.8
67	06/03/2018	x	2.8
68	08/03/2018	x	5.3
69	08/03/2018	x	5.4
70	08/03/2018	x	3.3
71	09/03/2018	x	7.8
72	24/02/2019	x	1.6
73	24/02/2019	x	3.2
74	31/03/2019	y	1.6
75	31/03/2019	y	3.7
76	25/01/2020	y	1.9
77	27/01/2020	x	6.7
78	27/01/2020	x	2.7
79	27/01/2020	x	1.3
80	05/02/2020	x	4.6
81	08/03/2020	y	4.4
82	08/03/2020	x	0.8
83	26/09/2020	x	1.6
84	26/09/2020	x	2.2
85	25/12/2020	x	1.7
86	10/03/2021	x	14
87	23/03/2021	x	1.4
88	24/06/2021	x	2.4
89	29/10/2021	x	4.2
90	21/01/2022	x	7.5
91	24/01/2022	x	12
92	26/01/2022	x	2.2
Sheet1

Cell Formulas
Range	Formula
E1:F1	E1	=SUM(($B$2:$B$92=E$2)*($C$2:$C$92<>""))
E3:F3	E3	=PERCENTILE.INC(INDEX(SORT(FILTER(FILTER($A$2:$C$92,($B$2:$B$92=E$2)*($C$2:$C$92<>"")),{1,0,1}),1,-1),SEQUENCE(MIN(24,SUM(($B$2:$B$92=E$2)*($C$2:$C$92<>"")))),2),0.8)

Hi again KRice,

I have attempted to replicate this formula in my spreadsheet and I get a #VALUE! error. I think this is because the values column is separated from the date and site column by a number of intervening columns. Do you know if there are any additions or modifications to the formula that might account for that. In addition to the sites, I also have numerous values columns of different types that I need to calculate the percentiles for.

Regards, Fitzy.

 

[KRice]

Fitzy, yes...some adjustments are needed. First, the E1:F1 formula isn't necessary, but it serves to indicate how many values satisfy the match criteria (whether site x or y and value is non-blank). If the number reported back is 24 or greater, then your original expectation is met (the most recent 24 values were used). If the number reported back is less than 24, then all of them are used...and in that case the number used is what appears in E1:F1 for that column. If you don't need to see this bit of information, then no, the E1:F1 formula is not needed, and the main formula does not reference E1 or F1.

Regarding the aim to adapt this formula, it should be straight forward, but the table structure does matter. In the formula:

=PERCENTILE.INC(INDEX(SORT(FILTER(FILTER($A$2:$C$92,($B$2:$B$92=E$2)*($C$2:$C$92<>"")),{1,0,1}),1,-1),SEQUENCE(MIN(24,SUM(($B$2:$B$92=E$2)*($C$2:$C$92<>"")))),2),0.8)

...the innermost FILTER needs to be adjusted to cover the entire range of data so that "Date" and "Value" are in the range. In the current example, Date is in column A and Value in column C, so

FILTER($A$2:$C$92,($B$2:$B$92=E$2)*($C$2:$C$92<>""))

$A$2:$C$92 captures both of them and the row range 2:92 covers all of the data that is needed. The filtering criteria may need to be adjusted, where here we have the "Site is x or y" criteria represented by ($B$2:$B$92=E$2). Adjust that range reference as needed, and determine whether you want to refer to the cell containing "x" (as in E$2 here) or simply replace that with "x" (surrounded by quotes since it is a text value)...this would be considered as hardwiring "x" into the formula, and if you do that, then another formula would look just like this one except a "y" would be hardwired into it. Then the non-blank value criteria must be multiplied by x or y criteria just described, so ($C$2:$C$92<>"") needs to refer to the data in the Value column. The rows represented by all of these must be the same (notice all are 2:92...adjust that to fit your data). That completes edits to the inner FILTER which I'll refer to a "filter inner" below.

The outer FILTER then needs to be adjusted. Currently it looks like this:

FILTER(filter inner,{1,0,1})

...that array of 1's and 0's is used to consolidate the array and essentially turn on (1) or off (0) a column. Since I want Date and Value in column A and C, the array is {1,0,1}. If you have a wider array, at this point, say Date and Value are in A and E, the turn on/off column filter array would be {1,0,0,0,1}. Make this adjustment and you should have at this point a two-column array of filtered data consisting of Date and Value produced by what I'll refer to as the "filter outer" below.

Then the data need to be sorted by date, in descending order, so

SORT(filter outer,1,-1)

...accomplishes that, where the "1" refers to the column where Date is found in the "filter outer" array. Since Date appears in "filter outer's" 1st column, I've used a 1. The "-1" means to sort in descending order, so this places the most recent dates (i.e., largest dates) at the top.

Then we need to cut off this list so that no more than 24 values are considered, so this filtered and sorted array is fed into the INDEX function:

INDEX(filtered and sorted array, SEQUENCE(MIN(24,SUM(($B$2:$B$92=E$2)*($C$2:$C$92<>"")))), 2)

Here is where an array of values for the rows to use is constructed with the SEQUENCE function. If you knew that you would always have at least 24 data points that met your criteria, the function would simply be SEQUENCE(24) which constructs an array {1;2;3;...24} to tell the INDEX function to return only those row indexes in the filtered and sorted array. But it appears that in some cases you may have fewer values, so a provision is made to account for that:

MIN(24, SUM(  ($B$2:$B$92=E$2) * ($C$2:$C$92<>"") ) )

You will see your two original filtering criteria...they should be the same as what you used before. By multiplying those criteria together and then taking the SUM, we will get the count of the number of data values satisfying the criteria . We then compare that sum to 24 and use whichever is smaller as the input to the SEQUENCE function to create the array of row indexes to use in the INDEX function. Since the "filtered and sorted array" from the previous step includes the Values in the 2nd column, the INDEX function must be told to extract data from that column...that is what the "2" does in the INDEX function. This produces a single column of values that represent your filtering criteria, and they number 24 (if available) or some smaller count. This array of values is then passed to the PERCENTILE.INC function, and no changes should be needed with it.

Please post back with some details if you have any questions.

 

[KRice]

As @BSALV mentioned, the LET function can be used. Here it is used to reduce some redundancy and slightly shorten the formula. Within the LET function, we define a new variable named fcrit and assign to it your filter criteria array that appears twice in the formula I originally presented. Now it is defined once and then referred to twice with its shorter name.

=LET(fcrit,($B$2:$B$92=E$2)*($C$2:$C$92<>""), PERCENTILE.INC(INDEX(SORT(FILTER(FILTER($A$2:$C$92,fcrit),{1,0,1}),1,-1),SEQUENCE(MIN(24,SUM(fcrit))),2),0.8))

 

[Fitzy22]

Thanks for the explanation KRice, it clarifies a lot regarding the workings of the formula and the changes have resulted in a working formula!! In excel, the thing I find most difficult is the order in which the various functions are written, it's definitely an area I need to understand more. I will also dive into the LET function at some stage and give that formula a test run.

Appreciate the help @BSALV and @KRice.

Cheers,