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 ?

 

[akardar1]

Worth of a Nobel Peace Prize

 

[Sulprobil]

I suggest to be cautious with such formulas.

If you need some sort of "memory" in your spreadsheet, you can insert date and time stamps (for creation and/or updates) into your rows.

Any implicit storage of previous spreeadsheet states is prone to errors because you cannot verify the date/time of its previous status.

 

[footoo]

I suggest to be cautious with such formulas.

Why? What specific part(s) of the 2 formulas suggested?

 

[Sulprobil]

Whenever I see a "special" complexity like
INDIRECT(),
MATCH(9.99E+300, or
LOOKUP(2,1/,
I suggest to first look for a "general" simplification of the overall spreadsheet design which would allow for simpler formulas.
In other words: If you use INDIRECT, something is wrong with your spreadsheet design.
In this case I would like to ask the OP what his overall process looks like (all data from input via calculation to output) and why he has blanks in his data.

 

[footoo]

Whenever I see a "special" complexity like
INDIRECT(),
MATCH(9.99E+300, or
LOOKUP(2,1/,
I suggest to first look for a "general" simplification of the overall spreadsheet design which would allow for simpler formulas.
In other words: If you use INDIRECT, something is wrong with your spreadsheet design.
In this case I would like to ask the OP what his overall process looks like (all data from input via calculation to output) and why he has blanks in his data.

"Special" complexity? What problems do you think might arise?
In any event, the OP is looking for a formula in one cell.
I think you're over-complicating it.

Are saying that you never use INDIRECT, MATCH(9.99E+300, or LOOKUP(2,1/, ?
These can all be useful in the appropriate circumstances.
For example, what suggestion do you have for finding the last row number containing data, without VBA?

"In other words: If you use INDIRECT, something is wrong with your spreadsheet design."
That's a very sweeping assertion.
So you never use INDIRECT, even if it's the simplest way?

 

[Sulprobil]

Not might, almost certainly will arise:
Just let the OP ask another 5 questions for his same sheet here which will be answered with "peep hole" optimizations (or single cell formula tricks, you name it), and his sheet will barely become manageable by him and never be understood by anybody else.

Yes, I never use INDIRECT, MATCH(9.99E+300, or LOOKUP(2,1/. I do not need to look for the last row. Just arrange the column totals at the top and let the table grow downwards, for example, or use an intelligent table - that's what they are for.

INDIRECT is never the simplest way.

In this thread the most recent records can maybe be sorted to the top as well, and then a simple COUNT(A2:A32) might suffice. But even that is questionable. As said, I would like to know why and how blanks show up in column A.

 

[footoo]

Sulprobil
I find it's best not to take such a dogmatic approach to Excel.
For the functions you don't like, I have been using them for many years without problems.
There can, of course, be performance problems with large, complicated worksheets but that's not a reason to avoid them completely.
Presumably you avoid all volatile functions for the same reason?

"INDIRECT is never the simplest way." - The word "never" is such an absolute qualifier and I cannot agree. Available on the internet there are many useful, simple ways of using INDIRECT.

For this thread you suggest that records can maybe be sorted and then COUNT might suffice. I don't really see how this would be simpler than just putting one of the suggested formulas in A1.

This is my last posting to this thread.

 

[Sulprobil]

...Presumably you avoid all volatile functions for the same reason?
...
This is my last posting to this thread.

Please do not state a question if you are not interested in any further conversation. I find it's best not to take such a dogmatic approach to communication.