mrjoeybrown
New Member
- Joined
- Sep 25, 2017
- Messages
- 3
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:
<tbody>
</tbody>
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":
<tbody>
</tbody>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.
last name | first name | consecutive days | days absent | 8/30 | 9/1 | 9/4 | 9/5 | 9/6 | 9/7 |
#1 | student | Yes L4 | Yes L4 | Yes L4 | Yes L4 | Yes L4 | |||
#2 | student | Yes L1 | Yes L1 | Yes L1 | ISS | Yes L1 | |||
#3 | student | Yes L1 | ABS | ABS | |||||
#4 | student | Yes L3 | Yes L4 | Yes L4 | Yes L4 | Yes L4 | |||
#5 | student | Yes L3 | OSS | Yes L3 | Yes L3 | Yes L3 |
<tbody>
</tbody>
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":
last name | first name | consecutive days | days absent | 8/30 | 9/1 | 9/4 | 9/5 | 9/6 | 9/7 |
#1 | student | 5 | 0 | Yes L4 | Yes L4 | Yes L4 | Yes L4 | Yes L4 | |
#2 | student | 1 | 0 | Yes L1 | Yes L1 | Yes L1 | ISS | Yes L1 | |
#3 | student | 1 | 2 | Yes L1 | ABS | ABS | |||
#4 | student | 4 | 0 | Yes L3 | Yes L4 | Yes L4 | Yes L4 | Yes L4 | |
#5 | student | 3 | 0 | Yes L3 | OSS | Yes L3 | Yes L3 | Yes L3 |
<tbody>
</tbody>