Get previous 6 months bottom 25% monthly average values

Firasath

Board Regular
Joined
Feb 20, 2011
Messages
134
Office Version
  1. 365
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
ABCDEF
1MonthScore
2Jan-2186.66Avg. Bottom 25% ValuesFormula
3Jan-2175.00Overall11.85AVERAGE(SMALL($B:$B,ROW(INDIRECT("1:"&ROUND(COUNT($B:$B)/4,0)))))
4Jan-2175.00Jan-21
5Jan-2175.00Feb-21
6Jan-2174.16Mar-21
7Jan-2172.88Apr-21
8Jan-2168.81
9Jan-2168.76
10Jan-2168.75
11Jan-2167.92
12Jan-2166.48
13Jan-210.00
14Feb-2162.51
15Feb-2162.50
16Feb-2162.50
17Feb-2162.50
18Feb-2158.70
19Feb-2156.25
20Feb-2155.89
21Feb-2153.75
22Feb-210.00
23Mar-2152.49
24Mar-2152.21
25Mar-2150.00
26Mar-2143.00
27Mar-2140.00
28Mar-2133.00
29Mar-2132.00
30Mar-2131.00
31Mar-2129.00
32Mar-2127.00
33Mar-2124.00
34Mar-2120.00
35Mar-2119.00
36Mar-2117.00
37Mar-217.00
38Mar-215.00
39Mar-210.00
40Mar-210.00
41Apr-2179.00
42Apr-2179.00
43Apr-2178.00
44Apr-2178.00
45Apr-2176.00
46Apr-2176.00
47Apr-2175.00
48Apr-2175.00
49Apr-2174.00
50Apr-2173.00
51Apr-2173.00
52Apr-2172.00
53Apr-2172.00
54Apr-2171.00
55Apr-2170.00
56Apr-2170.00
57Apr-2170.00
58Apr-2168.00
59Apr-2168.00
60Apr-2168.00
61Apr-2167.00
62Apr-2165.00
63Apr-2165.00
64Apr-2164.00
65Apr-2163.00
66Apr-2162.00
67Apr-2162.00
68Apr-2161.00
69Apr-2161.00
70Apr-2161.00
71Apr-2161.00
72Apr-2161.00
73Apr-2160.00
74Apr-2159.00
75Apr-2159.00
76Apr-2158.00
77Apr-2158.00
78Apr-2158.00
79Apr-2156.00
80Apr-2155.00
81Apr-2155.00
82Apr-2155.00
83Apr-2154.00
84Apr-2152.00
85Apr-2152.00
86Apr-2151.00
87Apr-2151.00
88Apr-2151.00
89Apr-2149.00
90Apr-2149.00
91Apr-2148.00
92Apr-2148.00
93Apr-2148.00
94Apr-2147.00
95Apr-2146.00
96Apr-2146.00
97Apr-2146.00
98Apr-2145.00
99Apr-2145.00
100Apr-2145.00
101Apr-2145.00
102Apr-2144.00
103Apr-2143.00
104Apr-2142.00
105Apr-2142.00
106Apr-2142.00
107Apr-2141.00
108Apr-2141.00
109Apr-2141.00
110Apr-2139.00
111Apr-2138.00
112Apr-2138.00
113Apr-2137.00
114Apr-2137.00
115Apr-2136.00
116Apr-2136.00
117Apr-2136.00
118Apr-2135.00
119Apr-2135.00
120Apr-2135.00
121Apr-2134.00
122Apr-2134.0016.85
123Apr-2133.0016.43
124Apr-2131.00
125Apr-2130.00
126Apr-2130.00
127Apr-2128.00
128Apr-2126.00
129Apr-2125.00
130Apr-2124.00
131Apr-2124.00
132Apr-2123.00
133Apr-2123.00
134Apr-2123.00
135Apr-2122.00
136Apr-2122.00
137Apr-2121.00
138Apr-2118.00
139Apr-2117.00
140Apr-2117.00
141Apr-2116.00
142Apr-2116.00
143Apr-2116.00
144Apr-2115.00
145Apr-2114.00
146Apr-2114.00
147Apr-2114.00
148Apr-2114.00
149Apr-2113.00
150Apr-2113.00
151Apr-2113.00
152Apr-2111.00
153Apr-2111.00
154Apr-2110.00
155Apr-2110.00
156Apr-2110.00
157Apr-2110.00
158Apr-210.00
159Apr-210.00
160Apr-210.00
161Apr-210.00
162Apr-210.00
Sheet2
Cell Formulas
RangeFormula
E3E3=AVERAGE(SMALL($B:$B,ROW(INDIRECT("1:"&ROUND(COUNT($B:$B)/4,0)))))
C122:C123C122=AVERAGE(B122:B162)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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’)
 
Upvote 0
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.
 
Upvote 0
Thanks for that.
How about
Fluff.xlsm
ABCDE
1MonthScore
201/01/202186.66Avg. Bottom 25% Values
301/01/202175.00Overall11.85
401/01/202175.0001/01/202144.80
501/01/202175.0001/02/202126.88
601/01/202174.1601/03/20213.00
701/01/202172.8801/04/202112.77
801/01/202168.81
901/01/202168.76
1001/01/202168.75
1101/01/202167.92
1201/01/202166.48
1301/01/20210.00
1401/02/202162.51
1501/02/202162.50
1601/02/202162.50
1701/02/202162.50
1801/02/202158.70
1901/02/202156.25
2001/02/202155.89
2101/02/202153.75
2201/02/20210.00
2301/03/202152.49
2401/03/202152.21
2501/03/202150.00
2601/03/202143.00
2701/03/202140.00
2801/03/202133.00
2901/03/202132.00
3001/03/202131.00
3101/03/202129.00
3201/03/202127.00
3301/03/202124.00
3401/03/202120.00
3501/03/202119.00
3601/03/202117.00
3701/03/20217.00
3801/03/20215.00
3901/03/20210.00
4001/03/20210.00
4101/04/202179.00
4201/04/202179.00
4301/04/202178.00
4401/04/202178.00
4501/04/202176.00
4601/04/202176.00
4701/04/202175.00
4801/04/202175.00
4901/04/202174.00
5001/04/202173.00
5101/04/202173.00
5201/04/202172.00
5301/04/202172.00
5401/04/202171.00
5501/04/202170.00
5601/04/202170.00
5701/04/202170.00
5801/04/202168.00
5901/04/202168.00
6001/04/202168.00
6101/04/202167.00
6201/04/202165.00
6301/04/202165.00
6401/04/202164.00
6501/04/202163.00
6601/04/202162.00
6701/04/202162.00
6801/04/202161.00
6901/04/202161.00
7001/04/202161.00
7101/04/202161.00
7201/04/202161.00
7301/04/202160.00
7401/04/202159.00
7501/04/202159.00
7601/04/202158.00
7701/04/202158.00
7801/04/202158.00
7901/04/202156.00
8001/04/202155.00
8101/04/202155.00
8201/04/202155.00
8301/04/202154.00
8401/04/202152.00
8501/04/202152.00
8601/04/202151.00
8701/04/202151.00
8801/04/202151.00
8901/04/202149.00
9001/04/202149.00
9101/04/202148.00
9201/04/202148.00
9301/04/202148.00
9401/04/202147.00
9501/04/202146.00
9601/04/202146.00
9701/04/202146.00
9801/04/202145.00
9901/04/202145.00
10001/04/202145.00
10101/04/202145.00
10201/04/202144.00
10301/04/202143.00
10401/04/202142.00
10501/04/202142.00
10601/04/202142.00
10701/04/202141.00
10801/04/202141.00
10901/04/202141.00
11001/04/202139.00
11101/04/202138.00
11201/04/202138.00
11301/04/202137.00
11401/04/202137.00
11501/04/202136.00
11601/04/202136.00
11701/04/202136.00
11801/04/202135.00
11901/04/202135.00
12001/04/202135.00
12101/04/202134.00
12201/04/202134.0016.8536585
12301/04/202133.0016.425
12401/04/202131.00
12501/04/202130.00
12601/04/202130.00
12701/04/202128.00
12801/04/202126.00
12901/04/202125.00
13001/04/202124.00
13101/04/202124.00
13201/04/202123.00
13301/04/202123.00
13401/04/202123.00
13501/04/202122.00
13601/04/202122.00
13701/04/202121.00
13801/04/202118.00
13901/04/202117.00
14001/04/202117.00
14101/04/202116.00
14201/04/202116.00
14301/04/202116.00
14401/04/202115.00
14501/04/202114.00
14601/04/202114.00
14701/04/202114.00
14801/04/202114.00
14901/04/202113.00
15001/04/202113.00
15101/04/202113.00
15201/04/202111.00
15301/04/202111.00
15401/04/202110.00
15501/04/202110.00
15601/04/202110.00
15701/04/202110.00
15801/04/20210.00
15901/04/20210.00
16001/04/20210.00
16101/04/20210.00
16201/04/20210.00
Main
Cell Formulas
RangeFormula
E3E3=AVERAGE(SMALL($B:$B,ROW(INDIRECT("1:"&ROUND(COUNT($B:$B)/4,0)))))
E4:E7E4=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:C123C122=AVERAGE(B122:B162)
 
Upvote 0
Thanks for that.
How about
Fluff.xlsm
ABCDE
1MonthScore
201/01/202186.66Avg. Bottom 25% Values
301/01/202175.00Overall11.85
401/01/202175.0001/01/202144.80
501/01/202175.0001/02/202126.88
601/01/202174.1601/03/20213.00
701/01/202172.8801/04/202112.77
801/01/202168.81
901/01/202168.76
1001/01/202168.75
1101/01/202167.92
1201/01/202166.48
1301/01/20210.00
1401/02/202162.51
1501/02/202162.50
1601/02/202162.50
1701/02/202162.50
1801/02/202158.70
1901/02/202156.25
2001/02/202155.89
2101/02/202153.75
2201/02/20210.00
2301/03/202152.49
2401/03/202152.21
2501/03/202150.00
2601/03/202143.00
2701/03/202140.00
2801/03/202133.00
2901/03/202132.00
3001/03/202131.00
3101/03/202129.00
3201/03/202127.00
3301/03/202124.00
3401/03/202120.00
3501/03/202119.00
3601/03/202117.00
3701/03/20217.00
3801/03/20215.00
3901/03/20210.00
4001/03/20210.00
4101/04/202179.00
4201/04/202179.00
4301/04/202178.00
4401/04/202178.00
4501/04/202176.00
4601/04/202176.00
4701/04/202175.00
4801/04/202175.00
4901/04/202174.00
5001/04/202173.00
5101/04/202173.00
5201/04/202172.00
5301/04/202172.00
5401/04/202171.00
5501/04/202170.00
5601/04/202170.00
5701/04/202170.00
5801/04/202168.00
5901/04/202168.00
6001/04/202168.00
6101/04/202167.00
6201/04/202165.00
6301/04/202165.00
6401/04/202164.00
6501/04/202163.00
6601/04/202162.00
6701/04/202162.00
6801/04/202161.00
6901/04/202161.00
7001/04/202161.00
7101/04/202161.00
7201/04/202161.00
7301/04/202160.00
7401/04/202159.00
7501/04/202159.00
7601/04/202158.00
7701/04/202158.00
7801/04/202158.00
7901/04/202156.00
8001/04/202155.00
8101/04/202155.00
8201/04/202155.00
8301/04/202154.00
8401/04/202152.00
8501/04/202152.00
8601/04/202151.00
8701/04/202151.00
8801/04/202151.00
8901/04/202149.00
9001/04/202149.00
9101/04/202148.00
9201/04/202148.00
9301/04/202148.00
9401/04/202147.00
9501/04/202146.00
9601/04/202146.00
9701/04/202146.00
9801/04/202145.00
9901/04/202145.00
10001/04/202145.00
10101/04/202145.00
10201/04/202144.00
10301/04/202143.00
10401/04/202142.00
10501/04/202142.00
10601/04/202142.00
10701/04/202141.00
10801/04/202141.00
10901/04/202141.00
11001/04/202139.00
11101/04/202138.00
11201/04/202138.00
11301/04/202137.00
11401/04/202137.00
11501/04/202136.00
11601/04/202136.00
11701/04/202136.00
11801/04/202135.00
11901/04/202135.00
12001/04/202135.00
12101/04/202134.00
12201/04/202134.0016.8536585
12301/04/202133.0016.425
12401/04/202131.00
12501/04/202130.00
12601/04/202130.00
12701/04/202128.00
12801/04/202126.00
12901/04/202125.00
13001/04/202124.00
13101/04/202124.00
13201/04/202123.00
13301/04/202123.00
13401/04/202123.00
13501/04/202122.00
13601/04/202122.00
13701/04/202121.00
13801/04/202118.00
13901/04/202117.00
14001/04/202117.00
14101/04/202116.00
14201/04/202116.00
14301/04/202116.00
14401/04/202115.00
14501/04/202114.00
14601/04/202114.00
14701/04/202114.00
14801/04/202114.00
14901/04/202113.00
15001/04/202113.00
15101/04/202113.00
15201/04/202111.00
15301/04/202111.00
15401/04/202110.00
15501/04/202110.00
15601/04/202110.00
15701/04/202110.00
15801/04/20210.00
15901/04/20210.00
16001/04/20210.00
16101/04/20210.00
16201/04/20210.00
Main
Cell Formulas
RangeFormula
E3E3=AVERAGE(SMALL($B:$B,ROW(INDIRECT("1:"&ROUND(COUNT($B:$B)/4,0)))))
E4:E7E4=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:C123C122=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
ABCDEFGHIJ
1MonthScore
2Jan-2186.66Avg. Bottom 25% ValuesFormula# of Records0.25Rounded 25%Achievement
3Jan-2175.00Overall11.85AVERAGE(SMALL($B:$B,ROW(INDIRECT("1:"&ROUND(COUNT($B:$B)/4,0)))))
4Jan-2175.00Jan-2144.80123344.8
5Jan-2175.00Feb-2126.8892.25226.88
6Jan-2174.16Mar-213.00184.555.8
7Jan-2172.88Apr-2112.7712230.53113.1
8Jan-2168.81
9Jan-2168.76
10Jan-2168.75
11Jan-2167.92
12Jan-2166.48
13Jan-210.00
14Feb-2162.512.25
15Feb-2162.50
16Feb-2162.50
17Feb-2162.50
18Feb-2158.70
19Feb-2156.25
20Feb-2155.89
21Feb-2153.75
22Feb-210.00
23Mar-2152.495
24Mar-2152.21
25Mar-2150.00
26Mar-2143.00
27Mar-2140.00
28Mar-2133.00
29Mar-2132.00
30Mar-2131.00
31Mar-2129.00
32Mar-2127.00
33Mar-2124.00
34Mar-2120.00
35Mar-2119.00
36Mar-2117.00
37Mar-217.00
38Mar-215.00
39Mar-210.00
40Mar-210.00
41Apr-2179.0031
42Apr-2179.00
43Apr-2178.00
44Apr-2178.00
45Apr-2176.00
46Apr-2176.00
47Apr-2175.00
48Apr-2175.00
49Apr-2174.00
50Apr-2173.00
51Apr-2173.00
52Apr-2172.00
53Apr-2172.00
54Apr-2171.00
55Apr-2170.00
56Apr-2170.00
57Apr-2170.00
58Apr-2168.00
59Apr-2168.00
60Apr-2168.00
61Apr-2167.00
62Apr-2165.00
63Apr-2165.00
64Apr-2164.00
65Apr-2163.00
66Apr-2162.00
67Apr-2162.00
68Apr-2161.00
69Apr-2161.00
70Apr-2161.00
71Apr-2161.00
72Apr-2161.00
73Apr-2160.00
74Apr-2159.00
75Apr-2159.00
76Apr-2158.00
77Apr-2158.00
78Apr-2158.00
79Apr-2156.00
80Apr-2155.00
81Apr-2155.00
82Apr-2155.00
83Apr-2154.00
84Apr-2152.00
85Apr-2152.00
86Apr-2151.00
87Apr-2151.00
88Apr-2151.00
89Apr-2149.00
90Apr-2149.00
91Apr-2148.00
92Apr-2148.00
93Apr-2148.00
94Apr-2147.00
95Apr-2146.00
96Apr-2146.00
97Apr-2146.00
98Apr-2145.00
99Apr-2145.00
100Apr-2145.00
101Apr-2145.00
102Apr-2144.00
103Apr-2143.00
104Apr-2142.00
105Apr-2142.00
106Apr-2142.00
107Apr-2141.00
108Apr-2141.00
109Apr-2141.00
110Apr-2139.00
111Apr-2138.00
112Apr-2138.00
113Apr-2137.00
114Apr-2137.00
115Apr-2136.00
116Apr-2136.00
117Apr-2136.00
118Apr-2135.00
119Apr-2135.00
120Apr-2135.00
121Apr-2134.00
122Apr-2134.00
123Apr-2133.00
124Apr-2131.00
125Apr-2130.00
126Apr-2130.00
127Apr-2128.00
128Apr-2126.00
129Apr-2125.00
130Apr-2124.00
131Apr-2124.00
132Apr-2123.00
133Apr-2123.00
134Apr-2123.00
135Apr-2122.00
136Apr-2122.00
137Apr-2121.00
138Apr-2118.00
139Apr-2117.00
140Apr-2117.00
141Apr-2116.00
142Apr-2116.00
143Apr-2116.00
144Apr-2115.00
145Apr-2114.00
146Apr-2114.00
147Apr-2114.00
148Apr-2114.00
149Apr-2113.00
150Apr-2113.00
151Apr-2113.00
152Apr-2111.00
153Apr-2111.00
154Apr-2110.00
155Apr-2110.00
156Apr-2110.00
157Apr-2110.00
158Apr-210.00
159Apr-210.00
160Apr-210.00
161Apr-210.00
162Apr-210.00
Sheet2
Cell Formulas
RangeFormula
G4:G7G4=COUNTIF(A:A,$D4)
H4:H7H4=G4/4
I4:I7I4=ROUND(H4,0)
E3E3=AVERAGE(SMALL($B:$B,ROW(INDIRECT("1:"&ROUND(COUNT($B:$B)/4,0)))))
E4:E7E4=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)))
D14D14=9/4
C23C23=ROUND(18/4,0)
D41D41=ROUND(122/4,0)
 
Upvote 0
In that case use
Excel Formula:
=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))))
 
Upvote 1
Solution
In that case use
Excel Formula:
=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" :).
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

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