Get previous 6 months bottom 25% monthly average values

[Firasath]

Hi,

The requirement is to get monthly average of bottom 25% agents' score on monthly basis. I'm able to achieve this by running the formula on a single month data in a separate sheet. But I want to have a formula to get the monthly average bottom 25% scores where monthly data is saved in a single sheet. I have added a working sheet with the demo data and formula currently I am using. Please advise.

CalcBottom25%Formula.xlsx
	A	B	C	D	E	F
1	Month	Score
2	Jan-21	86.66			Avg. Bottom 25% Values	Formula
3	Jan-21	75.00		Overall	11.85	AVERAGE(SMALL($B:$B,ROW(INDIRECT("1:"&ROUND(COUNT($B:$B)/4,0)))))
4	Jan-21	75.00		Jan-21
5	Jan-21	75.00		Feb-21
6	Jan-21	74.16		Mar-21
7	Jan-21	72.88		Apr-21
8	Jan-21	68.81
9	Jan-21	68.76
10	Jan-21	68.75
11	Jan-21	67.92
12	Jan-21	66.48
13	Jan-21	0.00
14	Feb-21	62.51
15	Feb-21	62.50
16	Feb-21	62.50
17	Feb-21	62.50
18	Feb-21	58.70
19	Feb-21	56.25
20	Feb-21	55.89
21	Feb-21	53.75
22	Feb-21	0.00
23	Mar-21	52.49
24	Mar-21	52.21
25	Mar-21	50.00
26	Mar-21	43.00
27	Mar-21	40.00
28	Mar-21	33.00
29	Mar-21	32.00
30	Mar-21	31.00
31	Mar-21	29.00
32	Mar-21	27.00
33	Mar-21	24.00
34	Mar-21	20.00
35	Mar-21	19.00
36	Mar-21	17.00
37	Mar-21	7.00
38	Mar-21	5.00
39	Mar-21	0.00
40	Mar-21	0.00
41	Apr-21	79.00
42	Apr-21	79.00
43	Apr-21	78.00
44	Apr-21	78.00
45	Apr-21	76.00
46	Apr-21	76.00
47	Apr-21	75.00
48	Apr-21	75.00
49	Apr-21	74.00
50	Apr-21	73.00
51	Apr-21	73.00
52	Apr-21	72.00
53	Apr-21	72.00
54	Apr-21	71.00
55	Apr-21	70.00
56	Apr-21	70.00
57	Apr-21	70.00
58	Apr-21	68.00
59	Apr-21	68.00
60	Apr-21	68.00
61	Apr-21	67.00
62	Apr-21	65.00
63	Apr-21	65.00
64	Apr-21	64.00
65	Apr-21	63.00
66	Apr-21	62.00
67	Apr-21	62.00
68	Apr-21	61.00
69	Apr-21	61.00
70	Apr-21	61.00
71	Apr-21	61.00
72	Apr-21	61.00
73	Apr-21	60.00
74	Apr-21	59.00
75	Apr-21	59.00
76	Apr-21	58.00
77	Apr-21	58.00
78	Apr-21	58.00
79	Apr-21	56.00
80	Apr-21	55.00
81	Apr-21	55.00
82	Apr-21	55.00
83	Apr-21	54.00
84	Apr-21	52.00
85	Apr-21	52.00
86	Apr-21	51.00
87	Apr-21	51.00
88	Apr-21	51.00
89	Apr-21	49.00
90	Apr-21	49.00
91	Apr-21	48.00
92	Apr-21	48.00
93	Apr-21	48.00
94	Apr-21	47.00
95	Apr-21	46.00
96	Apr-21	46.00
97	Apr-21	46.00
98	Apr-21	45.00
99	Apr-21	45.00
100	Apr-21	45.00
101	Apr-21	45.00
102	Apr-21	44.00
103	Apr-21	43.00
104	Apr-21	42.00
105	Apr-21	42.00
106	Apr-21	42.00
107	Apr-21	41.00
108	Apr-21	41.00
109	Apr-21	41.00
110	Apr-21	39.00
111	Apr-21	38.00
112	Apr-21	38.00
113	Apr-21	37.00
114	Apr-21	37.00
115	Apr-21	36.00
116	Apr-21	36.00
117	Apr-21	36.00
118	Apr-21	35.00
119	Apr-21	35.00
120	Apr-21	35.00
121	Apr-21	34.00
122	Apr-21	34.00	16.85
123	Apr-21	33.00	16.43
124	Apr-21	31.00
125	Apr-21	30.00
126	Apr-21	30.00
127	Apr-21	28.00
128	Apr-21	26.00
129	Apr-21	25.00
130	Apr-21	24.00
131	Apr-21	24.00
132	Apr-21	23.00
133	Apr-21	23.00
134	Apr-21	23.00
135	Apr-21	22.00
136	Apr-21	22.00
137	Apr-21	21.00
138	Apr-21	18.00
139	Apr-21	17.00
140	Apr-21	17.00
141	Apr-21	16.00
142	Apr-21	16.00
143	Apr-21	16.00
144	Apr-21	15.00
145	Apr-21	14.00
146	Apr-21	14.00
147	Apr-21	14.00
148	Apr-21	14.00
149	Apr-21	13.00
150	Apr-21	13.00
151	Apr-21	13.00
152	Apr-21	11.00
153	Apr-21	11.00
154	Apr-21	10.00
155	Apr-21	10.00
156	Apr-21	10.00
157	Apr-21	10.00
158	Apr-21	0.00
159	Apr-21	0.00
160	Apr-21	0.00
161	Apr-21	0.00
162	Apr-21	0.00
Sheet2

Cell Formulas
Range	Formula
E3	E3	=AVERAGE(SMALL($B:$B,ROW(INDIRECT("1:"&ROUND(COUNT($B:$B)/4,0)))))
C122:C123	C122	=AVERAGE(B122:B162)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Firasath]

Thanks for the info. Updated my profile. I'm using Microsoft 365.

***Please ignore the subject "previous 6 months" as I was intending earlier to put a different requirement.

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E
1	Month	Score
2	01/01/2021	86.66			Avg. Bottom 25% Values
3	01/01/2021	75.00		Overall	11.85
4	01/01/2021	75.00		01/01/2021	44.80
5	01/01/2021	75.00		01/02/2021	26.88
6	01/01/2021	74.16		01/03/2021	3.00
7	01/01/2021	72.88		01/04/2021	12.77
8	01/01/2021	68.81
9	01/01/2021	68.76
10	01/01/2021	68.75
11	01/01/2021	67.92
12	01/01/2021	66.48
13	01/01/2021	0.00
14	01/02/2021	62.51
15	01/02/2021	62.50
16	01/02/2021	62.50
17	01/02/2021	62.50
18	01/02/2021	58.70
19	01/02/2021	56.25
20	01/02/2021	55.89
21	01/02/2021	53.75
22	01/02/2021	0.00
23	01/03/2021	52.49
24	01/03/2021	52.21
25	01/03/2021	50.00
26	01/03/2021	43.00
27	01/03/2021	40.00
28	01/03/2021	33.00
29	01/03/2021	32.00
30	01/03/2021	31.00
31	01/03/2021	29.00
32	01/03/2021	27.00
33	01/03/2021	24.00
34	01/03/2021	20.00
35	01/03/2021	19.00
36	01/03/2021	17.00
37	01/03/2021	7.00
38	01/03/2021	5.00
39	01/03/2021	0.00
40	01/03/2021	0.00
41	01/04/2021	79.00
42	01/04/2021	79.00
43	01/04/2021	78.00
44	01/04/2021	78.00
45	01/04/2021	76.00
46	01/04/2021	76.00
47	01/04/2021	75.00
48	01/04/2021	75.00
49	01/04/2021	74.00
50	01/04/2021	73.00
51	01/04/2021	73.00
52	01/04/2021	72.00
53	01/04/2021	72.00
54	01/04/2021	71.00
55	01/04/2021	70.00
56	01/04/2021	70.00
57	01/04/2021	70.00
58	01/04/2021	68.00
59	01/04/2021	68.00
60	01/04/2021	68.00
61	01/04/2021	67.00
62	01/04/2021	65.00
63	01/04/2021	65.00
64	01/04/2021	64.00
65	01/04/2021	63.00
66	01/04/2021	62.00
67	01/04/2021	62.00
68	01/04/2021	61.00
69	01/04/2021	61.00
70	01/04/2021	61.00
71	01/04/2021	61.00
72	01/04/2021	61.00
73	01/04/2021	60.00
74	01/04/2021	59.00
75	01/04/2021	59.00
76	01/04/2021	58.00
77	01/04/2021	58.00
78	01/04/2021	58.00
79	01/04/2021	56.00
80	01/04/2021	55.00
81	01/04/2021	55.00
82	01/04/2021	55.00
83	01/04/2021	54.00
84	01/04/2021	52.00
85	01/04/2021	52.00
86	01/04/2021	51.00
87	01/04/2021	51.00
88	01/04/2021	51.00
89	01/04/2021	49.00
90	01/04/2021	49.00
91	01/04/2021	48.00
92	01/04/2021	48.00
93	01/04/2021	48.00
94	01/04/2021	47.00
95	01/04/2021	46.00
96	01/04/2021	46.00
97	01/04/2021	46.00
98	01/04/2021	45.00
99	01/04/2021	45.00
100	01/04/2021	45.00
101	01/04/2021	45.00
102	01/04/2021	44.00
103	01/04/2021	43.00
104	01/04/2021	42.00
105	01/04/2021	42.00
106	01/04/2021	42.00
107	01/04/2021	41.00
108	01/04/2021	41.00
109	01/04/2021	41.00
110	01/04/2021	39.00
111	01/04/2021	38.00
112	01/04/2021	38.00
113	01/04/2021	37.00
114	01/04/2021	37.00
115	01/04/2021	36.00
116	01/04/2021	36.00
117	01/04/2021	36.00
118	01/04/2021	35.00
119	01/04/2021	35.00
120	01/04/2021	35.00
121	01/04/2021	34.00
122	01/04/2021	34.00	16.8536585
123	01/04/2021	33.00	16.425
124	01/04/2021	31.00
125	01/04/2021	30.00
126	01/04/2021	30.00
127	01/04/2021	28.00
128	01/04/2021	26.00
129	01/04/2021	25.00
130	01/04/2021	24.00
131	01/04/2021	24.00
132	01/04/2021	23.00
133	01/04/2021	23.00
134	01/04/2021	23.00
135	01/04/2021	22.00
136	01/04/2021	22.00
137	01/04/2021	21.00
138	01/04/2021	18.00
139	01/04/2021	17.00
140	01/04/2021	17.00
141	01/04/2021	16.00
142	01/04/2021	16.00
143	01/04/2021	16.00
144	01/04/2021	15.00
145	01/04/2021	14.00
146	01/04/2021	14.00
147	01/04/2021	14.00
148	01/04/2021	14.00
149	01/04/2021	13.00
150	01/04/2021	13.00
151	01/04/2021	13.00
152	01/04/2021	11.00
153	01/04/2021	11.00
154	01/04/2021	10.00
155	01/04/2021	10.00
156	01/04/2021	10.00
157	01/04/2021	10.00
158	01/04/2021	0.00
159	01/04/2021	0.00
160	01/04/2021	0.00
161	01/04/2021	0.00
162	01/04/2021	0.00
Main

Cell Formulas
Range	Formula
E3	E3	=AVERAGE(SMALL($B:$B,ROW(INDIRECT("1:"&ROUND(COUNT($B:$B)/4,0)))))
E4:E7	E4	=LET(f,FILTER($B$2:$B$500,($A$2:$A$500>=D4)*($A$2:$A$500<=EOMONTH(D4,0))),AVERAGE(TAKE(SORT(f),ROWS(f)/4)))
C122:C123	C122	=AVERAGE(B122:B162)

 

[Firasath]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E
1	Month	Score
2	01/01/2021	86.66			Avg. Bottom 25% Values
3	01/01/2021	75.00		Overall	11.85
4	01/01/2021	75.00		01/01/2021	44.80
5	01/01/2021	75.00		01/02/2021	26.88
6	01/01/2021	74.16		01/03/2021	3.00
7	01/01/2021	72.88		01/04/2021	12.77
8	01/01/2021	68.81
9	01/01/2021	68.76
10	01/01/2021	68.75
11	01/01/2021	67.92
12	01/01/2021	66.48
13	01/01/2021	0.00
14	01/02/2021	62.51
15	01/02/2021	62.50
16	01/02/2021	62.50
17	01/02/2021	62.50
18	01/02/2021	58.70
19	01/02/2021	56.25
20	01/02/2021	55.89
21	01/02/2021	53.75
22	01/02/2021	0.00
23	01/03/2021	52.49
24	01/03/2021	52.21
25	01/03/2021	50.00
26	01/03/2021	43.00
27	01/03/2021	40.00
28	01/03/2021	33.00
29	01/03/2021	32.00
30	01/03/2021	31.00
31	01/03/2021	29.00
32	01/03/2021	27.00
33	01/03/2021	24.00
34	01/03/2021	20.00
35	01/03/2021	19.00
36	01/03/2021	17.00
37	01/03/2021	7.00
38	01/03/2021	5.00
39	01/03/2021	0.00
40	01/03/2021	0.00
41	01/04/2021	79.00
42	01/04/2021	79.00
43	01/04/2021	78.00
44	01/04/2021	78.00
45	01/04/2021	76.00
46	01/04/2021	76.00
47	01/04/2021	75.00
48	01/04/2021	75.00
49	01/04/2021	74.00
50	01/04/2021	73.00
51	01/04/2021	73.00
52	01/04/2021	72.00
53	01/04/2021	72.00
54	01/04/2021	71.00
55	01/04/2021	70.00
56	01/04/2021	70.00
57	01/04/2021	70.00
58	01/04/2021	68.00
59	01/04/2021	68.00
60	01/04/2021	68.00
61	01/04/2021	67.00
62	01/04/2021	65.00
63	01/04/2021	65.00
64	01/04/2021	64.00
65	01/04/2021	63.00
66	01/04/2021	62.00
67	01/04/2021	62.00
68	01/04/2021	61.00
69	01/04/2021	61.00
70	01/04/2021	61.00
71	01/04/2021	61.00
72	01/04/2021	61.00
73	01/04/2021	60.00
74	01/04/2021	59.00
75	01/04/2021	59.00
76	01/04/2021	58.00
77	01/04/2021	58.00
78	01/04/2021	58.00
79	01/04/2021	56.00
80	01/04/2021	55.00
81	01/04/2021	55.00
82	01/04/2021	55.00
83	01/04/2021	54.00
84	01/04/2021	52.00
85	01/04/2021	52.00
86	01/04/2021	51.00
87	01/04/2021	51.00
88	01/04/2021	51.00
89	01/04/2021	49.00
90	01/04/2021	49.00
91	01/04/2021	48.00
92	01/04/2021	48.00
93	01/04/2021	48.00
94	01/04/2021	47.00
95	01/04/2021	46.00
96	01/04/2021	46.00
97	01/04/2021	46.00
98	01/04/2021	45.00
99	01/04/2021	45.00
100	01/04/2021	45.00
101	01/04/2021	45.00
102	01/04/2021	44.00
103	01/04/2021	43.00
104	01/04/2021	42.00
105	01/04/2021	42.00
106	01/04/2021	42.00
107	01/04/2021	41.00
108	01/04/2021	41.00
109	01/04/2021	41.00
110	01/04/2021	39.00
111	01/04/2021	38.00
112	01/04/2021	38.00
113	01/04/2021	37.00
114	01/04/2021	37.00
115	01/04/2021	36.00
116	01/04/2021	36.00
117	01/04/2021	36.00
118	01/04/2021	35.00
119	01/04/2021	35.00
120	01/04/2021	35.00
121	01/04/2021	34.00
122	01/04/2021	34.00	16.8536585
123	01/04/2021	33.00	16.425
124	01/04/2021	31.00
125	01/04/2021	30.00
126	01/04/2021	30.00
127	01/04/2021	28.00
128	01/04/2021	26.00
129	01/04/2021	25.00
130	01/04/2021	24.00
131	01/04/2021	24.00
132	01/04/2021	23.00
133	01/04/2021	23.00
134	01/04/2021	23.00
135	01/04/2021	22.00
136	01/04/2021	22.00
137	01/04/2021	21.00
138	01/04/2021	18.00
139	01/04/2021	17.00
140	01/04/2021	17.00
141	01/04/2021	16.00
142	01/04/2021	16.00
143	01/04/2021	16.00
144	01/04/2021	15.00
145	01/04/2021	14.00
146	01/04/2021	14.00
147	01/04/2021	14.00
148	01/04/2021	14.00
149	01/04/2021	13.00
150	01/04/2021	13.00
151	01/04/2021	13.00
152	01/04/2021	11.00
153	01/04/2021	11.00
154	01/04/2021	10.00
155	01/04/2021	10.00
156	01/04/2021	10.00
157	01/04/2021	10.00
158	01/04/2021	0.00
159	01/04/2021	0.00
160	01/04/2021	0.00
161	01/04/2021	0.00
162	01/04/2021	0.00
Main

Cell Formulas
Range	Formula
E3	E3	=AVERAGE(SMALL($B:$B,ROW(INDIRECT("1:"&ROUND(COUNT($B:$B)/4,0)))))
E4:E7	E4	=LET(f,FILTER($B$2:$B$500,($A$2:$A$500>=D4)*($A$2:$A$500<=EOMONTH(D4,0))),AVERAGE(TAKE(SORT(f),ROWS(f)/4)))
C122:C123	C122	=AVERAGE(B122:B162)

Wow, it worked very well with Jan & Feb months, but for Mar and Apr is missed to consider one number most probably I am rounding bottom 25% number. I have pasted my numbers which I dervied by manual calculation in column "Achievement". Please check and advise.

CalcBottom25%Formula.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Month	Score
2	Jan-21	86.66			Avg. Bottom 25% Values	Formula	# of Records	0.25	Rounded 25%	Achievement
3	Jan-21	75.00		Overall	11.85	AVERAGE(SMALL($B:$B,ROW(INDIRECT("1:"&ROUND(COUNT($B:$B)/4,0)))))
4	Jan-21	75.00		Jan-21	44.80		12	3	3	44.8
5	Jan-21	75.00		Feb-21	26.88		9	2.25	2	26.88
6	Jan-21	74.16		Mar-21	3.00		18	4.5	5	5.8
7	Jan-21	72.88		Apr-21	12.77		122	30.5	31	13.1
8	Jan-21	68.81
9	Jan-21	68.76
10	Jan-21	68.75
11	Jan-21	67.92
12	Jan-21	66.48
13	Jan-21	0.00
14	Feb-21	62.51		2.25
15	Feb-21	62.50
16	Feb-21	62.50
17	Feb-21	62.50
18	Feb-21	58.70
19	Feb-21	56.25
20	Feb-21	55.89
21	Feb-21	53.75
22	Feb-21	0.00
23	Mar-21	52.49	5
24	Mar-21	52.21
25	Mar-21	50.00
26	Mar-21	43.00
27	Mar-21	40.00
28	Mar-21	33.00
29	Mar-21	32.00
30	Mar-21	31.00
31	Mar-21	29.00
32	Mar-21	27.00
33	Mar-21	24.00
34	Mar-21	20.00
35	Mar-21	19.00
36	Mar-21	17.00
37	Mar-21	7.00
38	Mar-21	5.00
39	Mar-21	0.00
40	Mar-21	0.00
41	Apr-21	79.00		31
42	Apr-21	79.00
43	Apr-21	78.00
44	Apr-21	78.00
45	Apr-21	76.00
46	Apr-21	76.00
47	Apr-21	75.00
48	Apr-21	75.00
49	Apr-21	74.00
50	Apr-21	73.00
51	Apr-21	73.00
52	Apr-21	72.00
53	Apr-21	72.00
54	Apr-21	71.00
55	Apr-21	70.00
56	Apr-21	70.00
57	Apr-21	70.00
58	Apr-21	68.00
59	Apr-21	68.00
60	Apr-21	68.00
61	Apr-21	67.00
62	Apr-21	65.00
63	Apr-21	65.00
64	Apr-21	64.00
65	Apr-21	63.00
66	Apr-21	62.00
67	Apr-21	62.00
68	Apr-21	61.00
69	Apr-21	61.00
70	Apr-21	61.00
71	Apr-21	61.00
72	Apr-21	61.00
73	Apr-21	60.00
74	Apr-21	59.00
75	Apr-21	59.00
76	Apr-21	58.00
77	Apr-21	58.00
78	Apr-21	58.00
79	Apr-21	56.00
80	Apr-21	55.00
81	Apr-21	55.00
82	Apr-21	55.00
83	Apr-21	54.00
84	Apr-21	52.00
85	Apr-21	52.00
86	Apr-21	51.00
87	Apr-21	51.00
88	Apr-21	51.00
89	Apr-21	49.00
90	Apr-21	49.00
91	Apr-21	48.00
92	Apr-21	48.00
93	Apr-21	48.00
94	Apr-21	47.00
95	Apr-21	46.00
96	Apr-21	46.00
97	Apr-21	46.00
98	Apr-21	45.00
99	Apr-21	45.00
100	Apr-21	45.00
101	Apr-21	45.00
102	Apr-21	44.00
103	Apr-21	43.00
104	Apr-21	42.00
105	Apr-21	42.00
106	Apr-21	42.00
107	Apr-21	41.00
108	Apr-21	41.00
109	Apr-21	41.00
110	Apr-21	39.00
111	Apr-21	38.00
112	Apr-21	38.00
113	Apr-21	37.00
114	Apr-21	37.00
115	Apr-21	36.00
116	Apr-21	36.00
117	Apr-21	36.00
118	Apr-21	35.00
119	Apr-21	35.00
120	Apr-21	35.00
121	Apr-21	34.00
122	Apr-21	34.00
123	Apr-21	33.00
124	Apr-21	31.00
125	Apr-21	30.00
126	Apr-21	30.00
127	Apr-21	28.00
128	Apr-21	26.00
129	Apr-21	25.00
130	Apr-21	24.00
131	Apr-21	24.00
132	Apr-21	23.00
133	Apr-21	23.00
134	Apr-21	23.00
135	Apr-21	22.00
136	Apr-21	22.00
137	Apr-21	21.00
138	Apr-21	18.00
139	Apr-21	17.00
140	Apr-21	17.00
141	Apr-21	16.00
142	Apr-21	16.00
143	Apr-21	16.00
144	Apr-21	15.00
145	Apr-21	14.00
146	Apr-21	14.00
147	Apr-21	14.00
148	Apr-21	14.00
149	Apr-21	13.00
150	Apr-21	13.00
151	Apr-21	13.00
152	Apr-21	11.00
153	Apr-21	11.00
154	Apr-21	10.00
155	Apr-21	10.00
156	Apr-21	10.00
157	Apr-21	10.00
158	Apr-21	0.00
159	Apr-21	0.00
160	Apr-21	0.00
161	Apr-21	0.00
162	Apr-21	0.00
Sheet2

Cell Formulas
Range	Formula
G4:G7	G4	=COUNTIF(A:A,$D4)
H4:H7	H4	=G4/4
I4:I7	I4	=ROUND(H4,0)
E3	E3	=AVERAGE(SMALL($B:$B,ROW(INDIRECT("1:"&ROUND(COUNT($B:$B)/4,0)))))
E4:E7	E4	=LET(f,FILTER($B$2:$B$500,($A$2:$A$500>=D4)*($A$2:$A$500<=EOMONTH(D4,0))),AVERAGE(TAKE(SORT(f),ROWS(f)/4)))
D14	D14	=9/4
C23	C23	=ROUND(18/4,0)
D41	D41	=ROUND(122/4,0)

 

[Fluff]

In that case use

=LET(f,FILTER($B$2:$B$500,($A$2:$A$500>=D4)*($A$2:$A$500<=EOMONTH(D4,0))),AVERAGE(TAKE(SORT(f),ROUND(ROWS(f)/4,0))))

 

[Firasath]

In that case use

=LET(f,FILTER($B$2:$B$500,($A$2:$A$500>=D4)*($A$2:$A$500<=EOMONTH(D4,0))),AVERAGE(TAKE(SORT(f),ROUND(ROWS(f)/4,0))))

Thank you very much for making my life easy and introducing to me new Functions "Let" & "Take" .

 

[Fluff]

You're welcome & thanks for the feedback.