Consecutive marks

[mellorjay]

Hello, i am after a formula that will allow me to count how many cells in a row contain the same value. Basically i am wanting to make an attendance streak calculator for my classroom. This will allow me to see how many days in a row each student has been in school. Any AI response is not making it clear how i would do this so after real humans to help .

For this to work for the entire academic year i feel i will need to add a start date to work this out from.

Students are given a mark each day they are in as either '100%' or a forward slash '/' so have options in how to calculate this.

Thanks in advance

 

[FormR]

Hi, welcome to the forum! I think it would help if you were able to show us an example of your data and layout and show us what results you would expect for that sample data.

You can read about one way to do that here.

In the meantime, this might give you some ideas.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Student																			Longest Streak
2	Student 1	/	/	/	/			/	/	/				/	/	/	/	/	/	6
3	Student 2	/		/	/	/			/	/										3
4	Student 3																			0
5	Student 4			/			/	/		/	/	/	/		/	/		/	/	4
6	Student 5	/	/	/	/	/	/	/	/	/	/	/	/	/	/	/	/	/	/	18
Sheet1

Cell Formulas
Range	Formula
T2:T6	T2	=MAX(SCAN(0,B2:S2,LAMBDA(a,b,IF(b="/",a+1,a*0))))

 

[mellorjay]

Hi, welcome to the forum! I think it would help if you were able to show us an example of your data and layout and show us what results you would expect for that sample data.

You can read about one way to do that here.

In the meantime, this might give you some ideas.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Student																			Longest Streak
2	Student 1	/	/	/	/			/	/	/				/	/	/	/	/	/	6
3	Student 2	/		/	/	/			/	/										3
4	Student 3																			0
5	Student 4			/			/	/		/	/	/	/		/	/		/	/	4
6	Student 5	/	/	/	/	/	/	/	/	/	/	/	/	/	/	/	/	/	/	18
Sheet1

Cell Formulas
Range	Formula
T2:T6	T2	=MAX(SCAN(0,B2:S2,LAMBDA(a,b,IF(b="/",a+1,a*0))))

Hi thanks for this it looks perfect. I have tried to copy your formula and i cannot get it to work for some reason. i have never come across the LAMBDA function before what is this?

 

[FormR]

cannot get it to work

What happens when you try? An error when trying to commit the formula? (if so, what's the error). It's returning an error? (if so, again what error?), it's returning a wrong result? Something else?

i have never come across the LAMBDA function before what is this?

See here and here.

 

[Special-K99]

Is this any good? This seems to work on a small piece of test data, count the maximum consecutive non blanks from columns B to S.
Something I found online years ago.
NOTE: This needs to be an array formula

=MAX(FREQUENCY(IF(B1:S1<>"",COLUMN(B1:S1)),IF(B1:S1="",COLUMN(B1:S1))))

 

[mellorjay]

What happens when you try? An error when trying to commit the formula? (if so, what's the error). It's returning an error? (if so, again what error?), it's returning a wrong result? Something else?


See here and here.

Thanks i am not sure why, i have closed and opened Excel and has worked thanks!