# Thread: Determine Month of Class Attended (2003/2003)

1. ## Determine Month of Class Attended (2003/2003)

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:

<table border=1><td>May?</td><td>June?</td><td>July?</td><td>Aug?</td><td>Class_date</td><td>Class_date</td><td>Class_date</td><td>Yes</td><td>No</td><td>Yes</td><td>No</td><td>05/10/2006</td><td>07/06/2006</td><td>05/25/2006</td><td>No</td><td>Yes</td><td>Yes</td><td>No</td><td> </td><td>06/06/2006</td><td>07/12/2006</td></table>

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

2. ## Re: Determine Month of Class Attended (2003/2003)

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:

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

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 <font color=red>very</font color=red> 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.

3. ## Re: Determine Month of Class Attended (2003/2003)

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.

4. ## Re: Determine Month of Class Attended (2003/2003)

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

5. ## Re: Determine Month of Class Attended (2003/2003)

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•