2 Replies Latest reply on Oct 17, 2018 6:29 AM by Tom Hier

    How to Calculate Percentages Based on a Count of Like-Records in a Field

    Tom Hier

      Hi.  Here is the situation.  I have a field called "04 Room Use Type Level 1" which describes various room types -- e.g., "Amphitheatre", "Lecture Hall", "Classroom", etc.  The database where this field resides is a database of individual course records (e.g., Nursing 101, etc.).  Each record (course) has meeting hours associated with it (e.g., Nursing 101 might meet for 4 hours per week).  I have a chart that shows for each room type the sum of total class meeting hours for that room type.  I'm now trying to figure out how to compute the percentage of hours used -- either by day or by week.  In algebraic terms, the calculation I need to create would be something like the following:

       

      Sum(Total Class Hours per Week for a Particular Room Type) / [ Count(Rooms of That Type) * (Number of Available Hours Per Day (or per Week))]

       

      To make this real, assume I have 5 Lecture Halls, and 7 Classrooms in the database.  I have 20 courses that meet in the 5 various Lecture Halls (with a total of 50 hours in those Lecture Halls), and I have 10 courses that meet in the 7 Classrooms (with a total of 40 hours in those Classrooms).

       

      I'm trying to figure out what the calculated field would be that would give me results -- e.g.:

      ...add all course hours associated with Lecture Halls  (50 hours)

      ...add up the number of Lecture Halls in the "04  Room Use Type Level 1" field (5 halls)

      ...compute the total potential hours available to be scheduled (5 halls * 40 hours per hall per week)

      ...compute the room utilization of Lecture Halls:  50 hours used / (5 halls * 40 hours per hall per week) = 50 / 200 = 25% room utilization for Lecture Halls.

       

      In the attached packaged workbook, the first sheet, labeled "START WITH THIS SHEET" shows the total hours scheduled in each room type.  Now I need to add up the number of rooms of that type in the "04 Room Use Type Level 1" field, multiply it by the total hours available per day or per week (which I already have as a parameter) and then compute the percentage.  I just don't know how to add the number of rooms by room type in a calculation.

       

      Hope this makes sense.  Thanks.

      (Looking forward to the conference next week!!!)