Averaging

[Corkster]

How do I create a formula that takes the lowest 4 numbers from the last 7 columns and creates their average?

 

[T. Valko]

How do I create a formula that takes the lowest 4 numbers from the last 7 columns and creates their average?

Will there always be at 7 numbers?

Are there any empty cells within the range that need to be accounted for?

 

[Corkster]

Will there always be at 7 numbers?

Are there any empty cells within the range that need to be accounted for?

Yess there will always be at least 7 numbers and in some cases up to 20 numbers entered. I only want to examine the last 7 entries of actual numbers and not empty cells in the range as there will be some of those.

 

[Haldoug]

So the question is how do I average the lowest for numbers from the last seven numbers enters, which could be the last 7 or more columns depending on blank cells in some of the columns.

Am I understanding this correctly?

The small function

(=small(array,1) + small(array,2 + small(array,3) +small(array,4)) / 4)

is what you are looking for, but the array definition is creating a problem.

 

[Haldoug]

Sorry, left of a )
(=small(array,1) + small(array,2) + small(array,3) +small(array,4)) / 4)

 

[T. Valko]

Yess there will always be at least 7 numbers and in some cases up to 20 numbers entered. I only want to examine the last 7 entries of actual numbers and not empty cells in the range as there will be some of those.

Try this...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	AVG	_	_	_	_	_	_	_	_	_	_	_	_	_	_	_	_	_	_	_
2	19.5	91	13	38	56	20	38	12	_	_	_	25	31	_	_	11	52	_	30	83
Sheet1

This array formula entered in A2:

=AVERAGE(SMALL(INDEX(B2:T2,LARGE((COLUMN(B2:T2)-COLUMN(B2)+1)*ISNUMBER(B2:T2),7)):T2,{1;2;3;4}))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Corkster]

Thanks T. Valko. I really appreciate the help.

 

[T. Valko]

Thanks T. Valko. I really appreciate the help.

You're welcome. Thanks for the feedback!