How to calculate a moving count?

[JenniferMurphy]

As part of my moving sums and averages project described here:

Is Take() the best way to calculate a moving sum?

About a week ago, Cubist introduced me to the Take function. I have made great use of it. I just had a situation where I needed to calculate a moving average. The Take function seems like the perfect solution, but I would appreciate any comments. Here are several examples. The mini-sheet is...

www.mrexcel.com

I also need to calculate a moving count of cells containing numbers. My first try was with CountIfs, but I discovered here that CountIfs cannot be used with IsNumber and to use SumProduct:

Countifs & Isnumber combo

Hello, I am trying to use ISNUMBER in a countifs formula and it's not working so appreciate if someone can help me out. It doesn't return an error but it's not counting the numeric values in column AM. I had this formula...

www.mrexcel.com

I tried using SumProduct, as shown in the Count column in the minisheet below, but it isn't working. I can get what I want with two columns, IsNumber and Countx, but would prefer a single column. Is there a formula for the Count column that will get the result in the Countx column without needing the IsNumber column?

Moving Totals.xlsx
	B	C	D	E	F
2		5	Number of rows
3
4	#	Value	IsNumber	Countx	Count
5	1	5	1	1	0
6	2	8	1	2	0
7	3		0	2	0
8	4	2	1	3	0
9	5	6	1	4	0
10	6		0	3	0
11	7	9	1	3	0
12	8	3	1	4	0
13	9		0	3	0
14	10	4	1	3	0
Sheet2

Cell Formulas
Range	Formula
D5:D14	D5	=ISNUMBER([@Value])+0
E5:E14	E5	=SUM(TAKE(Table2[[#Headers],[IsNumber]]:[@IsNumber],-NumRows))
F5:F14	F5	=SUMPRODUCT(ISNUMBER(TAKE(Table2[[#Headers],[Value]]:[@Value],-NumRows)))
B5:B14	B5	=ROW([@['#]])-ROW(Table2[[#Headers],['#]])

Named Ranges
Name	Refers To	Cells
NumRows	=Sheet2!$C$2	E5:F14

 

[RoryA]

You could do something like this to only count numbers:

=LET(mnum,MIN(NumRows,ROW()-ROW(Table1[#Headers])),mnum-COUNT(TAKE(Table1[[#Headers],[Time]]:[@Time],-mnum)))

 

[JenniferMurphy]

You could do something like this to only count numbers:

=LET(mnum,MIN(NumRows,ROW()-ROW(Table1[#Headers])),mnum-COUNT(TAKE(Table1[[#Headers],[Time]]:[@Time],-mnum)))

I like it. And it works! I wish I could mark more than 1 reply as a solution.

I can't decide between this solution using Let and one using Offset. Any comments other than the volatility of Offset? To me the Offset solution is a little simpler.

Moving Totals.xlsx
	C	D	E	F	G	H	I	J
5		5	Number of rows
6	Date	Time	Value	Sum	Average	#Rows	#Missed (Let)	#Missed (Offset)
7	1/01/24	2:51p	2	2	2.00	1	0	0
8	1/02/24		4	6	3.00	2	1	1
9	1/03/24	10:25a	3	9	3.00	3	1	1
10	1/04/24		5	14	3.50	4	2	2
11	1/05/24	8:15a	1	15	3.00	5	2	2
12	1/06/24	10:32a	0	13	2.60	6	2	2
13	1/07/24	9:46a	2	11	2.20	7	1	1
14	1/08/24		7	15	3.00	8	2	2
15	1/09/24	9:29a	3	13	2.60	9	1	1
16	1/10/24	4:24p	2	14	2.80	10	1	1
Sheet1

Cell Formulas
Range	Formula
F7:F16	F7	=SUM(TAKE(Table1[[#Headers],[Value]]:[@Value],-NumRows))
G7:G16	G7	=AVERAGE(TAKE(Table1[[#Headers],[Value]]:[@Value],-NumRows))
H7:H16	H7	=ROW()-ROW(Table1[[#Headers],['#Rows]])
I7:I16	I7	=LET(mnum, MIN(NumRows,ROW()-ROW(Table1[#Headers])), mnum-COUNT(TAKE(Table1[[#Headers],[Time]]:[@Time],-mnum)))
J7:J16	J7	=SUM(1-ISNUMBER(TAKE(OFFSET(Table1[[#Headers],[Time]],1,0):[@Time],-NumRows)))

Named Ranges
Name	Refers To	Cells
NumRows	=Sheet1!$D$5	I7:J16, F7:G16

 

[KRice]

You might also have a look at mine using DROP…posted at the same time as Rory’s last.

 

[JenniferMurphy]

I see...maybe you do want ISNUMBER then, as you won't necessarily know whether apparent blanks are true blanks, or spaces, or something that you can't see. You could revert to the ISNUMBER formula, and because you have an official table, you'd like to exclude the first header row from consideration. An easy option to do that is to wrap the range selection in DROP, and then drop the first row...and then look back NumRows for the values to consider:

=SUM(--ISNUMBER(TAKE(DROP(Table2[[#Headers],[Value]]:[@Value],1),-NumRows)))

I thought I looked at this option and concluded that it would sometimes drop a non-header row. Now I see that it works. Thanks. Now I think I like this solution best.

And it handles random text as well. Thank you!

Moving Totals.xlsx
	C	D	E	F	G	H	I	J	K
5		3	Number of rows
6	Date	Time	Value	Sum	Average	#Rows	#Blank (Drop)	#Blank (Let)	#Blank (Offset)
7	1/01/24	2:51p	2	2	2.00	1	0	0	0
8	1/02/24		4	6	3.00	2	1	1	1
9	1/03/24	10:25a	3	9	3.00	3	1	1	1
10	1/04/24	xx	5	12	4.00	4	2	2	2
11	1/05/24	8:15a	1	9	3.00	5	1	1	1
12	1/06/24	10:32a	0	6	2.00	6	1	1	1
13	1/07/24	9:46a	2	3	1.00	7	0	0	0
14	1/08/24		7	9	3.00	8	1	1	1
15	1/09/24	9:29a	3	12	4.00	9	1	1	1
16	1/10/24	4:24p	2	12	4.00	10	1	1	1
Sheet1

Cell Formulas
Range	Formula
F7:F16	F7	=SUM(TAKE(Table1[[#Headers],[Value]]:[@Value],-NumRows))
G7:G16	G7	=AVERAGE(TAKE(Table1[[#Headers],[Value]]:[@Value],-NumRows))
H7:H16	H7	=ROW()-ROW(Table1[[#Headers],['#Rows]])
I7:I16	I7	=SUM(1-ISNUMBER(TAKE(DROP(Table1[[#Headers],[Time]]:[@Time],1),-NumRows)))
J7:J16	J7	=LET(mnum, MIN(NumRows,ROW()-ROW(Table1[#Headers])), mnum-COUNT(TAKE(Table1[[#Headers],[Time]]:[@Time],-mnum)))
K7:K16	K7	=SUM(1-ISNUMBER(TAKE(OFFSET(Table1[[#Headers],[Time]],1,0):[@Time],-NumRows)))

Named Ranges
Name	Refers To	Cells
NumRows	=Sheet1!$D$5	F7:G16, I7:K16

 

[KRice]

thought I looked at this option and concluded that it would sometimes drop a non-header row.

The way the range is formed, it always begins with the header row down to your current row…and we drop only the first row, so that shouldn’t cause any problems.

 

[JenniferMurphy]

The way the range is formed, it always begins with the header row down to your current row…and we drop only the first row, so that shouldn’t cause any problems.

Yes, I see that now. I don't know now what I thought would go wrong.

Here is my more complete table comparing the three solutions. I added columns to show how many rows would be included in the tallies.

Moving Totals.xlsx
	C	D	E	F	G	H	I	J	K	L	M
3		7	Number of rows
4	Date	Time	Value	Sum	Average	#Rows (Drop)	#Rows (Let)	#Rows (Offset)	#Blank (Drop)	#Blank (Let)	#Blank (Offset)
5	1/01/24	2:51p	2	2	2.00	1	1	1	0	0	0
6	1/02/24		4	6	3.00	2	2	2	1	1	1
7	1/03/24	10:25a	3	9	3.00	3	3	3	1	1	1
8	1/04/24	xx	5	14	3.50	4	4	4	2	2	2
9	1/05/24	8:15a	1	15	3.00	5	5	5	2	2	2
10	1/06/24	10:32a	0	15	2.50	6	6	6	2	2	2
11	1/07/24	9:46a	2	17	2.43	7	7	7	2	2	2
12	1/08/24		7	22	3.14	7	7	7	3	3	3
13	1/09/24	9:29a	3	21	3.00	7	7	7	2	2	2
14	1/10/24	4:24p	2	20	2.86	7	7	7	2	2	2
Sheet1

Cell Formulas
Range	Formula
F5:F14	F5	=SUM(TAKE(Table1[[#Headers],[Value]]:[@Value],-NumRows))
G5:G14	G5	=AVERAGE(TAKE(Table1[[#Headers],[Value]]:[@Value],-NumRows))
H5:H14	H5	=ROWS(TAKE(DROP(Table1[[#Headers],[Time]]:[@Time],1),-NumRows))
I5:I14	I5	=MIN(NumRows,ROW()-ROW(Table1[#Headers]))
J5:J14	J5	=ROWS(TAKE(OFFSET(Table1[[#Headers],[Time]],1,0):[@Time],-NumRows))
K5:K14	K5	=SUM(1-ISNUMBER(TAKE(DROP(Table1[[#Headers],[Time]]:[@Time],1),-NumRows)))
L5:L14	L5	=LET(mnum, MIN(NumRows,ROW()-ROW(Table1[#Headers])), mnum-COUNT(TAKE(Table1[[#Headers],[Time]]:[@Time],-mnum)))
M5:M14	M5	=SUM(1-ISNUMBER(TAKE(OFFSET(Table1[[#Headers],[Time]],1,0):[@Time],-NumRows)))

Named Ranges
Name	Refers To	Cells
NumRows	=Sheet1!$D$3	F5:M14