Forum Discussion
Formula Recommendation
See the attached. A single formula was created, and then copied to all columns for which there are four weeks to be summed up, and then down for all rows for which attendance has been recorded.
The formula is this: =SUM(B2:E2)
B2:E2 contains the data for four sequential weeks. As that formula is copied to adjacent columns, it changes to C2:F2, D2:G2....etc, always getting the four weeks
I then added conditional formatting to highlight any values below 2.
Here's a sample of the results. The full workbook is attached below.
mathetes Thanks for your reply.
Yes, your phrasing is accurate in what I'm trying to identify from the data: Any student who has not attended a minimum of two classes in any rolling 30 day period between Feb and June
Here is the data sample with any identifying information removed: https://livemanchesterac-my.sharepoint.com/:x:/g/personal/joel_loutfi_manchester_ac_uk/EWInu4dzr2tCt-PNxiDeVbUBptKVjMg6tp9I2NJsPwleEw?e=zUzInM (This will be locked for editing but a copy can be downloaded).
I'm only able to obtain the data by a weekly export so I cannot get down to the granular detail of attendance per day, which I imagine would have made this task a little easier. I suppose the 'rolling 30 day period' could be changed to 'rolling 4 week period' to account for the data only being available weekly?
Many thanks!
Joel
See the attached. A single formula was created, and then copied to all columns for which there are four weeks to be summed up, and then down for all rows for which attendance has been recorded.
The formula is this: =SUM(B2:E2)
B2:E2 contains the data for four sequential weeks. As that formula is copied to adjacent columns, it changes to C2:F2, D2:G2....etc, always getting the four weeks
I then added conditional formatting to highlight any values below 2.
Here's a sample of the results. The full workbook is attached below.