Simpler than mine but can it be modified to give the correct result if
there
is a non-numeric value in the range?

It depends on the user's needs. In the posted example, there were numbers
and blanks....no text. Consequently, I tailored the solution to that
scenario.

If text may sneak into the range, this adjusted version works:
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE(ISNUMBER(B1:B28)*ROW(B1:B28),4),0))),0)

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)


"Mike H" wrote in message
...
Ron,

Simpler than mine but can it be modified to give the correct result if
there
is a non-numeric value in the range? My understanding is that this formula
takes the last 4 values in a range (not necessarily the last 4 numeric
values) and averages them if they are numeric.

Mike

"Ron Coderre" wrote:

Try something like this

This regular formula returns the average of up to the last 4 numbers in
the
range B1:B28.
(If there are no numbers, it returns zero):
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*ROW(B1:B28),4),0))),0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

"dartanion" wrote in message
...
I have a spreadsheet which gives me the following information, if there
is
no
data insert 0 - This is ok and works. The next bit asks if there are
less
than 5 entries, what is the average, again this formula works ok.
The next bit completely baffles me. All I want is to start at the
bottom
of
my column, and work back up, identifying the first 4 cells with data in
them,
add them together and give me the average.
eg column b has ten empty rows, then the number 600, then 3 empty rows
followed by cells with 400, 700, a couple of blanks then 900 then six
empty
rows followed by 500, a blank and then 650. So the formula would find
650,
500,900,700, returning for me at the top of the sheet the figure
687.5 -
help
anyone