Averaging List with Blank Cells

[Frank West]

Hi,

Someone gave me this formula to average the most recent five items in
a column of numbers:

=AVERAGE(OFFSET(B2,COUNTA(A:A)-5,0,5,1))

It works great unless the list has blank cells. In that case it does
not work.

1. 6
2.
3. 7
4. 9
5.
6. 2
7.
8. 1
9. 23
10 4

How can I make it work when the columns have some blank cells?

Thanks,

Frank

 

[dave]

Try =IF(count(A1:A10)=0,",Average(A1:A10))

 

[Frank West]

Hi, thanks for the response. Your formula doesn't work because if the
cell is blank it then counts that cell anyway. I want to ignore cells
that are blank.

1. 4
2.
3. 5
4. 6
5.
6. 4

If I have the formula set up to average the last three data points I
would get an answer of 5. This is what I want.

1. 4
2.
3. 5
4. 6
5.
6. 4
7. 2

Now, on the next day when I input new data, the average of the last
three data points would be 4.

Thanks,

Frank