Get bottom average score based on month

[Firasath]

Hi,

I'm trying to get average score based on starting cell of Quartile-1 value and the last cell of the range. I used the same formula without applying filter on month and it worked file but when I want to retrieve the results based on a month, the formula with the filter function not working. Please find below the formula and advise where I am making mistake. The formula I am using is as follows:

=LET(m,NV!$A:$A,s,NV!$AH:$AH,f,FILTER(NV!$AH:$AH,(m>=I47)*(m<=EOMONTH(I47,0))),AVERAGE(INDIRECT((ADDRESS(MATCH(QUARTILE.INC(f,1),s,0),MATCH("Overall Score",NV!1:1,0),1))):INDIRECT(ADDRESS(MAX(FILTER(f,ROW(s))),MAX(FILTER(f,COLUMN(s))),1))))

EGS BQ.xlsb
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
47	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	######	Jun-23	Jul-23	Aug-23	Sep-23
48	=LET(m,NV!$A:$A,s,NV!$AH:$AH,f,FILTER(NV!$AH:$AH,(m>=I47)*(m<=EOMONTH(I47,0))),AVERAGE(INDIRECT((ADDRESS(MATCH(QUARTILE.INC(f,1),s,0),MATCH("Overall Score",NV!1:1,0),1))):INDIRECT(ADDRESS(MAX(FILTER(f,ROW(s))),MAX(FILTER(f,COLUMN(s))),1))))
BQ_Summary

Cell Formulas
Range	Formula
AM47:AO47	AM47	=IF(AM44="","",AM44)

 

[frabulator]

A possible solution is to replace the INDIRECT function with INDEX function to create a dynamic range based on the results.

You can modify your formula as follows:

=LET(m,NV!$A:$A,s,NV!$AH:$AH,f,FILTER(NV!$AH:$AH,(m>=I47)*(m<=EOMONTH(I47,0))),startcol,MATCH(QUARTILE.INC(f,1),s,0),endcol,MAX(FILTER(f,COLUMN(s))),AVERAGE(INDEX(NV!$1:$1048576, MATCH("Overall Score",NV!$1:$1,0), startcol):INDEX(NV!$1:$1048576, MAX(FILTER(f,ROW(s))), endcol)))

The formula uses the INDEX function to dynamically create a range based on the filtered results.

The formula assumes that the data is in columns A to AH and that the score values are in the column AH.

-----

Without seeing the data and filter conditions, it's difficult to determine the exact issue. However, one possibility is that the FILTER function is not returning the correct range of cells for the calculation of the average score.

You can this by trying to use the VLOOKUP function instead of the INDIRECT function to retrieve the the last cell of the range. The VLOOKUP function can help you find the correct cells even when the data is filtered.

Here's an example

=LET(m,NV!$A:$A,s,NV!$AH:$AH,f,FILTER(NV!$AH:$AH,(m>=I47)*(m<=EOMONTH(I47,0))),q1,VLOOKUP(QUARTILE.INC(f,1),s,1,FALSE),ls,VLOOKUP("Overall Score",NV!1:1,MATCH("Overall Score",NV!1:1,0),FALSE),AVERAGE(INDIRECT((ADDRESS(MATCH(q1,s,0),MATCH(ls,NV!1:1,0),1))):INDIRECT(ADDRESS(MAX(FILTER(f,ROW(s))),MAX(FILTER(f,COLUMN(s))),1))))

In this formula, you use VLOOKUP to find the Quartile-1 value and the last cell of the range and the column number for the "Overall Score" header.

 

[Fluff]

Can you post some sample data.

 

[Firasath]

A possible solution is to replace the INDIRECT function with INDEX function to create a dynamic range based on the results.

You can modify your formula as follows:

=LET(m,NV!$A:$A,s,NV!$AH:$AH,f,FILTER(NV!$AH:$AH,(m>=I47)*(m<=EOMONTH(I47,0))),startcol,MATCH(QUARTILE.INC(f,1),s,0),endcol,MAX(FILTER(f,COLUMN(s))),AVERAGE(INDEX(NV!$1:$1048576, MATCH("Overall Score",NV!$1:$1,0), startcol):INDEX(NV!$1:$1048576, MAX(FILTER(f,ROW(s))), endcol)))

The formula uses the INDEX function to dynamically create a range based on the filtered results.

The formula assumes that the data is in columns A to AH and that the score values are in the column AH.

-----

Without seeing the data and filter conditions, it's difficult to determine the exact issue. However, one possibility is that the FILTER function is not returning the correct range of cells for the calculation of the average score.

You can this by trying to use the VLOOKUP function instead of the INDIRECT function to retrieve the the last cell of the range. The VLOOKUP function can help you find the correct cells even when the data is filtered.

Here's an example

=LET(m,NV!$A:$A,s,NV!$AH:$AH,f,FILTER(NV!$AH:$AH,(m>=I47)*(m<=EOMONTH(I47,0))),q1,VLOOKUP(QUARTILE.INC(f,1),s,1,FALSE),ls,VLOOKUP("Overall Score",NV!1:1,MATCH("Overall Score",NV!1:1,0),FALSE),AVERAGE(INDIRECT((ADDRESS(MATCH(q1,s,0),MATCH(ls,NV!1:1,0),1))):INDIRECT(ADDRESS(MAX(FILTER(f,ROW(s))),MAX(FILTER(f,COLUMN(s))),1))))

In this formula, you use VLOOKUP to find the Quartile-1 value and the last cell of the range and the column number for the "Overall Score" header.

Thanks for your response.

I tried above formula but is is giving error stating something "Are you trying to type formula", i am pasting some data below with your suggested formula. Appreciate to check and update the formula accordingly.

Book6
	A	B	C	D	E	F
1	Month	Overall Score		Jul-22	Aug-22	Feb-23
2	Jul-22	76.56		=LET(m,$A:$A,s,$B:$B,f,FILTER($B:$B,(m>=D$1)*(m<=EOMONTH(D$1,0))),q1,VLOOKUP(QUARTILE.INC(f,1),s,1,FALSE), ls,VLOOKUP("Overall Score",A$1:B$1,MATCH("Overall Score",$A$1:$B$1,0),FALSE), AVERAGE(INDIRECT((ADDRESS(MATCH(q1,s,0),MATCH(ls,A$1:B$1,0),1))):INDIRECT(ADDRESS(MAX(FILTER(f,ROW(s))),MAX(FILTER(f,COLUMN(s))),1))))
3	Jul-22	75.00
4	Jul-22	75.00
5	Jul-22	75.00
6	Jul-22	75.00
7	Jul-22	68.75
8	Jul-22	68.75
9	Jul-22	68.75
10	Jul-22	68.75
11	Jul-22	68.75
12	Jul-22	68.75
13	Jul-22	64.84
14	Jul-22	62.50
15	Jul-22	60.94
16	Jul-22	60.94
17	Jul-22	60.94
18	Jul-22	59.90
19	Jul-22	57.03
20	Jul-22	53.13
21	Jul-22	53.13
22	Jul-22	53.13
23	Jul-22	53.13
24	Jul-22	53.13
25	Jul-22	52.52
26	Jul-22	49.22
27	Jul-22	49.22
28	Jul-22	49.22
29	Jul-22	49.22
30	Jul-22	48.61
31	Jul-22	45.31
32	Jul-22	45.31
33	Jul-22	45.31
34	Jul-22	45.31
35	Jul-22	44.70
36	Jul-22	41.41
37	Jul-22	41.41
38	Jul-22	41.41
39	Jul-22	41.41
40	Jul-22	41.41
41	Jul-22	41.41
42	Jul-22	41.41
43	Jul-22	41.41
44	Jul-22	39.38
45	Jul-22	37.76
46	Jul-22	37.50
47	Jul-22	37.50
48	Jul-22	37.50
49	Jul-22	37.50
50	Jul-22	37.50
51	Jul-22	37.50
52	Jul-22	37.50
53	Jul-22	37.50
54	Jul-22	37.50
55	Jul-22	37.50
56	Jul-22	37.50
57	Jul-22	37.50
58	Jul-22	37.50
59	Jul-22	37.50
60	Jul-22	37.50
61	Jul-22	37.50
62	Jul-22	37.50
63	Jul-22	37.50
64	Jul-22	37.50
65	Jul-22	37.50
66	Jul-22	37.50
67	Jul-22	37.50
68	Jul-22	37.50
69	Jul-22	37.50
70	Jul-22	37.50
71	Jul-22	36.89
72	Jul-22	31.25
73	Jul-22	31.25
74	Jul-22	31.25
75	Jul-22	30.28
76	Jul-22	30.00
77	Jul-22	28.65
78	Jul-22	25.17
79	Jul-22	23.44
80	Jul-22	21.27
81	Jul-22	21.27
82	Jul-22	21.27
83	Jul-22	20.66
84	Jul-22	19.37
85	Jul-22	0.00
86	Jul-22	0.00
87	Jul-22	0.00
88	Jul-22	0.00
89	Aug-22	82.03
90	Aug-22	68.75
91	Aug-22	68.75
92	Aug-22	68.75
93	Aug-22	64.84
94	Aug-22	61.53
95	Aug-22	60.94
96	Aug-22	60.94
97	Aug-22	60.94
98	Aug-22	60.63
99	Aug-22	57.03
100	Aug-22	57.03
101	Aug-22	53.72
102	Aug-22	53.13
103	Aug-22	53.13
104	Aug-22	50.00
105	Aug-22	50.00
106	Aug-22	50.00
107	Aug-22	49.22
108	Aug-22	49.22
109	Aug-22	49.22
110	Aug-22	49.22
111	Aug-22	49.22
112	Aug-22	45.31
113	Aug-22	45.31
114	Aug-22	41.88
115	Aug-22	41.41
116	Aug-22	41.41
117	Aug-22	41.41
118	Aug-22	37.50
119	Aug-22	37.50
120	Aug-22	37.50
121	Aug-22	37.50
122	Aug-22	37.50
123	Aug-22	37.50
124	Aug-22	37.50
125	Aug-22	37.50
126	Aug-22	37.50
127	Aug-22	37.50
128	Aug-22	37.50
129	Aug-22	37.50
130	Aug-22	37.50
131	Aug-22	37.50
132	Aug-22	37.50
133	Aug-22	37.50
134	Aug-22	37.50
135	Aug-22	37.50
136	Aug-22	37.50
137	Aug-22	37.50
138	Aug-22	37.50
139	Aug-22	37.50
140	Aug-22	37.50
141	Aug-22	37.50
142	Aug-22	37.50
143	Aug-22	37.50
144	Aug-22	37.50
145	Aug-22	37.50
146	Aug-22	37.50
147	Aug-22	37.50
148	Aug-22	37.50
149	Aug-22	37.50
150	Aug-22	37.50
151	Aug-22	37.50
152	Aug-22	34.79
153	Aug-22	32.86
154	Aug-22	30.28
155	Aug-22	27.34
156	Aug-22	26.68
157	Aug-22	21.88
158	Aug-22	20.00
159	Aug-22	20.00
160	Aug-22	15.02
161	Aug-22	12.25
162	Aug-22	6.51
163	Aug-22	5.03
164	Aug-22	5.03
165	Aug-22	0.00
166	Aug-22	0.00
167	Aug-22	0.00
168	Feb-23	93.75
169	Feb-23	93.75
170	Feb-23	93.75
171	Feb-23	93.75
172	Feb-23	93.75
173	Feb-23	93.75
174	Feb-23	93.75
175	Feb-23	93.75
176	Feb-23	93.75
177	Feb-23	93.75
178	Feb-23	93.75
179	Feb-23	91.04
180	Feb-23	89.84
181	Feb-23	89.84
182	Feb-23	89.84
183	Feb-23	89.11
184	Feb-23	85.94
185	Feb-23	85.94
186	Feb-23	85.94
187	Feb-23	82.03
188	Feb-23	82.03
189	Feb-23	82.03
190	Feb-23	80.36
191	Feb-23	78.13
192	Feb-23	78.13
193	Feb-23	78.13
194	Feb-23	77.52
195	Feb-23	77.52
196	Feb-23	77.52
197	Feb-23	75.94
198	Feb-23	74.22
199	Feb-23	74.22
200	Feb-23	74.22
201	Feb-23	71.88
202	Feb-23	71.43
203	Feb-23	70.31
204	Feb-23	70.31
205	Feb-23	66.41
206	Feb-23	66.41
207	Feb-23	66.41
208	Feb-23	66.41
209	Feb-23	65.80
210	Feb-23	62.50
211	Feb-23	62.50
212	Feb-23	62.50
213	Feb-23	62.50
214	Feb-23	62.50
215	Feb-23	62.50
216	Feb-23	62.50
217	Feb-23	62.50
218	Feb-23	62.50
219	Feb-23	62.50
220	Feb-23	62.50
221	Feb-23	62.50
222	Feb-23	62.50
223	Feb-23	62.50
224	Feb-23	62.50
225	Feb-23	62.50
226	Feb-23	62.50
227	Feb-23	62.50
228	Feb-23	62.50
229	Feb-23	62.50
230	Feb-23	62.50
231	Feb-23	62.50
232	Feb-23	62.50
233	Feb-23	62.50
234	Feb-23	62.50
235	Feb-23	62.50
236	Feb-23	62.50
237	Feb-23	62.50
238	Feb-23	62.50
239	Feb-23	62.50
240	Feb-23	62.50
241	Feb-23	62.50
242	Feb-23	62.50
243	Feb-23	62.50
244	Feb-23	62.50
245	Feb-23	62.50
246	Feb-23	62.50
247	Feb-23	62.50
248	Feb-23	62.50
249	Feb-23	62.50
250	Feb-23	62.50
251	Feb-23	62.50
252	Feb-23	62.50
253	Feb-23	62.50
254	Feb-23	62.50
255	Feb-23	62.50
256	Feb-23	62.50
257	Feb-23	62.50
258	Feb-23	62.20
259	Feb-23	61.02
260	Feb-23	60.12
261	Feb-23	59.79
262	Feb-23	59.79
263	Feb-23	57.98
264	Feb-23	57.86
265	Feb-23	56.25
266	Feb-23	56.25
267	Feb-23	54.38
268	Feb-23	54.38
269	Feb-23	54.38
270	Feb-23	50.00
271	Feb-23	50.00
272	Feb-23	50.00
273	Feb-23	50.00
274	Feb-23	48.44
275	Feb-23	46.27
276	Feb-23	46.27
277	Feb-23	46.27
278	Feb-23	44.53
279	Feb-23	42.79
280	Feb-23	38.89
281	Feb-23	36.12
282	Feb-23	34.42
283	Feb-23	30.03
284	Feb-23	30.03
285	Feb-23	30.00
286	Feb-23	30.00
287	Feb-23	28.91
288	Feb-23	25.00
289	Feb-23	25.00
290	Feb-23	25.00
291	Feb-23	25.00
292	Feb-23	25.00
293	Feb-23	25.00
Sheet1

 

[Firasath]

Can you post some sample data.

Hi Fluff,

Posted the sample data for your kind inputs.

 

[Fluff]

Thanks for that.
Is this what you want?

Fluff.xlsm
	A	B	C	D	E	F
1	Month	Overall Score		01/07/2022	01/08/2022	01/02/2023
2	01/07/2022	76.5625		30.286007	30.513889	41.695175
3	01/07/2022	75
4	01/07/2022	75
5	01/07/2022	75
6	01/07/2022	75
7	01/07/2022	68.75
8	01/07/2022	68.75
9	01/07/2022	68.75
10	01/07/2022	68.75
11	01/07/2022	68.75
12	01/07/2022	68.75
13	01/07/2022	64.84375
14	01/07/2022	62.5
15	01/07/2022	60.9375
16	01/07/2022	60.9375
17	01/07/2022	60.9375
18	01/07/2022	59.8952184
19	01/07/2022	57.03125
20	01/07/2022	53.125
21	01/07/2022	53.125
22	01/07/2022	53.125
23	01/07/2022	53.125
24	01/07/2022	53.125
25	01/07/2022	52.5162338
26	01/07/2022	49.21875
27	01/07/2022	49.21875
28	01/07/2022	49.21875
29	01/07/2022	49.21875
30	01/07/2022	48.6099838
31	01/07/2022	45.3125
32	01/07/2022	45.3125
33	01/07/2022	45.3125
34	01/07/2022	45.3125
35	01/07/2022	44.7037338
36	01/07/2022	41.40625
37	01/07/2022	41.40625
38	01/07/2022	41.40625
39	01/07/2022	41.40625
40	01/07/2022	41.40625
41	01/07/2022	41.40625
42	01/07/2022	41.40625
43	01/07/2022	41.40625
44	01/07/2022	39.375
45	01/07/2022	37.7582645
46	01/07/2022	37.5
47	01/07/2022	37.5
48	01/07/2022	37.5
49	01/07/2022	37.5
50	01/07/2022	37.5
51	01/07/2022	37.5
52	01/07/2022	37.5
53	01/07/2022	37.5
54	01/07/2022	37.5
55	01/07/2022	37.5
56	01/07/2022	37.5
57	01/07/2022	37.5
58	01/07/2022	37.5
59	01/07/2022	37.5
60	01/07/2022	37.5
61	01/07/2022	37.5
62	01/07/2022	37.5
63	01/07/2022	37.5
64	01/07/2022	37.5
65	01/07/2022	37.5
66	01/07/2022	37.5
67	01/07/2022	37.5
68	01/07/2022	37.5
69	01/07/2022	37.5
70	01/07/2022	37.5
71	01/07/2022	36.8912338
72	01/07/2022	31.25
73	01/07/2022	31.25
74	01/07/2022	31.25
75	01/07/2022	30.2849928
76	01/07/2022	30
77	01/07/2022	28.6452184
78	01/07/2022	25.1724838
79	01/07/2022	23.4375
80	01/07/2022	21.2662338
81	01/07/2022	21.2662338
82	01/07/2022	21.2662338
83	01/07/2022	20.6574675
84	01/07/2022	19.3747101
85	01/07/2022	0
86	01/07/2022	0
87	01/07/2022	0
88	01/07/2022	0
89	01/08/2022	82.03125
90	01/08/2022	68.75
91	01/08/2022	68.75
92	01/08/2022	68.75
93	01/08/2022	64.84375
94	01/08/2022	61.5349928
95	01/08/2022	60.9375
96	01/08/2022	60.9375
97	01/08/2022	60.9375
98	01/08/2022	60.6331169
99	01/08/2022	57.03125
100	01/08/2022	57.03125
101	01/08/2022	53.7224928
102	01/08/2022	53.125
103	01/08/2022	53.125
104	01/08/2022	50
105	01/08/2022	50
106	01/08/2022	50
107	01/08/2022	49.21875
108	01/08/2022	49.21875
109	01/08/2022	49.21875
110	01/08/2022	49.21875
111	01/08/2022	49.21875
112	01/08/2022	45.3125
113	01/08/2022	45.3125
114	01/08/2022	41.875
115	01/08/2022	41.40625
116	01/08/2022	41.40625
117	01/08/2022	41.40625
118	01/08/2022	37.5
119	01/08/2022	37.5
120	01/08/2022	37.5
121	01/08/2022	37.5
122	01/08/2022	37.5
123	01/08/2022	37.5
124	01/08/2022	37.5
125	01/08/2022	37.5
126	01/08/2022	37.5
127	01/08/2022	37.5
128	01/08/2022	37.5
129	01/08/2022	37.5
130	01/08/2022	37.5
131	01/08/2022	37.5
132	01/08/2022	37.5
133	01/08/2022	37.5
134	01/08/2022	37.5
135	01/08/2022	37.5
136	01/08/2022	37.5
137	01/08/2022	37.5
138	01/08/2022	37.5
139	01/08/2022	37.5
140	01/08/2022	37.5
141	01/08/2022	37.5
142	01/08/2022	37.5
143	01/08/2022	37.5
144	01/08/2022	37.5
145	01/08/2022	37.5
146	01/08/2022	37.5
147	01/08/2022	37.5
148	01/08/2022	37.5
149	01/08/2022	37.5
150	01/08/2022	37.5
151	01/08/2022	37.5
152	01/08/2022	34.7943723
153	01/08/2022	32.8617811
154	01/08/2022	30.2849928
155	01/08/2022	27.34375
156	01/08/2022	26.6774892
157	01/08/2022	21.875
158	01/08/2022	20
159	01/08/2022	20
160	01/08/2022	15.0224775
161	01/08/2022	12.2474747
162	01/08/2022	6.50826446
163	01/08/2022	5.03246753
164	01/08/2022	5.03246753
165	01/08/2022	0
166	01/08/2022	0
167	01/08/2022	0
168	01/02/2023	93.75
169	01/02/2023	93.75
170	01/02/2023	93.75
171	01/02/2023	93.75
172	01/02/2023	93.75
173	01/02/2023	93.75
174	01/02/2023	93.75
175	01/02/2023	93.75
176	01/02/2023	93.75
177	01/02/2023	93.75
178	01/02/2023	93.75
179	01/02/2023	91.0443723
180	01/02/2023	89.84375
181	01/02/2023	89.84375
182	01/02/2023	89.84375
183	01/02/2023	89.1117811
184	01/02/2023	85.9375
185	01/02/2023	85.9375
186	01/02/2023	85.9375
187	01/02/2023	82.03125
188	01/02/2023	82.03125
189	01/02/2023	82.03125
190	01/02/2023	80.3571429
191	01/02/2023	78.125
192	01/02/2023	78.125
193	01/02/2023	78.125
194	01/02/2023	77.5162338
195	01/02/2023	77.5162338
196	01/02/2023	77.5162338
197	01/02/2023	75.9435877
198	01/02/2023	74.21875
199	01/02/2023	74.21875
200	01/02/2023	74.21875
201	01/02/2023	71.875
202	01/02/2023	71.4285714
203	01/02/2023	70.3125
204	01/02/2023	70.3125
205	01/02/2023	66.40625
206	01/02/2023	66.40625
207	01/02/2023	66.40625
208	01/02/2023	66.40625
209	01/02/2023	65.7974838
210	01/02/2023	62.5
211	01/02/2023	62.5
212	01/02/2023	62.5
213	01/02/2023	62.5
214	01/02/2023	62.5
215	01/02/2023	62.5
216	01/02/2023	62.5
217	01/02/2023	62.5
218	01/02/2023	62.5
219	01/02/2023	62.5
220	01/02/2023	62.5
221	01/02/2023	62.5
222	01/02/2023	62.5
223	01/02/2023	62.5
224	01/02/2023	62.5
225	01/02/2023	62.5
226	01/02/2023	62.5
227	01/02/2023	62.5
228	01/02/2023	62.5
229	01/02/2023	62.5
230	01/02/2023	62.5
231	01/02/2023	62.5
232	01/02/2023	62.5
233	01/02/2023	62.5
234	01/02/2023	62.5
235	01/02/2023	62.5
236	01/02/2023	62.5
237	01/02/2023	62.5
238	01/02/2023	62.5
239	01/02/2023	62.5
240	01/02/2023	62.5
241	01/02/2023	62.5
242	01/02/2023	62.5
243	01/02/2023	62.5
244	01/02/2023	62.5
245	01/02/2023	62.5
246	01/02/2023	62.5
247	01/02/2023	62.5
248	01/02/2023	62.5
249	01/02/2023	62.5
250	01/02/2023	62.5
251	01/02/2023	62.5
252	01/02/2023	62.5
253	01/02/2023	62.5
254	01/02/2023	62.5
255	01/02/2023	62.5
256	01/02/2023	62.5
257	01/02/2023	62.5
258	01/02/2023	62.1956169
259	01/02/2023	61.0242031
260	01/02/2023	60.1229128
261	01/02/2023	59.7943723
262	01/02/2023	59.7943723
263	01/02/2023	57.9849838
264	01/02/2023	57.8617811
265	01/02/2023	56.25
266	01/02/2023	56.25
267	01/02/2023	54.3831169
268	01/02/2023	54.3831169
269	01/02/2023	54.3831169
270	01/02/2023	50
271	01/02/2023	50
272	01/02/2023	50
273	01/02/2023	50
274	01/02/2023	48.4375
275	01/02/2023	46.2662338
276	01/02/2023	46.2662338
277	01/02/2023	46.2662338
278	01/02/2023	44.53125
279	01/02/2023	42.7934991
280	01/02/2023	38.8872491
281	01/02/2023	36.1201299
282	01/02/2023	34.4155844
283	01/02/2023	30.0324675
284	01/02/2023	30.0324675
285	01/02/2023	30
286	01/02/2023	30
287	01/02/2023	28.90625
288	01/02/2023	25
289	01/02/2023	25
290	01/02/2023	25
291	01/02/2023	25
292	01/02/2023	25
293	01/02/2023	25
Lists

Cell Formulas
Range	Formula
D2:F2	D2	=LET(m,$A2:$A500,s,$B2:$B500,f,FILTER(s,(m>=D$1)*(m<=EOMONTH(D$1,0))),AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,0))))

 

[Firasath]

Thanks for that.
Is this what you want?

Fluff.xlsm
	A	B	C	D	E	F
1	Month	Overall Score		01/07/2022	01/08/2022	01/02/2023
2	01/07/2022	76.5625		30.286007	30.513889	41.695175
3	01/07/2022	75
4	01/07/2022	75
5	01/07/2022	75
6	01/07/2022	75
7	01/07/2022	68.75
8	01/07/2022	68.75
9	01/07/2022	68.75
10	01/07/2022	68.75
11	01/07/2022	68.75
12	01/07/2022	68.75
13	01/07/2022	64.84375
14	01/07/2022	62.5
15	01/07/2022	60.9375
16	01/07/2022	60.9375
17	01/07/2022	60.9375
18	01/07/2022	59.8952184
19	01/07/2022	57.03125
20	01/07/2022	53.125
21	01/07/2022	53.125
22	01/07/2022	53.125
23	01/07/2022	53.125
24	01/07/2022	53.125
25	01/07/2022	52.5162338
26	01/07/2022	49.21875
27	01/07/2022	49.21875
28	01/07/2022	49.21875
29	01/07/2022	49.21875
30	01/07/2022	48.6099838
31	01/07/2022	45.3125
32	01/07/2022	45.3125
33	01/07/2022	45.3125
34	01/07/2022	45.3125
35	01/07/2022	44.7037338
36	01/07/2022	41.40625
37	01/07/2022	41.40625
38	01/07/2022	41.40625
39	01/07/2022	41.40625
40	01/07/2022	41.40625
41	01/07/2022	41.40625
42	01/07/2022	41.40625
43	01/07/2022	41.40625
44	01/07/2022	39.375
45	01/07/2022	37.7582645
46	01/07/2022	37.5
47	01/07/2022	37.5
48	01/07/2022	37.5
49	01/07/2022	37.5
50	01/07/2022	37.5
51	01/07/2022	37.5
52	01/07/2022	37.5
53	01/07/2022	37.5
54	01/07/2022	37.5
55	01/07/2022	37.5
56	01/07/2022	37.5
57	01/07/2022	37.5
58	01/07/2022	37.5
59	01/07/2022	37.5
60	01/07/2022	37.5
61	01/07/2022	37.5
62	01/07/2022	37.5
63	01/07/2022	37.5
64	01/07/2022	37.5
65	01/07/2022	37.5
66	01/07/2022	37.5
67	01/07/2022	37.5
68	01/07/2022	37.5
69	01/07/2022	37.5
70	01/07/2022	37.5
71	01/07/2022	36.8912338
72	01/07/2022	31.25
73	01/07/2022	31.25
74	01/07/2022	31.25
75	01/07/2022	30.2849928
76	01/07/2022	30
77	01/07/2022	28.6452184
78	01/07/2022	25.1724838
79	01/07/2022	23.4375
80	01/07/2022	21.2662338
81	01/07/2022	21.2662338
82	01/07/2022	21.2662338
83	01/07/2022	20.6574675
84	01/07/2022	19.3747101
85	01/07/2022	0
86	01/07/2022	0
87	01/07/2022	0
88	01/07/2022	0
89	01/08/2022	82.03125
90	01/08/2022	68.75
91	01/08/2022	68.75
92	01/08/2022	68.75
93	01/08/2022	64.84375
94	01/08/2022	61.5349928
95	01/08/2022	60.9375
96	01/08/2022	60.9375
97	01/08/2022	60.9375
98	01/08/2022	60.6331169
99	01/08/2022	57.03125
100	01/08/2022	57.03125
101	01/08/2022	53.7224928
102	01/08/2022	53.125
103	01/08/2022	53.125
104	01/08/2022	50
105	01/08/2022	50
106	01/08/2022	50
107	01/08/2022	49.21875
108	01/08/2022	49.21875
109	01/08/2022	49.21875
110	01/08/2022	49.21875
111	01/08/2022	49.21875
112	01/08/2022	45.3125
113	01/08/2022	45.3125
114	01/08/2022	41.875
115	01/08/2022	41.40625
116	01/08/2022	41.40625
117	01/08/2022	41.40625
118	01/08/2022	37.5
119	01/08/2022	37.5
120	01/08/2022	37.5
121	01/08/2022	37.5
122	01/08/2022	37.5
123	01/08/2022	37.5
124	01/08/2022	37.5
125	01/08/2022	37.5
126	01/08/2022	37.5
127	01/08/2022	37.5
128	01/08/2022	37.5
129	01/08/2022	37.5
130	01/08/2022	37.5
131	01/08/2022	37.5
132	01/08/2022	37.5
133	01/08/2022	37.5
134	01/08/2022	37.5
135	01/08/2022	37.5
136	01/08/2022	37.5
137	01/08/2022	37.5
138	01/08/2022	37.5
139	01/08/2022	37.5
140	01/08/2022	37.5
141	01/08/2022	37.5
142	01/08/2022	37.5
143	01/08/2022	37.5
144	01/08/2022	37.5
145	01/08/2022	37.5
146	01/08/2022	37.5
147	01/08/2022	37.5
148	01/08/2022	37.5
149	01/08/2022	37.5
150	01/08/2022	37.5
151	01/08/2022	37.5
152	01/08/2022	34.7943723
153	01/08/2022	32.8617811
154	01/08/2022	30.2849928
155	01/08/2022	27.34375
156	01/08/2022	26.6774892
157	01/08/2022	21.875
158	01/08/2022	20
159	01/08/2022	20
160	01/08/2022	15.0224775
161	01/08/2022	12.2474747
162	01/08/2022	6.50826446
163	01/08/2022	5.03246753
164	01/08/2022	5.03246753
165	01/08/2022	0
166	01/08/2022	0
167	01/08/2022	0
168	01/02/2023	93.75
169	01/02/2023	93.75
170	01/02/2023	93.75
171	01/02/2023	93.75
172	01/02/2023	93.75
173	01/02/2023	93.75
174	01/02/2023	93.75
175	01/02/2023	93.75
176	01/02/2023	93.75
177	01/02/2023	93.75
178	01/02/2023	93.75
179	01/02/2023	91.0443723
180	01/02/2023	89.84375
181	01/02/2023	89.84375
182	01/02/2023	89.84375
183	01/02/2023	89.1117811
184	01/02/2023	85.9375
185	01/02/2023	85.9375
186	01/02/2023	85.9375
187	01/02/2023	82.03125
188	01/02/2023	82.03125
189	01/02/2023	82.03125
190	01/02/2023	80.3571429
191	01/02/2023	78.125
192	01/02/2023	78.125
193	01/02/2023	78.125
194	01/02/2023	77.5162338
195	01/02/2023	77.5162338
196	01/02/2023	77.5162338
197	01/02/2023	75.9435877
198	01/02/2023	74.21875
199	01/02/2023	74.21875
200	01/02/2023	74.21875
201	01/02/2023	71.875
202	01/02/2023	71.4285714
203	01/02/2023	70.3125
204	01/02/2023	70.3125
205	01/02/2023	66.40625
206	01/02/2023	66.40625
207	01/02/2023	66.40625
208	01/02/2023	66.40625
209	01/02/2023	65.7974838
210	01/02/2023	62.5
211	01/02/2023	62.5
212	01/02/2023	62.5
213	01/02/2023	62.5
214	01/02/2023	62.5
215	01/02/2023	62.5
216	01/02/2023	62.5
217	01/02/2023	62.5
218	01/02/2023	62.5
219	01/02/2023	62.5
220	01/02/2023	62.5
221	01/02/2023	62.5
222	01/02/2023	62.5
223	01/02/2023	62.5
224	01/02/2023	62.5
225	01/02/2023	62.5
226	01/02/2023	62.5
227	01/02/2023	62.5
228	01/02/2023	62.5
229	01/02/2023	62.5
230	01/02/2023	62.5
231	01/02/2023	62.5
232	01/02/2023	62.5
233	01/02/2023	62.5
234	01/02/2023	62.5
235	01/02/2023	62.5
236	01/02/2023	62.5
237	01/02/2023	62.5
238	01/02/2023	62.5
239	01/02/2023	62.5
240	01/02/2023	62.5
241	01/02/2023	62.5
242	01/02/2023	62.5
243	01/02/2023	62.5
244	01/02/2023	62.5
245	01/02/2023	62.5
246	01/02/2023	62.5
247	01/02/2023	62.5
248	01/02/2023	62.5
249	01/02/2023	62.5
250	01/02/2023	62.5
251	01/02/2023	62.5
252	01/02/2023	62.5
253	01/02/2023	62.5
254	01/02/2023	62.5
255	01/02/2023	62.5
256	01/02/2023	62.5
257	01/02/2023	62.5
258	01/02/2023	62.1956169
259	01/02/2023	61.0242031
260	01/02/2023	60.1229128
261	01/02/2023	59.7943723
262	01/02/2023	59.7943723
263	01/02/2023	57.9849838
264	01/02/2023	57.8617811
265	01/02/2023	56.25
266	01/02/2023	56.25
267	01/02/2023	54.3831169
268	01/02/2023	54.3831169
269	01/02/2023	54.3831169
270	01/02/2023	50
271	01/02/2023	50
272	01/02/2023	50
273	01/02/2023	50
274	01/02/2023	48.4375
275	01/02/2023	46.2662338
276	01/02/2023	46.2662338
277	01/02/2023	46.2662338
278	01/02/2023	44.53125
279	01/02/2023	42.7934991
280	01/02/2023	38.8872491
281	01/02/2023	36.1201299
282	01/02/2023	34.4155844
283	01/02/2023	30.0324675
284	01/02/2023	30.0324675
285	01/02/2023	30
286	01/02/2023	30
287	01/02/2023	28.90625
288	01/02/2023	25
289	01/02/2023	25
290	01/02/2023	25
291	01/02/2023	25
292	01/02/2023	25
293	01/02/2023	25
Lists

Cell Formulas
Range	Formula
D2:F2	D2	=LET(m,$A2:$A500,s,$B2:$B500,f,FILTER(s,(m>=D$1)*(m<=EOMONTH(D$1,0))),AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,0))))

Thanks Fluff. I amended the formula as I was getting #N/A error for few months. With the below formula, I'm getting correct results from Sep-20 onwards but for the month of Feb-23 the result isn't matching. Couldn't figure out the reason for the same. Please support.

Book9
	A	B	C	D	E	F	G
1	Month	Overall Score		Feb-23
2	Feb-23	93.75		41.70	** Score should be 42.24
3	Feb-23	93.75
4	Feb-23	93.75		Quartile-1 resut:	59.79437
5	Feb-23	93.75		Starting Cell Reference	B95
6	Feb-23	93.75		Avg. Req. for 33 rows	42.24
7	Feb-23	93.75		Avg. Req. for 32 rows	41.70
8	Feb-23	93.75
9	Feb-23	93.75
10	Feb-23	93.75
11	Feb-23	93.75
12	Feb-23	93.75
13	Feb-23	91.04437229
14	Feb-23	89.84375
15	Feb-23	89.84375
16	Feb-23	89.84375
17	Feb-23	89.11178108
18	Feb-23	85.9375
19	Feb-23	85.9375
20	Feb-23	85.9375
21	Feb-23	82.03125
22	Feb-23	82.03125
23	Feb-23	82.03125
24	Feb-23	80.35714286
25	Feb-23	78.125
26	Feb-23	78.125
27	Feb-23	78.125
28	Feb-23	77.51623377
29	Feb-23	77.51623377
30	Feb-23	77.51623377
31	Feb-23	75.94358766
32	Feb-23	74.21875
33	Feb-23	74.21875
34	Feb-23	74.21875
35	Feb-23	71.875
36	Feb-23	71.42857143
37	Feb-23	70.3125
38	Feb-23	70.3125
39	Feb-23	66.40625
40	Feb-23	66.40625
41	Feb-23	66.40625
42	Feb-23	66.40625
43	Feb-23	65.79748377
44	Feb-23	62.5
45	Feb-23	62.5
46	Feb-23	62.5
47	Feb-23	62.5
48	Feb-23	62.5
49	Feb-23	62.5
50	Feb-23	62.5
51	Feb-23	62.5
52	Feb-23	62.5
53	Feb-23	62.5
54	Feb-23	62.5
55	Feb-23	62.5
56	Feb-23	62.5
57	Feb-23	62.5
58	Feb-23	62.5
59	Feb-23	62.5
60	Feb-23	62.5
61	Feb-23	62.5
62	Feb-23	62.5
63	Feb-23	62.5
64	Feb-23	62.5
65	Feb-23	62.5
66	Feb-23	62.5
67	Feb-23	62.5
68	Feb-23	62.5
69	Feb-23	62.5
70	Feb-23	62.5
71	Feb-23	62.5
72	Feb-23	62.5
73	Feb-23	62.5
74	Feb-23	62.5
75	Feb-23	62.5
76	Feb-23	62.5
77	Feb-23	62.5
78	Feb-23	62.5
79	Feb-23	62.5
80	Feb-23	62.5
81	Feb-23	62.5
82	Feb-23	62.5
83	Feb-23	62.5
84	Feb-23	62.5
85	Feb-23	62.5
86	Feb-23	62.5
87	Feb-23	62.5
88	Feb-23	62.5
89	Feb-23	62.5
90	Feb-23	62.5
91	Feb-23	62.5
92	Feb-23	62.19561688
93	Feb-23	61.02420307
94	Feb-23	60.1229128
95	Feb-23	59.79437229
96	Feb-23	59.79437229
97	Feb-23	57.98498377
98	Feb-23	57.86178108
99	Feb-23	56.25
100	Feb-23	56.25
101	Feb-23	54.38311688
102	Feb-23	54.38311688
103	Feb-23	54.38311688
104	Feb-23	50
105	Feb-23	50
106	Feb-23	50
107	Feb-23	50
108	Feb-23	48.4375
109	Feb-23	46.26623377
110	Feb-23	46.26623377
111	Feb-23	46.26623377
112	Feb-23	44.53125
113	Feb-23	42.79349911
114	Feb-23	38.88724911
115	Feb-23	36.12012987
116	Feb-23	34.41558442
117	Feb-23	30.03246753
118	Feb-23	30.03246753
119	Feb-23	30
120	Feb-23	30
121	Feb-23	28.90625
122	Feb-23	25
123	Feb-23	25
124	Feb-23	25
125	Feb-23	25
126	Feb-23	25
127	Feb-23	25
Sheet1

Cell Formulas
Range	Formula
D2	D2	=LET(m,A:A,s,B:B,f,FILTER(s,(m>=D1)*(m<=EOMONTH(D1,0))),IFNA(AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,0)-1)),AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,-1)))))
E4	E4	=QUARTILE.INC(B2:B127,1)
E6:E7	E6	=AVERAGE(B95:B127)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$B$127	D2

 

[Firasath]

Thanks Fluff. I amended the formula as I was getting #N/A error for few months. With the below formula, I'm getting correct results from Sep-20 onwards but for the month of Feb-23 the result isn't matching. Couldn't figure out the reason for the same. Please support.

Book9
	A	B	C	D	E	F	G
1	Month	Overall Score		Feb-23
2	Feb-23	93.75		41.70	** Score should be 42.24
3	Feb-23	93.75
4	Feb-23	93.75		Quartile-1 resut:	59.79437
5	Feb-23	93.75		Starting Cell Reference	B95
6	Feb-23	93.75		Avg. Req. for 33 rows	42.24
7	Feb-23	93.75		Avg. Req. for 32 rows	41.70
8	Feb-23	93.75
9	Feb-23	93.75
10	Feb-23	93.75
11	Feb-23	93.75
12	Feb-23	93.75
13	Feb-23	91.04437229
14	Feb-23	89.84375
15	Feb-23	89.84375
16	Feb-23	89.84375
17	Feb-23	89.11178108
18	Feb-23	85.9375
19	Feb-23	85.9375
20	Feb-23	85.9375
21	Feb-23	82.03125
22	Feb-23	82.03125
23	Feb-23	82.03125
24	Feb-23	80.35714286
25	Feb-23	78.125
26	Feb-23	78.125
27	Feb-23	78.125
28	Feb-23	77.51623377
29	Feb-23	77.51623377
30	Feb-23	77.51623377
31	Feb-23	75.94358766
32	Feb-23	74.21875
33	Feb-23	74.21875
34	Feb-23	74.21875
35	Feb-23	71.875
36	Feb-23	71.42857143
37	Feb-23	70.3125
38	Feb-23	70.3125
39	Feb-23	66.40625
40	Feb-23	66.40625
41	Feb-23	66.40625
42	Feb-23	66.40625
43	Feb-23	65.79748377
44	Feb-23	62.5
45	Feb-23	62.5
46	Feb-23	62.5
47	Feb-23	62.5
48	Feb-23	62.5
49	Feb-23	62.5
50	Feb-23	62.5
51	Feb-23	62.5
52	Feb-23	62.5
53	Feb-23	62.5
54	Feb-23	62.5
55	Feb-23	62.5
56	Feb-23	62.5
57	Feb-23	62.5
58	Feb-23	62.5
59	Feb-23	62.5
60	Feb-23	62.5
61	Feb-23	62.5
62	Feb-23	62.5
63	Feb-23	62.5
64	Feb-23	62.5
65	Feb-23	62.5
66	Feb-23	62.5
67	Feb-23	62.5
68	Feb-23	62.5
69	Feb-23	62.5
70	Feb-23	62.5
71	Feb-23	62.5
72	Feb-23	62.5
73	Feb-23	62.5
74	Feb-23	62.5
75	Feb-23	62.5
76	Feb-23	62.5
77	Feb-23	62.5
78	Feb-23	62.5
79	Feb-23	62.5
80	Feb-23	62.5
81	Feb-23	62.5
82	Feb-23	62.5
83	Feb-23	62.5
84	Feb-23	62.5
85	Feb-23	62.5
86	Feb-23	62.5
87	Feb-23	62.5
88	Feb-23	62.5
89	Feb-23	62.5
90	Feb-23	62.5
91	Feb-23	62.5
92	Feb-23	62.19561688
93	Feb-23	61.02420307
94	Feb-23	60.1229128
95	Feb-23	59.79437229
96	Feb-23	59.79437229
97	Feb-23	57.98498377
98	Feb-23	57.86178108
99	Feb-23	56.25
100	Feb-23	56.25
101	Feb-23	54.38311688
102	Feb-23	54.38311688
103	Feb-23	54.38311688
104	Feb-23	50
105	Feb-23	50
106	Feb-23	50
107	Feb-23	50
108	Feb-23	48.4375
109	Feb-23	46.26623377
110	Feb-23	46.26623377
111	Feb-23	46.26623377
112	Feb-23	44.53125
113	Feb-23	42.79349911
114	Feb-23	38.88724911
115	Feb-23	36.12012987
116	Feb-23	34.41558442
117	Feb-23	30.03246753
118	Feb-23	30.03246753
119	Feb-23	30
120	Feb-23	30
121	Feb-23	28.90625
122	Feb-23	25
123	Feb-23	25
124	Feb-23	25
125	Feb-23	25
126	Feb-23	25
127	Feb-23	25
Sheet1

Cell Formulas
Range	Formula
D2	D2	=LET(m,A:A,s,B:B,f,FILTER(s,(m>=D1)*(m<=EOMONTH(D1,0))),IFNA(AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,0)-1)),AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,-1)))))
E4	E4	=QUARTILE.INC(B2:B127,1)
E6:E7	E6	=AVERAGE(B95:B127)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$B$127	D2

I used the below formula in my database, in which I got the incorret result for Feb-23 but for all other months got right results.

=LET(m,NV[Month],s,NV[Overall Score],f,FILTER(s,(m>=R30)*(m<=EOMONTH(R30,0))),IFNA(AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,0)-1)),AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,-1)))))

 

[Fluff]

When I copy the data you posted I get 42.24. Check that you don't have any further rows for Feb 23

 

[Firasath]

When I copy the data you posted I get 42.24. Check that you don't have any further rows for Feb 23

No, I don't and I am getting 41.7 in both in my database and in fili I shared with you.