Formula to Average lowest 10 of most recent 20 values

AZgolfer59 · Nov 17, 2014

I have columns of golf scores (by person and date of round) varying from 1 score to 30 scores and constantly being added to. I need a formula at the bottom to average the lowest 10 scores from the most recent 20. Some columns have many blanks, some are very few. And if a column has less than 20 scores, it needs to average whatever is available. Thanks!

shg · Nov 17, 2014

Row\Col	A	B	C	D
1	Date	Alan	Barb	Cain
2	01-Nov			92
3	02-Nov		95
4	03-Nov
5	04-Nov	83	81	84
6	05-Nov	96	93
7	06-Nov
8	07-Nov	79
9	08-Nov		86
10	09-Nov		98	77
11	10-Nov	80
12	11-Nov		110
13	12-Nov	90	85	93
14	13-Nov		90
15	14-Nov	85	91
16	15-Nov
17	16-Nov	95
18	17-Nov	92		74
19	18-Nov		73	90
20	19-Nov
21	20-Nov		93	72
22		87.5	88.5	83.1

In B22 and copied across:

=IF(COUNT(B1:B21) < 10, AVERAGE(B1:B21), AVERAGE(SMALL(B1:B21, {1,2,3,4,5,6,7,8,9,10})))

EDIT: That doesn't account for only considering the last 20 games.

ghrain22 · Nov 17, 2014

Sample data might be helpful to help see how we can define the most recent 20.

as an array formula(ctrl+shft+enter):
=AVERAGE(IF(RANK(Score,Score,1)<=10,Score,""))

shg · Nov 17, 2014

Ah:

Row\Col	A	B	C
1		75.4	83.6
2	Date	Alan	Barb
3	01-Oct
4	02-Oct	87
5	03-Oct
6	04-Oct	80
7	05-Oct	91
8	06-Oct	87
9	07-Oct	91
10	08-Oct
11	09-Oct
12	10-Oct	79
13	11-Oct	77
14	12-Oct	74
15	13-Oct	73
16	14-Oct	94
17	15-Oct
18	16-Oct	76
19	17-Oct	74
20	18-Oct
21	19-Oct	86
22	20-Oct
23	21-Oct	94
24	22-Oct
25	23-Oct		78
26	24-Oct		92
27	25-Oct
28	26-Oct	86	88
29	27-Oct
30	28-Oct		77
31	29-Oct
32	30-Oct	78	90
33	31-Oct
34	01-Nov
35	02-Nov	78	76
36	03-Nov	75
37	04-Nov
38	05-Nov	93
39	06-Nov	75	84
40	07-Nov	79
41	08-Nov	74

In B2, confirmed with Ctrl+Shift+Enter:

=IF(COUNT(B2:B41) < 10, AVERAGE(B2:B41), AVERAGE(SMALL(B41:INDEX(B2:B41, LARGE(ROW(B2:B41) * ISNUMBER(B2:B41), 20)), {1,2,3,4,5,6,7,8,9,10})))

AZgolfer59 · Nov 17, 2014

Seems like this works! Thanks so much shg!

shg said:
Ah:

Row\Col
A
B
C
1
75.4
83.6
2
Date
Alan
Barb
3
01-Oct
4
02-Oct
87
5
03-Oct
6
04-Oct
80
7
05-Oct
91
8
06-Oct
87
9
07-Oct
91
10
08-Oct
11
09-Oct
12
10-Oct
79
13
11-Oct
77
14
12-Oct
74
15
13-Oct
73
16
14-Oct
94
17
15-Oct
18
16-Oct
76
19
17-Oct
74
20
18-Oct
21
19-Oct
86
22
20-Oct
23
21-Oct
94
24
22-Oct
25
23-Oct
78
26
24-Oct
92
27
25-Oct
28
26-Oct
86
88
29
27-Oct
30
28-Oct
77
31
29-Oct
32
30-Oct
78
90
33
31-Oct
34
01-Nov
35
02-Nov
78
76
36
03-Nov
75
37
04-Nov
38
05-Nov
93
39
06-Nov
75
84
40
07-Nov
79
41
08-Nov
74

<tbody>
</tbody>

In B2, confirmed with Ctrl+Shift+Enter:

=IF(COUNT(B2:B41) < 10, AVERAGE(B2:B41), AVERAGE(SMALL(B41:INDEX(B2:B41, LARGE(ROW(B2:B41) * ISNUMBER(B2:B41), 20)), {1,2,3,4,5,6,7,8,9,10})))

shg · Nov 17, 2014

You're welcome. Note that the dates must be in ascending order (i.e., most recent at bottom).

AZgolfer59 · Nov 17, 2014

Got it, and would it be easy to change the formula to take the lowest 2 score avg from the most recent 5 scores? I tried on my own but seems slightly off.

AZgolfer59 said:
Seems like this works! Thanks so much shg!

shg · Nov 17, 2014

What did you try?

AZgolfer59 · Nov 17, 2014

Here is my example file, I've highlighted the bottom formula cells RED that I've checked and are slightly off from my manual average. Let me know if you are able to open it.

https://drive.google.com/file/d/0BzOCmTWgkhUrcFVkbVlTZjJQWXM/view?usp=sharing

shg said:
What did you try?

shg · Nov 17, 2014

Row\Col	A	B	C
1	Smallest
2	2
3	OfLast
4	5	77.0	81.0
5	Date	Alan	Barb
6	01-Oct
7	02-Oct	87
8	03-Oct		81
9	04-Oct	80
10	05-Oct	91
11	06-Oct	87
12	07-Oct	91
13	08-Oct
14	09-Oct
15	10-Oct	74

In B4,

=IF(COUNT(B5:B15) < Smallest,
AVERAGE(B5:B15),
AVERAGE(SMALL(B15:INDEX(B5:B15, LARGE((ROW(B5:B15) - ROW(B5) + 1) * ISNUMBER(B5:B15), OfLast)), ROW(INDIRECT("1:" & Smallest)))))

Smallest is $A$2 and OfLast is $A$4.

Formula to Average lowest 10 of most recent 20 values

AZgolfer59

New Member

Excel Facts

shg

MrExcel MVP

ghrain22

Active Member

shg

MrExcel MVP

AZgolfer59

New Member

shg

MrExcel MVP

AZgolfer59

New Member

shg

MrExcel MVP

AZgolfer59

New Member

shg

MrExcel MVP

Similar threads

Forum statistics

Share this page

Formula to Average lowest 10 of most recent 20 values

New Member

Excel Facts

MrExcel MVP

Active Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock