How do I add the last consecutive cells starting from right to left and also reset and/or skip particular cells?

[mrjoeybrown]

So I have created this awesome spreadsheet (in my opinion) as a school admin but I have at least one thing that has been bugging me for close to 3 months. Here is the data:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]last name[/TD]
[TD]first name[/TD]
[TD]consecutive days[/TD]
[TD]days absent[/TD]
[TD]8/30[/TD]
[TD]9/1[/TD]
[TD]9/4[/TD]
[TD]9/5[/TD]
[TD]9/6[/TD]
[TD]9/7[/TD]
[/TR]
[TR]
[TD]#1[/TD]
[TD]student[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes L4[/TD]
[TD]Yes L4[/TD]
[TD][/TD]
[TD]Yes L4[/TD]
[TD]Yes L4[/TD]
[TD]Yes L4[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[TD]student[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes L1[/TD]
[TD]Yes L1[/TD]
[TD][/TD]
[TD]Yes L1[/TD]
[TD]ISS[/TD]
[TD]Yes L1[/TD]
[/TR]
[TR]
[TD]#3[/TD]
[TD]student[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes L1[/TD]
[TD]ABS[/TD]
[TD]ABS[/TD]
[/TR]
[TR]
[TD]#4[/TD]
[TD]student[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes L3[/TD]
[TD]Yes L4[/TD]
[TD][/TD]
[TD]Yes L4[/TD]
[TD]Yes L4[/TD]
[TD]Yes L4[/TD]
[/TR]
[TR]
[TD]#5[/TD]
[TD]student[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes L3[/TD]
[TD]OSS[/TD]
[TD][/TD]
[TD]Yes L3[/TD]
[TD]Yes L3[/TD]
[TD]Yes L3[/TD]
[/TR]
</tbody>[/TABLE]


I need a counting cell called "CONSECUTIVE DAYS" with a formula to count from right to left the consecutive days a cell has the word "Yes". BUT...If the cell contains an "ISS" or "OSS" then the counting cell needs to reset to "0". If the cell contains an "ABS" or is blank, then it needs to simply skip that cell and keep counting. ALSO, if there is a level change, from say "Yes L4" to a "Yes L3"(reading right to left), then the counting cell needs to reset to "0".'

The previous formula filled in with the appropriate totals for "CONSECUTIVE DAYS":
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]last name[/TD]
[TD]first name[/TD]
[TD]consecutive days[/TD]
[TD]days absent[/TD]
[TD]8/30[/TD]
[TD]9/1[/TD]
[TD]9/4[/TD]
[TD]9/5[/TD]
[TD]9/6[/TD]
[TD]9/7[/TD]
[/TR]
[TR]
[TD]#1[/TD]
[TD]student[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]Yes L4[/TD]
[TD]Yes L4[/TD]
[TD][/TD]
[TD]Yes L4[/TD]
[TD]Yes L4[/TD]
[TD]Yes L4[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[TD]student[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]Yes L1[/TD]
[TD]Yes L1[/TD]
[TD][/TD]
[TD]Yes L1[/TD]
[TD]ISS[/TD]
[TD]Yes L1[/TD]
[/TR]
[TR]
[TD]#3[/TD]
[TD]student[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes L1[/TD]
[TD]ABS[/TD]
[TD]ABS[/TD]
[/TR]
[TR]
[TD]#4[/TD]
[TD]student[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]Yes L3[/TD]
[TD]Yes L4[/TD]
[TD][/TD]
[TD]Yes L4[/TD]
[TD]Yes L4[/TD]
[TD]Yes L4[/TD]
[/TR]
[TR]
[TD]#5[/TD]
[TD]student[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]Yes L3[/TD]
[TD]OSS[/TD]
[TD][/TD]
[TD]Yes L3[/TD]
[TD]Yes L3[/TD]
[TD]Yes L3

[/TD]
[/TR]
</tbody>[/TABLE]
And in final explanation, I do not need to keep up with that number once it has been counted. I just need my teachers to be able to refer to that column on THAT day they are looking at the table.

 

[Eric W]

Welcome to the MrExcel board.

You're asking for a lot from a formula! Still, here's my shot at it:

	A	B	C	D	E	F	G	H	I	J	K	L

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]last name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]first name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]consecutive days[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]days absent[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]30-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1-Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4-Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5-Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6-Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7-Sep[/TD]
[TD="align: right"]8-Sep[/TD]
[TD="align: right"]9-Sep[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]student[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]student[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]ISS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]student[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]ABS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]ABS[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]student[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]student[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]OSS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=1/LOOKUP(2,1/FREQUENCY(IF(ISNUMBER(SEARCH(MID(LOOKUP(2,1/ISNUMBER(SEARCH("L",E2:L2)),E2:L2),5,5),E2:L2)),COLUMN(E2:L2)),IF(E2:L2<>"",IF(ISNUMBER(SEARCH("SS",E2:L2)),COLUMN(E2:L2)))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Even so, I made a few assumptions. First, I'm not looking for "yes", I'm looking for an "L", which appears in every cell that "yes" is in. Next, I'm lumping OSS and ISS together by looking for "SS", since "SS" does not appear in any of your other cells. Finally, the L1 always starts in position 5 in the cell. The formula can be adapted if these assumptions aren't always true.

Finally, if you don't want the formula to be "live", we can write a macro to count the numbers on demand which might be easier.

Let me know what you think.

 

[mrjoeybrown]

Well first of all... YOU are the man! or the woman! or heck...I'm so excited...you are the PERSON! You have taken a bit of a weight off of my shoulders. I've been trying things for weeks now.

With that out of the way, there are a few instances where it is not returning the correct count. I've tried to paste a few things from my original file:

=1/LOOKUP(2,1/FREQUENCY(IF(ISNUMBER(SEARCH(MID(LOOKUP(2,1/ISNUMBER(SEARCH("L",Table22[@[8/7]:[12/22]])),Table22[@[8/7]:[12/22]]),5,5),Table22[@[8/7]:[12/22]])),COLUMN(Table22[@[8/7]:[12/22]])),IF(Table22[@[8/7]:[12/22]]<>"",IF(OR(ISNUMBER(SEARCH("SS",Table22[@[8/7]:[12/22]])),ISNUMBER(SEARCH("NO",Table22[@[8/7]:[12/22]]))),COLUMN(Table22[@[8/7]:[12/22]])))))

**This is the formula that I used to produce the 1st row under "CONSECUTIVE DAYS" and then I copied it down. It didn't calculate correctly in A2, A6, A8, and a few others. If it would be helpful for me to share the entire spreadsheet I would be willing. I am new to forums and so my "pasting" is pretty rough. Also, if you notice, I added a "NO" in your search. I had forgotten that this was an option as well.

---

[TABLE="width: 180"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]CONSECUTIVE DAYS[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Current
Level[/TD]
[/TR]
[TR]
[TD="align: center"]1*[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]GREEN[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]GREEN[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]PURPLE[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]PURPLE[/TD]
[/TR]
[TR]
[TD="align: center"]1*[/TD]
[TD="align: center"][/TD]
[TD="align: center"]PURPLE[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]GREEN[/TD]
[/TR]
[TR]
[TD="align: center"]1*[/TD]
[TD="align: center"][/TD]
[TD="align: center"]BLUE[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]PURPLE[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]BLUE[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]PURPLE[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 778"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col span="3" style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col span="3" style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]9/7[/TD]
[TD="align: center"]9/8[/TD]
[TD="align: center"]9/11[/TD]
[TD="align: center"]9/12[/TD]
[TD="align: center"]9/13[/TD]
[TD="align: center"]9/14[/TD]
[TD="align: center"]9/15[/TD]
[TD="align: center"]9/18[/TD]
[TD="align: center"]9/19[/TD]
[TD="align: center"]9/20[/TD]
[TD="align: center"]9/21[/TD]
[TD="align: center"]9/22[/TD]
[/TR]
[TR]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]NO[/TD]
[TD="align: center"]NO[/TD]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[/TR]
[TR]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[/TR]
[TR]
[TD="align: center"]NO[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]NO[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]NO[/TD]
[TD="align: center"]Yes L4[/TD]
[/TR]
[TR]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[/TR]
[TR]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L2[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Yes L1[/TD]
[TD="align: center"]Yes L1[/TD]
[TD="align: center"]Yes L1[/TD]
[TD="align: center"]NO[/TD]
[TD="align: center"]NO[/TD]
[TD="align: center"]OSS[/TD]
[TD="align: center"]OSS[/TD]
[TD="align: center"]OSS[/TD]
[/TR]
[TR]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]Yes L3[/TD]
[TD="align: center"]ABS[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[TD="align: center"]Yes L4[/TD]
[/TR]
</tbody>[/TABLE]


Under consecutive days, these are what they are vs. what they should be
1=4
1=2
1=0 (but this one isn't a big deal unless he returns the next day (9/23) with a "YES L1" but it would read "1")

 

[Eric W]

With the addition of "NO" to the mix, I changed it so that I'm explicitly looking for ISS, OSS, or NO to reset the count. Then I needed to use MMULT as the equivalent of an OR. Here's the latest version:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
Yes L2	Yes L2	Yes L3	Yes L3	Yes L3	Yes L3
Yes L3	Yes L3	Yes L3	Yes L3	Yes L3	Yes L3
Yes L4	Yes L4	Yes L4	Yes L4	NO	Yes L4
Yes L4	Yes L4	Yes L4	Yes L4	Yes L4	Yes L4
Yes L2	Yes L2	Yes L2	Yes L2	Yes L2	Yes L2	Yes L3	Yes L3	Yes L3	Yes L3	Yes L3	Yes L3
Yes L1	Yes L1	Yes L1	NO	NO	OSS	OSS	OSS
Yes L4	Yes L4	Yes L4	Yes L4	Yes L4	Yes L4	Yes L4
Yes L3	Yes L3	ABS	Yes L4	Yes L4	Yes L4	Yes L4	Yes L4	Yes L4	Yes L4	Yes L4	Yes L4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]last name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]first name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]consecutive days[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]days absent[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7-Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8-Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]11-Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12-Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]13-Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]14-Sep[/TD]
[TD="align: right"]15-Sep[/TD]
[TD="align: right"]18-Sep[/TD]
[TD="align: right"]19-Sep[/TD]
[TD="align: right"]20-Sep[/TD]
[TD="align: right"]21-Sep[/TD]
[TD="align: right"]22-Sep[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]student[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]NO[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]NO[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L2[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]student[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L3[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]student[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]NO[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]NO[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]student[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]student[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes L4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "] #7 [/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "] #8 [/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#9[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#10[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=IFERROR(1/LOOKUP(2,1/FREQUENCY(IF(ISNUMBER(SEARCH(MID(LOOKUP(2,1/ISNUMBER(SEARCH("L",E2:Z2)),E2:Z2),5,5),E2:Z2)),COLUMN(E2:Z2)),IF(E2:Z2<>"",IF(MMULT({1,1,1},ISNUMBER(SEARCH({"OSS";"ISS";"NO"},E2:Z2))+0),COLUMN(E2:Z2))))),0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



With this version, I get all the expected results you mention, EXCEPT:

On #7 , I don't see why you expect 0. You have 3 OSS's, 2 NO's, then 3 L1s.
On #8 , you have 10. I assume you must have data for that row that doesn't show, since there are only 7 L4's on that row that I see.

I'll leave it to you to convert it to table format, since you seem to have that sussed out. Let me know if this works for you.

 

[Eric W]

I swear my brain is mush sometimes. If we're looking for the whole cell value, we don't need to use SEARCH, and we can take out the check for an empty cell too:

=IFERROR(1/LOOKUP(2,1/FREQUENCY(IF(ISNUMBER(SEARCH(MID(LOOKUP(2,1/ISNUMBER(SEARCH("L",E2:Z2)),E2:Z2),5,5),E2:Z2)),COLUMN(E2:Z2)),IF(MMULT({1,1,1},IF(E2:Z2={"OSS";"ISS";"NO"},1,0)),COLUMN(E2:Z2)))),0)

with CSE.

 

[mrjoeybrown]

You are amazing and brilliant. I believe that my data will be much more reliable now. I thank you (although our students might not appreciate the efficiency).

 

[Eric W]

Glad this works for you!

And just tell your students that it's good for them!