Howdy,
I have a range of single column data, and I need a formula that will calculate the average of only the last four entries that also ignores zero's and blank cells. Please see below and attachment for additional information:
Example (four blank cells in the range below the zero's):
2
9
3
4
7
8
3
0
0
0
0
If this is my data set, I need a formula to calculate the average of 4, 7, 8, and 3 only. The idea here is that when that first zero underneath the 3 becomes a number, lets say a 10- that the formula will then return the average for: 10,3, 8, 7.
Here is where I've gotten with my formula: =((SUM(A1:A25))/(OFFSET(A1,COUNTIF(A1:A25,">0")-4,0,4,1)))
Bookmarks