Average of Lowest Four Values from the Last Six Values, excluding Blanks

[rphill48]

Microsoft Excel 365
Windows

I want to create a formula I can drag down as I add values. I'm looking to calculate the average of the four lowest values at out at least the last four values but not more than the last six, excluding blanks. See a screen cap below with examples.

Red Group - shows that only four values were available so the average was calculated for those four values.
Purple Group - shows there are blank values, so the list expanded to capture the last six, excluding blanks.

Thank you!

 

[Eric W]

Welcome to the MrExcel forum!

There's got to be something simpler than this, but try:

Book1
	A	B	C
21	Week 1	30
22	Week 2	32
23	Week 3	34
24	Week 4	36	33
25	Week 5	38	33
26	Week 6	40	33
27	Week 7	42	35
28	Week 8		35
29	Week 9	42	37
30	Week 10	40	38.5
31	Week 11		38.5
32	Week 12		38.5
33	Week 13	38	39
34	Week 14	36	38.5
35	Week 15	34	37
36	Week 16	32	35
37	Week 17	30	33
38	Week 18	28	31
39	Week 19	26	29
40
Sheet16

Cell Formulas
Range	Formula
C24:C39	C24	=AVERAGE(SMALL(INDEX(SORT(FILTER(CHOOSE({1,2},$B$21:$B24,ROW($B$21:$B24)),$B$21:$B24<>""),2,-1),SEQUENCE(MIN(6,COUNT($B$21:$B24)))),{1,2,3,4}))

 

[Eric W]

A somewhat shorter version:

=AVERAGE(AGGREGATE(15,6,INDEX(FILTER($B$21:$B24,$B$21:$B24<>""),1/(1/(SEQUENCE(6,,COUNT($B$21:$B24),-1)))),{1,2,3,4}))

 

[rphill48]

Worked like a charm! Thanks Eric!

 

[Eric W]

Happy to help! Thanks for the update. ?

 

[TenFour]

I'm looking for the same thing but in the horizontal fashion. I tried editing your formula for it but I had no success.
The numbers on the side/blue which correlate with each other are the numbers that I'd like to be averaged together. If it doesn't work horizontally. I'd could use a formula that recalls the previous 6 to an updating table as show but I need to figure out that as well.

Please help!
Thank you!

 

[Eric W]

Welcome to the MrExcel forum!

I can see why you had trouble with it. I can barely remember how it works. Give this a shot:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1														1st	2nd	3rd	4th	5th	6th		Average	Average
2		4	5	9	7	6		2	2	4	3			7	6	2	2	4	3		4	4
3		2	5	6		9	4	1	3					5	6	9	4	1	3		4.666667	4.666667
4			4	7			8	9	1	1				4	7	8	9	1	1		5	5
5		7	9		1		6		7	10	2			9	1	6	7	10	2		5.833333	5.833333
6						5		6	6	8				5	6	6	8				6.25	6.25
7			6				8			2				6	8	2					5.333333	5.333333
Sheet28

Cell Formulas
Range	Formula
U2:U7	U2	=AVERAGE(N2#)
V2:V7	V2	=AVERAGE(INDEX(FILTER(B2:L2,B2:L2<>""),SEQUENCE(,MIN(6,COUNT(B2:L2)),MAX(COUNT(B2:L2)-5,1))))
N7:P7,N6:Q6,N2:S5	N2	=INDEX(FILTER(B2:L2,B2:L2<>""),SEQUENCE(,MIN(6,COUNT(B2:L2)),MAX(COUNT(B2:L2)-5,1)))
Dynamic array formulas.

If you want the actual numbers displayed that are included, use the N2 formula. Then you can use the basic U2 formula to get the average. If you don't want the numbers listed, you can just wrap the N2 formula in AVERAGE and get your answer (V2).

Hope this helps!

 

[Peter_SSs]

Just a slight tweak to Eric's column V formula: By reversing the sequence, we could avoid the MAX calculation in there. I've also used LET to save counting twice. ?

21 08 30.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q
1													Average
2	4	5	9	7	6		2	2	4	3			4
3	2	5	6		9	4	1	3					4.666667
4		4	7			8	9	1	1				5
5	7	9		1		6		7	10	2			5.833333
6					5		6	6	8				6.25
7		6				8			2				5.333333
Average

Cell Formulas
Range	Formula
Q2:Q7	Q2	=LET(c,COUNT(E2:N2),AVERAGE(INDEX(FILTER(E2:N2,E2:N2<>""),SEQUENCE(MIN(6,c),,c,-1))))

 

[Eric W]

If you don't need to see the values used, you can get the formula a tad shorter with a different approach:

=AVERAGE(INDEX(2:2,LARGE(COLUMN(B2:L2)*(B2:L2<>""),MIN(6,COUNT(B2:L2)))):L2)