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

 

[KRice]

You can do it that way, but you'll need to convert the TRUE/FALSE values from ISNUMBER to 1's and 0's:

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

...and then, SUM is shorter.

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

 

[JenniferMurphy]

You can do it that way, but you'll need to convert the TRUE/FALSE values from ISNUMBER to 1's and 0's:

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

...and then, SUM is shorter.

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

Dang! I thought I tried that. Thanks

 

[KRice]

Happy to help.

 

[JenniferMurphy]

Hmmm... I guess I still don't get it. I added a Time column (C) with some missing times. I want to count how many missing times there are in the moving range. In the minisheet below, the Bad column (H) is off by +1 for the first n-1 values, where n is the length of the moving tally (NumRows). But the Good column (I), which is just the sum of the Not a Number column (J), is correct for all values of NumRows.

If I set NumRows to 1, everything is correct:

If I set it to 2, H7 is off by +1; the rest of H is correct.

If I set it to 3, H7 & H8 are off by +1; the rest of H is correct.

What am I doing wrong now?

Moving Totals.xlsx
	B	C	D	E	F	G	H	I	J
4			3	Number of rows
5		C	D	E	F	G	H	I	J
6		Date	Time	Value	Sum	Average	#Missing Times Bad	#Missing Times Good	Time Not a Number
7	7	1/01/24	2:51p	2	2	2.00	1	0	0
8	8	1/02/24		4	6	3.00	2	1	1
9	9	1/03/24	10:25a	3	9	3.00	1	1	0
10	10	1/04/24		5	12	4.00	2	2	1
11	11	1/05/24	8:15a	1	9	3.00	1	1	0
12	12	1/06/24	10:32a	0	6	2.00	1	1	0
13	13	1/07/24	9:46a	2	3	1.00	0	0	0
14	14	1/08/24	9:29a	7	9	3.00	0	0	0
15	15	1/09/24		3	12	4.00	1	1	1
16	16	1/10/24	4:24p	2	12	4.00	1	1	0
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	=SUM(1-ISNUMBER(TAKE(Table1[[#Headers],[Time]]:[@Time],-NumRows)))
I7:I16	I7	=SUM(TAKE(Table1[[#Headers],[Time Not a Number]]:[@[Time Not a Number]],-NumRows))
J7:J16	J7	=1-ISNUMBER([@Time])
B7:B16	B7	=ROW()

Named Ranges
Name	Refers To	Cells
NumRows	=Sheet1!$D$4	F7:I16

 

[RoryA]

Can't you just use COUNTBLANK?

=COUNTBLANK(TAKE(Table1[[#Headers],[Time]]:[@Time],-NumRows))

 

[KRice]

I agree with Rory's suggestion. It sounds as if you don't want to count numbers, but blanks. Your original formula includes the column heading, so it will be a factor until you move down the column far enough such that -NumRows no longer reaches back far enough to include it. When the header is included, ISNUMBER evaluates it as FALSE (converted to 0), just like any of the blank cells. And if you are using this formula to count blank cells, then the count will be too high by 1 in some cases due to this bad assumption that ISNUMBER=FALSE means blank (because it doesn't when the header is considered). It is better to count the blanks directly. Assuming the cells are truly blank, check whether Rory's suggestion works okay.

 

[JenniferMurphy]

Can't you just use COUNTBLANK?

=COUNTBLANK(TAKE(Table1[[#Headers],[Time]]:[@Time],-NumRows))

That seems to work. Thanks.

The reason I was counting "not numbers" is to make sure that a cell that looks empty, but contains one or more spaces, doesn't screw up the count.

 

[JenniferMurphy]

I agree with Rory's suggestion. It sounds as if you don't want to count numbers, but blanks. Your original formula includes the column heading, so it will be a factor until you move down the column far enough such that -NumRows no longer reaches back far enough to include it. When the header is included, ISNUMBER evaluates it as FALSE (converted to 0), just like any of the blank cells. And if you are using this formula to count blank cells, then the count will be too high by 1 in some cases due to this bad assumption that ISNUMBER=FALSE means blank (because it doesn't when the header is considered). It is better to count the blanks directly. Assuming the cells are truly blank, check whether Rory's suggestion works okay.

Aha! I should have thought of that. I guess I assumed that the range was starting with the first cell after the header.

Is there any way to easily specify that first cell without using Offset? As I replied to Rory, I would like to keep cells that look empty, but are not, from screwing up the count.

Thanks

 

[KRice]

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)))