Find the lowest4 numbers of the last 6 in a range

[Pjmcc64]

Is there a formula that will look at the last 6 non blank number values in a
range, and return the average of the four lowest? The numbers are in a row
that is 16 columns and the numbers are added per division weekly. Looks like
this:

45 43 44 45 51 58 38 49 52 38 60 59 41 65 50

Where the large spaces are blank cells, in this example I am looking for the
average of 59, 41, 38, 50. Ideas?

 

[T. Valko]

Will there *always* be at least 6 numbers in the range? If not, then what
should happen?

 

[Pjmcc64]

Not always 6, but there will be at least 4. Once we get to 6, I only want to
take the average of 4. Basically, the 4 lowest of the most recent 6.

 

[Domenic]

Assuming that A22 contains the data, try the following formulas that
need to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(SMALL(INDEX(A22,LARGE(IF(A22<>"",COLUMN(A22)-COLUMN(A2)+1)
,6))2,{1,2,3,4}))

or

=IF(COUNT(A22)>=6,AVERAGE(SMALL(INDEX(A22,LARGE(IF(A22<>"",COLUMN(A
22)-COLUMN(A2)+1),6))2,{1,2,3,4})),"Less than 6 numbers")

 

[Harlan Grove]

=AVERAGE(SMALL(INDEX(A22,LARGE(IF(A22<>"",
COLUMN(A22)-COLUMN(A2)+1),6))2,{1,2,3,4}))

....

You could shorten that to

=AVERAGE(SMALL(INDEX(2:2,
LARGE(COLUMN(A22)*ISNUMBER(A22),6))2,{1;2;3;4}))