Formula Recommendation

[joelyalex]

Hi All,

I'm looking for recommendations for a formula (or alternative function) that will enable me to identify where a student hasn't attended up to two classes within any given 30 day rolling period between February and June. Does anyone have any recommendations?

For the data set, the attendance data can either be displayed as a whole number of classes attended between February and June (e.g. 45 classes between 1st Feb and 30th June). Or I can display the data as a whole number of classess attended per week between February and June (e.g Week 1: 5 classes, Week 2: 7 classes etc.)

Any advice would be much appreciated!

Many thanks,

Joel

[#2]

@joelyalex 

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.

[#3]

@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: Attendance data sample.xlsx (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

[#4 · Accepted solution]

@joelyalex 

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.

[#5]

@mathetes 

This is exactly what I need, thank you very much for your time, help and explanation! I just couldn't get my head around how to do it so will be remembering this in future.

All the best,

Joel