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.
Can you tell us how you have been tracking attendance in the first place? That is, what does the data set actually look like? Your second paragraph is a little ambiguous--are you describing how you'd like the result of the inquiry to be displayed? Or is that how you store the data that is to be queried?
And, just for clarity, let me re-state what you appear to be asking: "I'd like to identify any student who has not attended a minimum of two classes in any rolling 30 day period between Feb and June"
If it's possible--assuming you do have an attendance worksheet--could you post a copy of that workbook in Google Drive or OneDrive; just making sure that no real names of real students are included.
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.