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<"",COLU MN(A2:P2)-COLUMN(A2)+1)
,6)):P2,{1,2,3,4}))
or
=IF(COUNT(A2:P2)=6,AVERAGE(SMALL(INDEX(A2:P2,LARG E(IF(A2:P2<"",COLUMN(A
2:P2)-COLUMN(A2)+1),6)):P2,{1,2,3,4})),"Less than 6 numbers")
--
Domenic
http://www.xl-central.com
In article ,
Pjmcc64 wrote:
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.
"T. Valko" wrote:
Will there *always* be at least 6 numbers in the range? If not, then what
should happen?
--
Biff
Microsoft Excel MVP
"Pjmcc64" wrote in message
...
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?