# Here’s an idea for using Excel to calculate students’ community service time

Subjects:

Computers & Internet, Math

6, 7, 8

Title – Automating Community Service
By – John J. Cafagna
Primary Subject – Computers / Internet
Secondary Subjects – Math
In my school, every student is required to participate in at least 25 hours of community service.

Since I teach technology, I meshed tracking the community service with Microsoft Excel.

The students developed an automated spreadsheet that used formulas to track the total hours they have completed and also how many hours they need to achieve their goal. (some ambitious students set goals above the requirement.)

We explored ways to make the spreadsheet so that it would be user friendly.

We arrived at the notion that not all students would have the same amount of entries, so a restrictive formula to total the hours would not be useful. We used an open formula that calculates ALL numbers in a column [ =sum(D:D) ] –> This would calculate all numbers recorded in column D. Beware… if the cell used to make the total is also in column D then it becomes a circular statement and fails.

We also realized that some community service logs may have entries that go beyond the point that when viewing the spreadsheet, the titles are still visible. This is where the task of freeze panes is taught. Selecting WINDOW, then FREEZE PANES, will freeze everything to the left and above the cell the function occurs in. If you want to Freeze row 1 and no columns, then we select this function from cell A2.

To calculate the amount of hours needed to reach the goal we simply made a formula to take the GoalAmount-TotalHours… [ =25-F1 ].

This can be used also as a bank book if you change the titles and create a CREDIT & DEBIT Column using the appropriate formula… [ =sum(D:D)-sum(E:E) ].

E-Mail John J. Cafagna !