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

 

[KRice]

That’s good news, and we’re happy to help.

 

[Fitzy22]

Hi again @KRice.

I am having issues with some of the calculations still and I'm not sure why.

The formula I'm using is:

=PERCENTILE.INC(INDEX(SORT(FILTER(FILTER(A9:BD273,($D$9:$D$273=F2)*($K$9:$K$273<>"")),{1,0,0,0,0,0,0,0,0,0,1}),1,-1),SEQUENCE(MIN(24,SUM(($D$9:$D$273=F2)*(K9:K273<>"")))),11),0.8)

In this case the spreadsheet columns are:

- Entire data array - A9:BD273
- Dates - A9:A273
- Site - D9:D273
- Values - K9:K273
- Site reference - F2

There are 9 intervening columns between the Date (Column A - Column 1) and the Values (Column K - Column 11) which are represented by the 9 zeros in the formula.

Apologies if this is a hassle. I cannot understand why its not working because it works on one spreadsheet and not another.

Regards,

 

[KRice]

This part (A9:BD273) of your inner FILTER says to return information from columns A:BD...that's a lot. Yet your outer FILTER says to turn on/off this many columns {1,0,0,0,0,0,0,0,0,0,1}, which means to turn on, or keep, columns A and K. So change the first reference to A9:K273. So the outer FILTER isn't receiving enough information to know what to do with columns L:BD.

 

[KRice]

If your table structure is such that the Date, Site, and Value columns are far apart and constructing the outer FILTER column "on/off" array is tedious (lots of 0's), there is another approach. Rather than specifying a very large contiguous range in the inner FILTER, you could feed that large contiguous range into an INDEX function and have it return only the three columns that you need. Here, I've separated Date (A) and Site (C) and Value (G) so that they are no longer contiguous, and suppose I do not want to write the awkward {1,0,0,0,0,0,1} outer filter column on/off array. Instead, I could use this:

INDEX($A$2:$G$92,SEQUENCE(COUNT($A$2:$A$92)),{1,3,7})

...in the inner FILTER to consolidate columns 1,3, and 7 (A,C,G) into a range that FILTER operates on just like before.

MrExcel_20220629b.xlsx
	A	B	C	D	E	F	G	H	I
1	Date		Site				Value		47
2	25/12/2006		x						x
3	21/12/2007		x				1		7.62
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
Sheet1 (2)

Cell Formulas
Range	Formula
I1	I1	=SUM(($C$2:$C$92=I$2)*($G$2:$G$92<>""))
I3	I3	=PERCENTILE.INC(INDEX(SORT(FILTER(FILTER(INDEX($A$2:$G$92,SEQUENCE(COUNT($A$2:$A$92)),{1,3,7}), ($C$2:$C$92=I$2)*($G$2:$G$92<>"") ),{1,0,1}),1,-1),SEQUENCE(MIN(24,SUM( ($C$2:$C$92=I$2)*($G$2:$G$92<>"") ))),2),0.8)

 

[Fitzy22]

Thanks @KRice,

It's working perfectly now. I have set the formula up in such a way that I can fill right and just change the number of the Values column. I think the biggest hassle will be once I add additional data to the bottom of the spreadsheet and I have to extend the selection of the various columns.

 

[KRice]

You have a couple of options to deal with extending the ranges. You could convert your data table into an official Excel table and use structured references. By doing so, Excel will automatically account for data added to the end of the table. The other option is to make the range overly large right now...perhaps you anticipate this doubling in size, so instead of rows 2:92, you could make it 2:1000 to accommodate anticipated growth and more. The FILTER functions will still work fine, even with no data present...although if you do this and you are using the INDEX variation I mentioned in post #14, change the COUNT function to ROWS($A$2:$A$1000), as the COUNT function will not consider the spare blank cells, whereas the ROWS function will simply count the rows.

 

[Fitzy22]

Thanks @KRice, I'll look into both of those options. I may start out initially with making the range larger and then convert to the excel table option.

 

[KRice]

Post back if you run into any difficulties. Here is an example of an expanded range using non-contiguous data columns (therefore the INDEX variation is used), and the ROWS functions is used instead of the COUNT function:

=PERCENTILE.INC(INDEX(SORT(FILTER(FILTER(INDEX($A$2:$G$1000,SEQUENCE(ROWS($A$2:$A$1000)),{1,3,7}), ($C$2:$C$1000=I$2)*($G$2:$G$1000<>"") ),{1,0,1}),1,-1),SEQUENCE(MIN(24,SUM( ($C$2:$C$1000=I$2)*($G$2:$G$1000<>"") ))),2),0.8)

...or slightly shortening it with the LET function:

=LET(fcrit,($C$2:$C$1000=I$2)*($G$2:$G$1000<>""),PERCENTILE.INC(INDEX(SORT(FILTER(FILTER(INDEX($A$2:$G$1000,SEQUENCE(ROWS($A$2:$A$1000)),{1,3,7}),fcrit),{1,0,1}),1,-1),SEQUENCE(MIN(24,SUM(fcrit))),2),0.8))

 

[Fitzy22]

Brilliant, you've been such a great help with this!! Thanks for your patience.

 

[KRice]

You're welcome...I'm happy to help.