Rolling average with blanks in raw data

akardar1

New Member
Joined
Dec 22, 2010
Messages
23
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 ?
 

Some videos you may like

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
Joined
May 12, 2020
Messages
147
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.
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
147
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
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 12, 2020
Messages
147
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
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 12, 2020
Messages
147
...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. ;)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top