HELP- overlapping number formula

Hi, my name is Amy.

I need help with finding a formula that's going to work out how many over laps there are based on start times/end times of classes.

This is what I have been given to use to work out:

The answer from this is 7, 7 rooms at 1 time.
If it helps we have 20 class rooms.

Start End
08:30 17:00 1
09:00 11:00 2
09:00 17:00 2
10:00 16:30 4
10:30 12:00 5
11:00 13:00 5
11:00 13:00 5
13:30 17:00 4
14:00 17:00 5

Any help would be greatly appciated

Attached is a simple approach that creates a table of classroom usage at half hour intervals. Here is a fragment:

classUse.png

The formula in C2 copied across and down is:
=IF(AND(C$1>=$A2, C$1<$B2), 1, "")

Row 11 simply sums the rows above. C12 finds the maximum value in row 11 (your required answer).

Note that I calculate the maximum classroom usage as 6 rather than the 7 you expect. Please point out where I am wrong if you believe your total is correct.

The attached workbook implements the above.

I was assuming that if a class finishes at 11:00 then the room is available starting at 11:00 for the next class.

Maybe I have never had experience of how long it takes to herd a group of students out of a room and get a new group in

If Ben has the correct interpretation then my formula would change to:

Formula:

Please Login or Register  to view this content.

Hi everyone,

Thank you so so much for all your help!! This has helped us so much!!

I really greatly apprciated all your work for helping with this

Thanks again!!

Amy

Thanks for the feedback. Glad it was of use to you

As Ali requests please mark the thread as solved if you're all set.