Averaging List with Blank Cells

  • Thread starter Thread starter Frank West
  • Start date Start date
F

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
 
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
 
Back
Top