Rolling average with blanks in raw data

[akardar1]

Hi Excel Team,

In Column A I have values like this from row 5 to 35 for instance:

5
6
blank
7
8
blank
blank
2

I would like to count the number of non-blank value and return that in A1... so I use =COUNT(A5:A35)
but the next day, i have a new value in cell A36, so now i would like A1 to count A6:A36..always counting back 30.. from the last value in column A
How do i amend my excel A1 formula ?

 

[footoo]

=COUNT(OFFSET(A1,COUNT(A:A),0,-30))

Note : you can't put in A1

 

[footoo]

To put in A1, something like :
=COUNT(OFFSET(A1,COUNT(A2:A10000),0,-30))

 

[akardar1]

Thank you !!

 

[footoo]

Thank you !!

I don't think it does what you want.
Will look at again tomorrow.

 

[footoo]

Try this :

=COUNT(OFFSET(A1,LOOKUP(2,1/(A2:A10000<>""),ROW(A2:A10000))-1,0,-30))

 

[Osvaldo Palmeiro]

... i would like A1 to count A6:A36..always counting back 30..

So, you want to count 31 cells, is it" ?

=COUNT(OFFSET(INDIRECT("A"&MATCH(9.99E+307,A:A)),,,-31))

 

[footoo]

So, you want to count 31 cells, is it" ?

=COUNT(OFFSET(INDIRECT("A"&MATCH(9.99E+307,A:A)),,,-31))

That's a much neater formula, but the OP wants it in A1.
So :
=COUNT(OFFSET(INDIRECT("A"&MATCH(9.99E+307,A2:A10000))-1,,,-31))

 

[Osvaldo Palmeiro]

... but the OP wants it in A1.

Yes, you can place it in any cell out of counting range, including A1.

 

[footoo]

Correction :
=COUNT(OFFSET(INDIRECT("A"&MATCH(9.99E+307,A2:A10000)-1),,,-31))