Find the lowest4 numbers of the last 6 in a range

P

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

T. Valko

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

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.
 
D

Domenic

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

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

or

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

Harlan Grove

Domenic said:
=AVERAGE(SMALL(INDEX(A2:p2,LARGE(IF(A2:p2<>"",
COLUMN(A2:p2)-COLUMN(A2)+1),6)):p2,{1,2,3,4}))
....

You could shorten that to

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top