Determine Month of Class Attended (2003/2003)

Topic

New Reply

Hello everyone

I need a bit of help. I need to keep track of how many students attended classes in May, June, July, and August 2006. Students could attend classes in just one of those months, or all of those months. Each record is an individual student. I have a list that looks a little like this:

May?	June?	July?	Aug?	Class_date	Class_date	Class_date
Yes	No	Yes	No	05/10/2006	07/06/2006	05/25/2006
No	Yes	Yes	No		06/06/2006	07/12/2006

I only need to determine if a student attended at least one class in the month of interest. I then count all the “Yes” in each month’s column. As new students attend they are added to the list, and the list is resorted by last name. I’m trying to automate this so I don’t have to enter Yes, or No in the month columns. I can potentionally expect up to 300 students.

Any suggestions?

Thanks so much,
Rich

Reply | Quote

Replies

Rich – will you always have three Class_Dates listed, or could there be more – for instance, if every weekly class was listed?

With only three you could generate the “Yes” and “No” responses by formula (warning, aircode) – something like:

=IF((MONTH($E6) = A$4) + (MONTH($F6) = A$4) + (MONTH($G6) = A$4) > 0,"Yes","No")

where row 4 contains the month values – “5” in the column coresponding to May, “6” for June, etc, and the columns E through G contain the Class_Dates as shown. You would copy this formula to columns A-D and the result will match the sample you provided. You can then add the “Yes’s” and “No’s” with a “Countif” or an array function.

If there are more than three or four columns (like twenty or thirty) then this sort of code becomes very difficult to maintain, and I would suggest looking at a database solution, or at least at normalizing your data so that each record represented one class time. That might leave you with multiple records per student, which requires two steps in determining the number of students who attended any class in each month. You would add the number of classes in -say- August for each student, and then count the number of students where the class count >= 1. That means a little more work on processing, but it will make maintenance much easier.

Reply | Quote

Dear Dean,

At the moment I the maximum number of different class dates would be four columns. So, I will try your suggestion.

You are absolutely correct about moving to a database (Access or Oracle), which is what I really should have done in the first place. Then I could run appropriate queries on the data.

I’ll let you know how your formula suggestion worked.

Thanks so much,
G’Day,
Rich

Reply | Quote

Rich – I think Hans’ use of the Match formula is cleaner and will be easier to maintain, and probably execute faster, too!

Reply | Quote

You could use array formulas (confirmed with Ctrl+Shift+Enter) with the MATCH function. See attached workbook. If you apply the formulas in your own workbook, take care with the absolute and relative references; you should be able to enter the formula in one cell, then fill down and right.

Reply | Quote