ATTENDANCE SPREADSHEET

[Chris]

Me again! Some of our students have to attend 6 times and some 7. They are
listed down column A with various details (including attendance) running
across the row. At the end of all of their attendance days is a 'total
attendance'column. Assuming they all attend all of the days (I wish!!) is
there a one formula fits all solution to show the percentage of attendance
for each student. If they do attend 6 or 7 (as required) it will still amount
to 100%. I could then copy that formula down all of the students and this
would save separate formulae for each of the 6's and 7's. A nightmare to sort
out!
Thanks

 

[Pete_UK]

How do you indicate in column A that it should be 6 or 7 attendances for
each student? Is your attendance shown as Y and absence shown as A, or do
you have some other codes? Do you leave the column blank for a student who
is not expected to be in (on the 7th session)?

Please give a few more details.

Pete

 

[Chris]

Thanks Pete. I was hoping to use Y and A but having already had some
problems with using a letter (with autocorrect that affected the whole
suite!) I was hoping to just use a 1 for attending and 0 for non attendance.
The 7th attendance, if not needed, would be blank. If that would create yet
more problems maybe you have a suggestion?
Chris

 

[Pete_UK]

No, that's fine. COUNT(range) will count all the cells which are not empty
and which contain numbers, so that represents the maximum attendances
expected. COUNTIF can be used to count only the cells containing a
particular value (1), so you can put these together to get a percentage:

=COUNTIF(range,1)/COUNT(range)

and format the cell as a percentage. Here "range" will be something like
B2:AM2 for the first student in row 2, and then you can copy this down for
the other students.

Hope this helps.

Pete

 

[Chris]

Thanks Pete, that worked brilliantly. After 2 days of slogging on it I had
given up hope and lost the will to live! Thanks
Chris

 

[Pete_UK]

Well, Chris, I hope you are suitably invigorated now to face the next
challenges - thanks for feeding back.

Pete