4 Replies Latest reply: Sep 1, 2013 1:04 PM by KKA
KKA Level 1 Level 1 (0 points)


I'm a HS teacher, and I'd like to set up an attendance worksheet that would allow me to keep tabs on students' classroom participation--for instance, something that I could mark each kid A or 0 (Absent), and then (assuming they are present) either a 1, 2, or 3 for their degree of participation in the class.  The idea would be that at the end, I could tally each kid's score and come up with a good idea of how to grade them on the "class participation" portion of their overall grade.  So, for instance, if there were 20 days of class in a particular block of lessons, someone who got a "3" each day would have the maximum score of 60, whereas someone who got a 2 each day would have only a 40.


Ideally, there might even be another table that converted that fraction (16/20) into a percentage rate, since usually the number of days in a given block is not as nicely "round-able" as 20. 


I've looked at Roger's attendance worksheet (referenced in some other links on this discussion board), but that seems to be set up as a simpler "present/absent" binary option. 


I should say, finally, that I'm an English/History teacher and decidedly not a "technie," so if you can put any response in relatively simple language, I'd appreciate it.


Thanks in advance!


macbook 2,1, Mac OS X (10.4.10)
  • Jerrold Green1 Level 7 Level 7 (29,935 points)



    If you mock-up a Numbers Sheet illustrating your needs, we can help you with the programming. Take a shot at it and then post a Screen Capture here for us to comment on.


    To capture an area of your screen, type Shift-Command-4 and then drag the cross-hair cursor across the parts of interest. The graphic file will appear on your Desktop. You then use the Camera Icon in the discussions Toolbar to bring the screen graphic into the discussion's editor.



  • KKA Level 1 Level 1 (0 points)

    Hi Jerry,

    Thanks for your suggestion.  When I went back and created the draft table, I think I figured out the programming piece myself, because it turned out to be rather simple.  I realized that if I worked off the existing gradebook template I could take advantage of their calculators/formulas.  Here's what I came up with.  Thanks for being willing to field my question!

    Screen Shot 2013-08-31 at 11.10.28 PM.png

  • Barry Level 7 Level 7 (29,215 points)

    HI Kristin,


    I read your question earlier, and started to work on a table to answer it, but got interrupted by dinner and some chores.


    While you've answered your own question, I think this might be of interest.


    The paired rows are identical, with the exception of number of absences. There are two calculation of the percent score for each student, and two calculations of the average score (in the range 0 to 3). Explanations below.

    Screen Shot 2013-08-31 at 9.29.41 PM.png

    Adam is the 'perfect' student—always there, and always engaged in the class. In the first eight days, he's scored 3 every day. His score is three out of three, or 100%, no matter how you calculate it.


    Bobbi is just as perfect, but missed one class (due to a medical appointment, as it turns out). A straightforward calculation of her points shows 21 for the first eight days, 88% of what she could have earned if she'd been in class on all eight days (as shown in column AD). 'But,' she might say, I couldn't be here that day for a good reason. I shouldn't be penalized for that. Column AE considers only the days where the student is present (ie. where the score is greater than the zero assigned for 'Absent,' and Bobbi was 100% on those days.


    Calvin and Dave are pretty good students—average one day, superior the next. Dave was away a couple of days. Including those days in his calculations drops his percent score from 83 to 63, ignoring them keeps his percentage at 83, the same as Calvin's.


    Gerri and Henry's rows introduce a new category—an excused absence, like the one due to Bobbi's medical appointment. In terms of 'bums in the seats', both have the same attendance (and both participate equally well on the days both are there). On the days they were absent, Gerri was playing hookey so she could go shopping; Henry was playing hockey with the school team, and permission to be absent had been asked for and granted before the team left for the tournament. Using a text code (e) for the excused absence, and a numerical one (0) for the unexcused makes the formula in AD ignore the excused absences, but include the unexcused ones in its calculations.


    The final pair shows the case of a pair of students who entered the class several days after the rest. The days before they arrived, marked with text entries ( - ) are ignored by the formulas.


    The last two columns do similar caculations to those in AD and AE, but show the results as a decimal value out of 3.


    Any of the four formulas in these columns will provide you with a readable and useable result; the choice of wich to use is up to you as the teacher, and the choice depends on how you and your school want to treat absences in terms of their affecting calculated grades.


    Formulas for each column are listed below.


    AA2: =COUNTIF(B2:Z2,0)


    Note that this counts only the absences marked with a zero. (see row 9, Henry)


    To count excused absences, marked with "e", you'll need this version, placed in a different column:


    Aa2: =COUNTIF(B11:Z11,"e")


    For a single count including both types of absence, you could use this (see row11,  John):


    AA2: =COUNTIF(B11:Z11,0)+COUNTIF(B11:Z11,"e")


    AB2: =COUNTIF(B2:Z2,">0")


    This counts the number of days the student was present (marked with 1, 2, or 3).


    AC2: =SUM(B2:Z2)


    Sums the number of points earned to date.


    AD2: =SUM(B2:Z2)/(3*COUNT(B2:Z2))

    AE2: =SUM(B2:Z2)/(3*COUNTIF(B2:Z2,">0"))


    Calculates the fraction points earned/points available, as described for the various cases above. Cells are formatted to display this value as a percentage.


    AF2: =AVERAGE(B2:Z2)

    AG2: =AVERAGEIF(B2:Z2,">0")


    Calculates the average points per day, using 'all days' or 'days attended' as above.


    Interesting exercise. Took me back to working out a similar sheet in AppleWorks (or more likely, ClarisWorks) for my own classroom several years ago.




  • KKA Level 1 Level 1 (0 points)

    Hi Barry,

    This is great!  Much more sophisticated than what I'd worked out.  I'm going to try it out--perhaps even adjusting so that it dovetails with the other charts in my gradebook. 


    I really appreciate the time and thought you put into this.  I'm the "guinea pig" of our school--trying out new grading formats--so when I pass on what I've set up, it's likely that this will be helpful to more than one person.


    On that note, I'd think that if you made it into a template, something along these lines would be helpful to all sorts of teachers.  By the time grade-time rolls around, it's often hard to remember (at least fairly, not based on one's own bias) which students contributed consistently.  Part of why I'm interested in tracking this is so that I don't get caught up in my own preconceptions of what each student's participation grade should be.  Hopefully, but marking down something each day, I'll "autocorrect" (as it were).

    Thanks again!