Hi,

Let's say your data are in the range B1 - B1000. This will give the average
of the bottom 4 numeric cells:-

=AVERAGE(IF(ROW(B1:B1000)=LARGE(IF(ISNUMBER(B1:B1 000),IF(B1:B10000,ROW(B1:B1000))),MIN(4,COUNTIF(B 1:B1000,"0"))),IF(B1:B10000,B1:B1000)))

It's an array so enter with Ctrl+Shift+Enter

Mike

"dartanion" wrote:

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