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?

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?

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?

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?

Domenic wrote...
....
=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}))