Forum Discussion

joelyalex's avatar
joelyalex
Copper Contributor
Jun 13, 2022
Solved

Formula Recommendation

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 p...
excel
Formulas and Functions
  • mathetes's avatar
    mathetes
    Jun 13, 2022

    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.

     

mathetes's avatar
mathetes
Gold Contributor
Jun 13, 2022

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.

 

joelyalex's avatar
joelyalex
Copper Contributor
Jun 14, 2022

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