Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
  •