Nov 18, 2020

Counting streaks and longest streaks using sheets...

So I am creating a sheet to keep track of my attendance for my virtual classroom. I am using check boxes to indicate whether the student attended the virtual class meeting (check means attended, blank means missed the class). The goal is to begin a badge system where badges get sent out to students when they have achieved a certain consecutive attendance streak. The issue I am running into is that my current streak doesn't go back to 0 when a student has a missed day. I've attached my sheet in order to see what I have, any ideas on making current streaks show 0 when a student has missed a day?

https://docs.google.com/spreadsheets/d/1AqOpZWDrg0C0SL-uhCv_Fs05b0abNbRxdYjt512YK8s/edit?usp=sharing

Details

Sheets,Microsoft Windows,Education

Locked

Informational notification.

This question is locked and replying has been disabled.

Community content may not be verified or up-to-date. Learn more.

Recommended Answer

--Hyde

Diamond Product Expert

Top Contributor since 12 January 2013

Nov 19, 2020

Hi Eddie,

I copy-pasted the two formulas to your sample spreadsheet in the new Solution sheet and they seem to work fine.

If you need more help, please show your hand-entered expected results from the data in the sample spreadsheet, and explain why they would be the correct results.

Cheers --Hyde

Original Poster Eddie Lopez 9098 marked this as an answer

Helpful?

All Replies (4)

--Hyde

Diamond Product Expert

Top Contributor since 12 January 2013

Nov 19, 2020

Hi Eddie,

Try something like this in cell B2:

=arrayformula( if( sum(D2:2 + 0), max( len( split( concatenate( left(D2:2) ), "F" ) ) ), "no streak" ) )

...and this in cell C2:

=arrayformula( if( filter( D2:2, D$1:$1 = max(D$1:$1) ), len( sortn( transpose( split( concatenate( left(D2:2) ), "F" ) ), 1, 0, array_constrain( row(A$1:A), columns( split( concatenate( left(D2:2) ), "F" ) ), 1 ), false ) ), "no streak" ) )

Then copy down the formulas.

Your sample spreadsheet is view-only. If you need more help, please share your sample spreadsheet with "can edit" rights as explained in this topic. Post back in this thread with the link.

Cheers --Hyde

Eddie Lopez 9098

Original Poster

Nov 19, 2020

Thanks for the help Hyde, it works for the "longest streak column" and it works to stop the count of the "current streak" but it won't count for a current streak now. I have changed the sharing settings on the sheets to "can edit" if that helps out a little more.

https://docs.google.com/spreadsheets/d/1AqOpZWDrg0C0SL-uhCv_Fs05b0abNbRxdYjt512YK8s/edit?usp=sharing

--Hyde

Diamond Product Expert

Top Contributor since 12 January 2013