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

 

[Fitzy22]

Yes, I will do that @KRice. Thanks. I'm interested in how excel does this. I'll let you know the result either way.