Average the Last Five Cells in a Column

I have some numbers in a column and I want to take the average of the last
five. If my numbers are in A1:A10 I know I can use the AVERAGE function:
AVERAGE(A6:A10). But the problem is that some of the cells are empty.
What I want to do is take the average of the last five cells in a column, so
long as those cells have numbers in them. So if cell A7, for example, is
empty, I would average A5:A10. If A7 and A8 are empty, I would average
A4:A10. Is there any way that this is possible?

Here is a solution that Peo Sjoblom posted last year

=AVERAGE(A65535:INDEX(A1:A65535,SUMPRODUCT(LARGE(ROW(1:65535)*(A1:A65535<>""
),C1))))

C1 is a cell containing the number of cells to average, 5 in your case

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Warrior Princess" <[email protected]> wrote in message
news:[email protected]...
> I have some numbers in a column and I want to take the average of the last
> five. If my numbers are in A1:A10 I know I can use the AVERAGE function:
> AVERAGE(A6:A10). But the problem is that some of the cells are empty.
> What I want to do is take the average of the last five cells in a column,
so
> long as those cells have numbers in them. So if cell A7, for example, is
> empty, I would average A5:A10. If A7 and A8 are empty, I would average
> A4:A10. Is there any way that this is possible?
>
>

Thank you!

"Bob Phillips" <[email protected]> wrote in message
news:%235Lxq%[email protected]...
> Here is a solution that Peo Sjoblom posted last year
>
>
=AVERAGE(A65535:INDEX(A1:A65535,SUMPRODUCT(LARGE(ROW(1:65535)*(A1:A65535<>""
> ),C1))))
>
> C1 is a cell containing the number of cells to average, 5 in your case
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Warrior Princess" <[email protected]> wrote in message
> news:[email protected]...
> > I have some numbers in a column and I want to take the average of the
last
> > five. If my numbers are in A1:A10 I know I can use the AVERAGE function:
> > AVERAGE(A6:A10). But the problem is that some of the cells are empty.
> > What I want to do is take the average of the last five cells in a
column,
> so
> > long as those cells have numbers in them. So if cell A7, for example, is
> > empty, I would average A5:A10. If A7 and A8 are empty, I would average
> > A4:A10. Is there any way that this is possible?
> >
> >
>
>

One way:

=AVERAGE(OFFSET(A10,,,LARGE(IF(A1:A10<>"",ROW(1:10)),5)-
ROWS(A1:A10)-1))

While it works fine, the only downside is that OFFSET is
a volatile function. INDEX should be the preferred
function.

HTH
Jason
Atlanta, GA


>-----Original Message-----
>I have some numbers in a column and I want to take the
average of the last
>five. If my numbers are in A1:A10 I know I can use the
AVERAGE function:
>AVERAGE(A6:A10). But the problem is that some of the
cells are empty.
>What I want to do is take the average of the last five
cells in a column, so
>long as those cells have numbers in them. So if cell A7,
for example, is
>empty, I would average A5:A10. If A7 and A8 are empty, I
would average
>A4:A10. Is there any way that this is possible?
>
>
>.
>