I didn't think you were questioning the accuracy, Mike. It was a good
question and prompted me to tweak the formula so it avoids being tripped up
by inadvertent text in the range. Thanks.

Best Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)



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

Thank you for that. I wasn't questioning the accuracy of your reply to the
OP, I just wanted to know whether the non-array solution could cope with
text
in the range.

Mike

"Ron Coderre" wrote:

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