# Rolling average with blanks in raw data

#### akardar1

##### New Member
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

##### New Member
Worth of a Nobel Peace Prize

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Sulprobil

##### Board Regular
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

##### Well-known Member
I suggest to be cautious with such formulas.
Why? What specific part(s) of the 2 formulas suggested?

#### Sulprobil

##### Board Regular
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

##### Well-known Member

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

##### Board Regular
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

##### Well-known Member
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

##### Board Regular
...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.

Replies
5
Views
243
Replies
3
Views
59
Replies
4
Views
197
Replies
3
Views
57
Replies
3
Views
73

1,127,316
Messages
5,623,968
Members
416,002
Latest member
Neshx

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back