+ Reply to Thread
Results 1 to 7 of 7

HELP- overlapping number formula

  1. #1
    Registered User
    Join Date
    04-17-2019
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    2

    Question 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

  2. #2
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: HELP- overlapping number formula

    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.
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: HELP- overlapping number formula

    Try:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: HELP- overlapping number formula

    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: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-17-2019
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    2

    Re: HELP- overlapping number formula

    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

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: HELP- overlapping number formula

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: HELP- overlapping number formula

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] counting number of days in overlapping date ranges
    By afgi in forum Excel General
    Replies: 11
    Last Post: 07-18-2018, 03:02 PM
  2. Find number of overlapping days per person
    By maryjane84 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-21-2016, 12:46 PM
  3. [SOLVED] Excel Formula/Function to find total number of non-overlapping months
    By akynyemi in forum Excel General
    Replies: 6
    Last Post: 09-07-2015, 06:15 PM
  4. number of overlapping minutes between two sets of times
    By laborspy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-04-2014, 12:47 PM
  5. Number of overlapping calls
    By wwaitesr in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-24-2014, 09:30 PM
  6. How to Count number of overlapping rows within a time interval
    By czou6 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-10-2013, 04:45 PM
  7. Number range and overlapping values
    By losty in forum Excel General
    Replies: 1
    Last Post: 04-21-2012, 12:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1