Question

Use a spreadsheet to prepare a class timetable. It should compute and check the total number of lectures, tutorials and lab practical sessions allocated for each subject. It should also compute and check the total number of hours of engagement for each teacher.

Answer 1

Create a grid mapping Days against Time Slots. This is where you manually enter the Subject and Teacher codes.

Columns: Column A for Day, Column B for Time Slot, Columns C to G for Classes/Batches (e.g., FY, SY, TY, or Batch A, Batch B).

Data Entry Format: Input data using a consistent format like [Subject Code] / [Teacher Code].

Example: CS101 / PROF_A for a lecture, or CS101_LAB / PROF_A for a practical session.

Create a separate table on the same sheet or a new tab to count and verify the sessions allocated for each subject against the academic syllabus requirements.

Table Layout:

• Column A: Subject Code (e.g., CS101)
• Column B: Required Lectures (Manual Input)
• Column C: Allocated Lectures (Formula)
• Column D: Required Tutorials (Manual Input)
• Column E: Allocated Tutorials (Formula)
• Column F: Required Labs (Manual Input)
• Column G: Allocated Labs (Formula)
• Column H: Allocation Status (Check Formula)

Key Formulas for Subject Allocation (Assuming Weekly Grid is in range C4:G30):

To Count Allocated Lectures:
=COUNTIF(C4:G30, A4)
(Counts exact matches of the subject code)

To Count Allocated Labs/Practical Sessions:
=COUNTIF(C4:G30, A4 & "_LAB")
(Counts cells that append “_LAB” to the subject code)

To Check Status (Column H):
=IF(AND(C4=B4, E4=D4, G4=F4), "OK", "ERROR/MISMATCH")

Create a third table to calculate the total hours of engagement for each teacher and check if they meet or exceed their target hours.

Table Layout:

• Column A: Teacher Code (e.g., PROF_A)
• Column B: Target Weekly Hours (Manual Input, e.g., 16)
• Column C: Actual Lecture/Tutorial Hours (Formula)
• Column D: Actual Lab/Practical Hours (Formula)
• Column E: Total Engagement Hours (Formula)
• Column F: Workload Status (Check Formula)

To Count Lecture/Tutorial Hours (Assuming 1 Session = 1 Hour):
=COUNTIF($C$4:$G$30, "*" & A4)
(Uses a wildcard * to find any cell ending with that teacher's code)

To Count Lab Hours (Assuming 1 Lab Session = 2 Hours):
=COUNTIF($C$4:$G$30, "*_LAB*" & A4) * 2
(Counts lab cells containing the teacher code and multiplies by 2)

To Compute Total Engagement Hours (Column E):
=SUM(C4:D4)

To Check Workload Status (Column F):
=IF(E4>B4, "OVERLOADED", IF(E4=B4, "PERFECT", "UNDER-UTILIZED"))