Get bottom average score based on month

Firasath

Board Regular
Joined
Feb 20, 2011
Messages
134
Office Version
  1. 365
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
IJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO
47Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21Jan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22Jan-23Feb-23Mar-23Apr-23######Jun-23Jul-23Aug-23Sep-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
RangeFormula
AM47:AO47AM47=IF(AM44="","",AM44)
 
I get the correct answer
Fluff.xlsm
ABCDE
1MonthOverall Score01/02/2023
201/02/202393.7542.24** Score should be 42.24
301/02/202393.75
401/02/202393.75Quartile-1 resut:59.79
501/02/202393.75Starting Cell ReferenceB95
601/02/202393.75Avg. Req. for 33 rows42.24
701/02/202393.75Avg. Req. for 32 rows41.70
801/02/202393.75
901/02/202393.75
1001/02/202393.75
1101/02/202393.75
1201/02/202393.75
1301/02/202391.0443723
Lists
Cell Formulas
RangeFormula
D2D2=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)))))
E4E4=QUARTILE.INC(B2:B127,1)
E6:E7E6=AVERAGE(B95:B127)


What happens if you remove the IFNA part?
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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))))
 
Upvote 0
I am not going to try & understand that monstrosity of a formula.
Can you please answer my question?
 
Upvote 0
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
ABCDEFG
1MonthOverall ScoreJul-22Aug-22Feb-23Mar-23
2Jul-2276.5630.4530.65#N/A#N/A
3Jul-2275.00
4Jul-2275.00
5Jul-2275.00
6Jul-2275.00
7Jul-2268.75
8Jul-2268.75
9Jul-2268.75
10Jul-2268.75
11Jul-2268.75
12Jul-2268.75
13Jul-2264.84
14Jul-2262.50
15Jul-2260.94
16Jul-2260.94
17Jul-2260.94
18Jul-2259.90
19Jul-2257.03
20Jul-2253.13
21Jul-2253.13
22Jul-2253.13
23Jul-2253.13
24Jul-2253.13
25Jul-2252.52
26Jul-2249.22
27Jul-2249.22
28Jul-2249.22
29Jul-2249.22
30Jul-2248.61
31Jul-2245.31
32Jul-2245.31
33Jul-2245.31
34Jul-2245.31
35Jul-2244.70
36Jul-2241.41
37Jul-2241.41
38Jul-2241.41
39Jul-2241.41
40Jul-2241.41
41Jul-2241.41
42Jul-2241.41
43Jul-2241.41
44Jul-2239.38
45Jul-2237.76
46Jul-2237.50
47Jul-2237.50
48Jul-2237.50
49Jul-2237.50
50Jul-2237.50
51Jul-2237.50
52Jul-2237.50
53Jul-2237.50
54Jul-2237.50
55Jul-2237.50
56Jul-2237.50
57Jul-2237.50
58Jul-2237.50
59Jul-2237.50
60Jul-2237.50
61Jul-2237.50
62Jul-2237.50
63Jul-2237.50
64Jul-2237.50
65Jul-2237.50
66Jul-2237.50
67Jul-2237.50
68Jul-2237.50
69Jul-2237.50
70Jul-2237.50
71Jul-2236.89
72Jul-2231.25
73Jul-2231.25
74Jul-2231.25
75Jul-2230.28
76Jul-2230.00
77Jul-2228.65
78Jul-2225.17
79Jul-2223.44
80Jul-2221.27
81Jul-2221.27
82Jul-2221.27
83Jul-2220.66
84Jul-2219.37
85Jul-220.00
86Jul-220.00
87Jul-220.00
88Jul-220.00
89Aug-2282.03
90Aug-2268.75
91Aug-2268.75
92Aug-2268.75
93Aug-2264.84
94Aug-2261.53
95Aug-2260.94
96Aug-2260.94
97Aug-2260.94
98Aug-2260.63
99Aug-2257.03
100Aug-2257.03
101Aug-2253.72
102Aug-2253.13
103Aug-2253.13
104Aug-2250.00
105Aug-2250.00
106Aug-2250.00
107Aug-2249.22
108Aug-2249.22
109Aug-2249.22
110Aug-2249.22
111Aug-2249.22
112Aug-2245.31
113Aug-2245.31
114Aug-2241.88
115Aug-2241.41
116Aug-2241.41
117Aug-2241.41
118Aug-2237.50
119Aug-2237.50
120Aug-2237.50
121Aug-2237.50
122Aug-2237.50
123Aug-2237.50
124Aug-2237.50
125Aug-2237.50
126Aug-2237.50
127Aug-2237.50
128Aug-2237.50
129Aug-2237.50
130Aug-2237.50
131Aug-2237.50
132Aug-2237.50
133Aug-2237.50
134Aug-2237.50
135Aug-2237.50
136Aug-2237.50
137Aug-2237.50
138Aug-2237.50
139Aug-2237.50
140Aug-2237.50
141Aug-2237.50
142Aug-2237.50
143Aug-2237.50
144Aug-2237.50
145Aug-2237.50
146Aug-2237.50
147Aug-2237.50
148Aug-2237.50
149Aug-2237.50
150Aug-2237.50
151Aug-2237.50
152Aug-2234.79
153Aug-2232.86
154Aug-2230.28
155Aug-2227.34
156Aug-2226.68
157Aug-2221.88
158Aug-2220.00
159Aug-2220.00
160Aug-2215.02
161Aug-2212.25
162Aug-226.51
163Aug-225.03
164Aug-225.03
165Aug-220.00
166Aug-220.00
167Aug-220.00
168Feb-2393.75
169Feb-2393.75
170Feb-2393.75
171Feb-2393.75
172Feb-2393.75
173Feb-2393.75
174Feb-2393.75
175Feb-2393.75
176Feb-2393.75
177Feb-2393.75
178Feb-2393.75
179Feb-2391.04
180Feb-2389.84
181Feb-2389.84
182Feb-2389.84
183Feb-2389.11
184Feb-2385.94
185Feb-2385.94
186Feb-2385.94
187Feb-2382.03
188Feb-2382.03
189Feb-2382.03
190Feb-2380.36
191Feb-2378.13
192Feb-2378.13
193Feb-2378.13
194Feb-2377.52
195Feb-2377.52
196Feb-2377.52
197Feb-2375.94
198Feb-2374.22
199Feb-2374.22
200Feb-2374.22
201Feb-2371.88
202Feb-2371.43
203Feb-2370.31
204Feb-2370.31
205Feb-2366.41
206Feb-2366.41
207Feb-2366.41
208Feb-2366.41
209Feb-2365.80
210Feb-2362.50
211Feb-2362.50
212Feb-2362.50
213Feb-2362.50
214Feb-2362.50
215Feb-2362.50
216Feb-2362.50
217Feb-2362.50
218Feb-2362.50
219Feb-2362.50
220Feb-2362.50
221Feb-2362.50
222Feb-2362.50
223Feb-2362.50
224Feb-2362.50
225Feb-2362.50
226Feb-2362.50
227Feb-2362.50
228Feb-2362.50
229Feb-2362.50
230Feb-2362.50
231Feb-2362.50
232Feb-2362.50
233Feb-2362.50
234Feb-2362.50
235Feb-2362.50
236Feb-2362.50
237Feb-2362.50
238Feb-2362.50
239Feb-2362.50
240Feb-2362.50
241Feb-2362.50
242Feb-2362.50
243Feb-2362.50
244Feb-2362.50
245Feb-2362.50
246Feb-2362.50
247Feb-2362.50
248Feb-2362.50
249Feb-2362.50
250Feb-2362.50
251Feb-2362.50
252Feb-2362.50
253Feb-2362.50
254Feb-2362.50
255Feb-2362.50
256Feb-2362.50
257Feb-2362.50
258Feb-2362.20
259Feb-2361.02
260Feb-2360.12
261Feb-2359.79
262Feb-2359.79
263Feb-2357.98
264Feb-2357.86
265Feb-2356.25
266Feb-2356.25
267Feb-2354.38
268Feb-2354.38
269Feb-2354.38
270Feb-2350.00
271Feb-2350.00
272Feb-2350.00
273Feb-2350.00
274Feb-2348.44
275Feb-2346.27
276Feb-2346.27
277Feb-2346.27
278Feb-2344.53
279Feb-2342.79
280Feb-2338.89
281Feb-2336.12
282Feb-2334.42
283Feb-2330.03
284Feb-2330.03
285Feb-2330.00
286Feb-2330.00
287Feb-2328.91
288Feb-2325.00
289Feb-2325.00
290Feb-2325.00
291Feb-2325.00
292Feb-2325.00
293Feb-2325.00
294Mar-2320.00
295Mar-2310.00
Sheet1
Cell Formulas
RangeFormula
D2:G2D2=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)))
 
Upvote 0
Try it like
Excel Formula:
=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)))
 
Upvote 0
Solution
Like in below sample data. For Jul and Aug 2022 getting results but for Feb and Mar getting #N/A message.

Book6
ABCDEFG
1MonthOverall ScoreJul-22Aug-22Feb-23Mar-23
2Jul-2276.5630.4530.65#N/A#N/A
3Jul-2275.00
4Jul-2275.00
5Jul-2275.00
6Jul-2275.00
7Jul-2268.75
8Jul-2268.75
9Jul-2268.75
10Jul-2268.75
11Jul-2268.75
12Jul-2268.75
13Jul-2264.84
14Jul-2262.50
15Jul-2260.94
16Jul-2260.94
17Jul-2260.94
18Jul-2259.90
19Jul-2257.03
20Jul-2253.13
21Jul-2253.13
22Jul-2253.13
23Jul-2253.13
24Jul-2253.13
25Jul-2252.52
26Jul-2249.22
27Jul-2249.22
28Jul-2249.22
29Jul-2249.22
30Jul-2248.61
31Jul-2245.31
32Jul-2245.31
33Jul-2245.31
34Jul-2245.31
35Jul-2244.70
36Jul-2241.41
37Jul-2241.41
38Jul-2241.41
39Jul-2241.41
40Jul-2241.41
41Jul-2241.41
42Jul-2241.41
43Jul-2241.41
44Jul-2239.38
45Jul-2237.76
46Jul-2237.50
47Jul-2237.50
48Jul-2237.50
49Jul-2237.50
50Jul-2237.50
51Jul-2237.50
52Jul-2237.50
53Jul-2237.50
54Jul-2237.50
55Jul-2237.50
56Jul-2237.50
57Jul-2237.50
58Jul-2237.50
59Jul-2237.50
60Jul-2237.50
61Jul-2237.50
62Jul-2237.50
63Jul-2237.50
64Jul-2237.50
65Jul-2237.50
66Jul-2237.50
67Jul-2237.50
68Jul-2237.50
69Jul-2237.50
70Jul-2237.50
71Jul-2236.89
72Jul-2231.25
73Jul-2231.25
74Jul-2231.25
75Jul-2230.28
76Jul-2230.00
77Jul-2228.65
78Jul-2225.17
79Jul-2223.44
80Jul-2221.27
81Jul-2221.27
82Jul-2221.27
83Jul-2220.66
84Jul-2219.37
85Jul-220.00
86Jul-220.00
87Jul-220.00
88Jul-220.00
89Aug-2282.03
90Aug-2268.75
91Aug-2268.75
92Aug-2268.75
93Aug-2264.84
94Aug-2261.53
95Aug-2260.94
96Aug-2260.94
97Aug-2260.94
98Aug-2260.63
99Aug-2257.03
100Aug-2257.03
101Aug-2253.72
102Aug-2253.13
103Aug-2253.13
104Aug-2250.00
105Aug-2250.00
106Aug-2250.00
107Aug-2249.22
108Aug-2249.22
109Aug-2249.22
110Aug-2249.22
111Aug-2249.22
112Aug-2245.31
113Aug-2245.31
114Aug-2241.88
115Aug-2241.41
116Aug-2241.41
117Aug-2241.41
118Aug-2237.50
119Aug-2237.50
120Aug-2237.50
121Aug-2237.50
122Aug-2237.50
123Aug-2237.50
124Aug-2237.50
125Aug-2237.50
126Aug-2237.50
127Aug-2237.50
128Aug-2237.50
129Aug-2237.50
130Aug-2237.50
131Aug-2237.50
132Aug-2237.50
133Aug-2237.50
134Aug-2237.50
135Aug-2237.50
136Aug-2237.50
137Aug-2237.50
138Aug-2237.50
139Aug-2237.50
140Aug-2237.50
141Aug-2237.50
142Aug-2237.50
143Aug-2237.50
144Aug-2237.50
145Aug-2237.50
146Aug-2237.50
147Aug-2237.50
148Aug-2237.50
149Aug-2237.50
150Aug-2237.50
151Aug-2237.50
152Aug-2234.79
153Aug-2232.86
154Aug-2230.28
155Aug-2227.34
156Aug-2226.68
157Aug-2221.88
158Aug-2220.00
159Aug-2220.00
160Aug-2215.02
161Aug-2212.25
162Aug-226.51
163Aug-225.03
164Aug-225.03
165Aug-220.00
166Aug-220.00
167Aug-220.00
168Feb-2393.75
169Feb-2393.75
170Feb-2393.75
171Feb-2393.75
172Feb-2393.75
173Feb-2393.75
174Feb-2393.75
175Feb-2393.75
176Feb-2393.75
177Feb-2393.75
178Feb-2393.75
179Feb-2391.04
180Feb-2389.84
181Feb-2389.84
182Feb-2389.84
183Feb-2389.11
184Feb-2385.94
185Feb-2385.94
186Feb-2385.94
187Feb-2382.03
188Feb-2382.03
189Feb-2382.03
190Feb-2380.36
191Feb-2378.13
192Feb-2378.13
193Feb-2378.13
194Feb-2377.52
195Feb-2377.52
196Feb-2377.52
197Feb-2375.94
198Feb-2374.22
199Feb-2374.22
200Feb-2374.22
201Feb-2371.88
202Feb-2371.43
203Feb-2370.31
204Feb-2370.31
205Feb-2366.41
206Feb-2366.41
207Feb-2366.41
208Feb-2366.41
209Feb-2365.80
210Feb-2362.50
211Feb-2362.50
212Feb-2362.50
213Feb-2362.50
214Feb-2362.50
215Feb-2362.50
216Feb-2362.50
217Feb-2362.50
218Feb-2362.50
219Feb-2362.50
220Feb-2362.50
221Feb-2362.50
222Feb-2362.50
223Feb-2362.50
224Feb-2362.50
225Feb-2362.50
226Feb-2362.50
227Feb-2362.50
228Feb-2362.50
229Feb-2362.50
230Feb-2362.50
231Feb-2362.50
232Feb-2362.50
233Feb-2362.50
234Feb-2362.50
235Feb-2362.50
236Feb-2362.50
237Feb-2362.50
238Feb-2362.50
239Feb-2362.50
240Feb-2362.50
241Feb-2362.50
242Feb-2362.50
243Feb-2362.50
244Feb-2362.50
245Feb-2362.50
246Feb-2362.50
247Feb-2362.50
248Feb-2362.50
249Feb-2362.50
250Feb-2362.50
251Feb-2362.50
252Feb-2362.50
253Feb-2362.50
254Feb-2362.50
255Feb-2362.50
256Feb-2362.50
257Feb-2362.50
258Feb-2362.20
259Feb-2361.02
260Feb-2360.12
261Feb-2359.79
262Feb-2359.79
263Feb-2357.98
264Feb-2357.86
265Feb-2356.25
266Feb-2356.25
267Feb-2354.38
268Feb-2354.38
269Feb-2354.38
270Feb-2350.00
271Feb-2350.00
272Feb-2350.00
273Feb-2350.00
274Feb-2348.44
275Feb-2346.27
276Feb-2346.27
277Feb-2346.27
278Feb-2344.53
279Feb-2342.79
280Feb-2338.89
281Feb-2336.12
282Feb-2334.42
283Feb-2330.03
284Feb-2330.03
285Feb-2330.00
286Feb-2330.00
287Feb-2328.91
288Feb-2325.00
289Feb-2325.00
290Feb-2325.00
291Feb-2325.00
292Feb-2325.00
293Feb-2325.00
294Mar-2320.00
295Mar-2310.00
Sheet1
Cell Formulas
RangeFormula
D2:G2D2=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
Excel Formula:
=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.
 
Upvote 0
In that case I suspect that it maybe down to floating point precision, are those values the result of formulae?
 
Upvote 0
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)))
 
Upvote 0

Forum statistics

Threads
1,215,147
Messages
6,123,297
Members
449,095
Latest member
Chestertim

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top