I am doing an attendance report and have to calculate absences (codes UA, SKUP & NC/NS) based on a rolling year. I already have the tardies/late set up but I calculate it manually as every 3 equals 1 occurrence and I couldn't figure out a formula for that lol. With the absences, the 3 codes listed above is considered unexcused. If it is an individual event it is 1 occurrence. 2 events is 2 occurrences BUT 3 or more goes back to 1 occurrence. The occurrence codes are referenced in column E beginning at row 95. The total needs roll into O6....Now the tardie or late total is rolling year from 04/01/2016 BUT the absent total is just a straight rolling year. Do I need to create a separate column next to my current occurrence column or can a formula be put in to just do what I need?
Workbook is attached for reference.
Bookmarks