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)

 

[Fluff]

I get the correct answer

Fluff.xlsm
	A	B	C	D	E
1	Month	Overall Score		01/02/2023
2	01/02/2023	93.75		42.24	** Score should be 42.24
3	01/02/2023	93.75
4	01/02/2023	93.75		Quartile-1 resut:	59.79
5	01/02/2023	93.75		Starting Cell Reference	B95
6	01/02/2023	93.75		Avg. Req. for 33 rows	42.24
7	01/02/2023	93.75		Avg. Req. for 32 rows	41.70
8	01/02/2023	93.75
9	01/02/2023	93.75
10	01/02/2023	93.75
11	01/02/2023	93.75
12	01/02/2023	93.75
13	01/02/2023	91.0443723
Lists

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)

What happens if you remove the IFNA part?

 

[Firasath]

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

Can we correct this formula, I think it will serve the purpose as it is first finding the first record based on the value of Quartile-1 and last row in the range and taking average. Please advise.

=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))))

 

[Fluff]

I am not going to try & understand that monstrosity of a formula.
Can you please answer my question?

 

[Firasath]

I am not going to try & understand that monstrosity of a formula.
Can you please answer my question?

If I remove IFNA, I am getting #N/A message.

 

[Firasath]

I am not going to try & understand that monstrosity of a formula.
Can you please answer my question?

Like in below sample data. For Jul and Aug 2022 getting results but for Feb and Mar getting #N/A message.

Book6
	A	B	C	D	E	F	G
1	Month	Overall Score		Jul-22	Aug-22	Feb-23	Mar-23
2	Jul-22	76.56		30.45	30.65	#N/A	#N/A
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
294	Mar-23	20.00
295	Mar-23	10.00
Sheet1

Cell Formulas
Range	Formula
D2:G2	D2	=LET(m,$A$2:$A$500,s,$B$2:$B$500,f,FILTER(s,(m>=D$1)*(m<=EOMONTH(D$1,0))),AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,0)-1)))

 

[Fluff]

Try it like

=LET(m,$A$2:$A$500,s,$B$2:$B$500,f,FILTER(s,(m>=D$1)*(m<=EOMONTH(D$1,0))),AVERAGE(DROP(f,XMATCH(QUARTILE.INC(f,1),f,-1)-1)))

 

[Firasath]

Like in below sample data. For Jul and Aug 2022 getting results but for Feb and Mar getting #N/A message.

Book6
	A	B	C	D	E	F	G
1	Month	Overall Score		Jul-22	Aug-22	Feb-23	Mar-23
2	Jul-22	76.56		30.45	30.65	#N/A	#N/A
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
294	Mar-23	20.00
295	Mar-23	10.00
Sheet1

Cell Formulas
Range	Formula
D2:G2	D2	=LET(m,$A$2:$A$500,s,$B$2:$B$500,f,FILTER(s,(m>=D$1)*(m<=EOMONTH(D$1,0))),AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,0)-1)))

Try it like

=LET(m,$A$2:$A$500,s,$B$2:$B$500,f,FILTER(s,(m>=D$1)*(m<=EOMONTH(D$1,0))),AVERAGE(DROP(f,XMATCH(QUARTILE.INC(f,1),f,-1)-1)))

Getting same 41.7 instead of 42.24.

 

[Fluff]

In that case I suspect that it maybe down to floating point precision, are those values the result of formulae?

 

[Firasath]

In that case I suspect that it maybe down to floating point precision, are those values the result of formulae?

Yeah, same formula as you suggested. You know, getting all right results since Sep-20, only for Feb-23 month getting variance.

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

 

[Fluff]

Yeah, same formula as you suggested

I meant the values in column B