Trying to calculate school attendance in Excel 2010

**barclaygp** · 08-24-2012, 08:59 AM

I am an assistant principal in a high school and am wanting to set up an attendance calculation spreadsheet. I have already set up some parts of it. For instance, I have columns that will count how many excused absences, unexcused absences, and tardies that students have. However, I would also like to set up additional columns that will begin counting once the unexcused absences and tardies reach 7, and I have not been able to do that. Thanks.

**K m** · 08-24-2012, 09:09 AM

It would be better with a sample workbook but this should work

=if((unexcusted_absenses+tardies)>=7,"Flag","")

==================
It can also be done with Conditional Formatting

**Andrew-R** · 08-24-2012, 09:10 AM

If you could post an example spreadsheet (obviously without any real information in) then it would make it much easier to see what you're after.

As a starter for 10, however, if you had a spreadsheet with students' names in column A, starting at row 2, excused absences in column B, unexcused in column C and tardies in column D then in cell E2 you could have something like:

=MAX(0,SUM(C2:D2)-7)

Which would give you the number of infractions over 7 that student has.

If, instead, you want that column to be at 0 until the infraction count reaches 7 and then show the number you'd want something like:

=IF(SUM(C2:D2)<7,0,SUM(C2:D2))

**barclaygp** · 08-24-2012, 09:24 AM

Here is the spreadsheet without names and student numbers. I have it set up to flag whenever they reach seven unexcused absences and tardies, but I would like a new column to begin at one when they reach 7, go to 2 when they reach 8, and so on. Also, when they reach those absences and tardies, they will be required to serve detention, so I want to insert a formula to make the new column decrease once a detention have been served.

**Andrew-R** · 08-24-2012, 09:34 AM

If I'm right in thinking G2 is your flag then the formula to make it a count would be:

=MAX(0,SUM(E2:F2)-6)

How do we tell how many detentions have been served?

**barclaygp** · 08-24-2012, 09:44 AM

The detentions served will be submitted to school administration physically, so a formula will be needed to be able to decrease the number in the new column. Is that even possible? I was thinking that one of the unexcused absences could be changed so the count in the new column would decrease. Thanks so much for your help with this.

**K m** · 08-24-2012, 09:52 AM

I think you need a new column that indicates detentions served.

If so, the formula could be changed to include this column. However, you would have to determine the "weighting factor" ie how much to reduce the time based on detentions served

**Andrew-R** · 08-24-2012, 10:00 AM

The easiest solution would be to insert a column between F & G to count number of detentions.

This inserted column would become column G, with the flag formula moving to column H. So H2 would now be:

=MAX(0,SUM(E2:F2)-(6+G2))