Try this array formula** :

=AVERAGE(C42:INDEX(C2:C42,LARGE(IF(ISNUMBER(C2:C42 ),ROW(C2:C42)-MIN(ROW(C2:C42))+1),5)))

If there are less than 5 numbers the formula will return a #NUM! error.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"kjal" wrote in message
...
Maybe I am not explaining well or maybe it is not possible to average the
last 5 or whatever numbers of a list of numbers that has spaces but no
zeros.
The range in the avg formula will be c2:c42, and the numbers will
ultimatley
fill 3/4 of the fields with random spaces for the balance, So the formula,
hopefully, will avg the last 5 NUMBERS in the row, ignoring the spaces. So
the numbers in the avg of the last 5 may be spread over 7 or 8 spaces.

"Pete_UK" wrote:

But if you are putting numbers into random cells in the range, how
would you know which were the last 5 or last 10 entered? Do you not
mean last by date of entry, but rather last in the sequence of data?

Pete

On Sep 25, 9:32 pm, kjal wrote:
Hey there. If there are only 5 numbers at some point, the 5 and 10
would be
the same. I hope that helps. Basically, over the course of 3 or so
months,
these numbers will begin to be added to the rows and by the end, there
will
be somewhere around 30 numbers out of 40 potential leaving 10 random
spaces
in each column assuming 40 max. So the last 5 and last 10 averages will
develop and deviate as more data is entered.

Thanks



"T. Valko" wrote:
What should happen if there aren't 5 or 10 numbers to average?

--
Biff
Microsoft Excel MVP

"kjal" wrote in message
...
Hello, I need to design a spreadsheet that averages the last 5 and
last 10
numbers in a row of up to 40 numbers. There will be various random
spaces
between the entered numbers. So for example, if there are 15
numbers
spread
over 40 rows, I would need to average the last 5 which could each
have a
few
spaces between them. There will be no 0's or negatives in the data.

Thanks- Hide quoted text -

- Show quoted text -