+ Reply to Thread
Results 1 to 9 of 9

problems calculating occupancy

  1. #1
    Registered User
    Join Date
    04-10-2014
    Location
    Telford, England
    MS-Off Ver
    ms365
    Posts
    34

    problems calculating occupancy

    I am going round in circles with this one.

    In essence I need to count the number of people in the department on a specified day, at any time, by the length of time they have been there.

    The info from the attached will be pulled into another table by referencing the discharge date and counting the numbers of yes in each time period and the LOS from column AC.

    This separate table which will drive a stacked Bar chart.

    The first bit is getting the formulas in columns E to AB to work.

    Each line represents 1 persons stay, so I need to see a YES in all the time blocks that they occupy, which is not happening.

    Its an issue because they span over 24 hrs, i.e. some people who came in yesterday are still here today and some people who came in today, will not go home till tomorrow.

    Making sense so far?

    Could you please look at the If statements I am using as they are not working.

    I have tried IF(AND as well as IF(OR, but I am clearly getting the logic wrong.

    Any help appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: problems calculating occupancy

    what do you want as a result though?
    how do you present those which arent on the same day?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    04-10-2014
    Location
    Telford, England
    MS-Off Ver
    ms365
    Posts
    34

    Re: problems calculating occupancy

    I have added the table that I need to complete to the attached file.

    This will output to a stacked bar chart.

    In simple terms I need to show how many people were in the department by hour and how long they had been waiting.

    i.e. if a person comes in at 00:30:00, they would appear as a <1 hour in the 00:00:00 - 00:59:00 box.

    Providing they don't leave, at 01:00:00 to 01:59:00, they would now appear as a 1-2 hour wait and so on.

    Each hour would capture all the new <1hr arrivals and move everyone else into the next waiting time band until they leave.

    Initially, I put in an extra column in the data set and calculated the Length of Stay (LOS) and tried to use the following statement to pull the numbers into the table.

    this example is for someone who would be in the 1-2 hour band and the Column E:E was used for calculating the LOS.

    =COUNTIFS('ED Long Attenders Data'!$A:$A,$A$2,'ED Long Attenders Data'!$B:$B,"<"&$E2,'ED Long Attenders Data'!$D:$D,">"&$B2,'ED Long Attenders Data'!$E:$E,"<0.0833",'ED Long Attenders Data'!$E:$E,">0.0417")+COUNTIFS('ED Long Attenders Data'!$A:$A,"<>"&$A$2,'ED Long Attenders Data'!$c:$c,$A$2,'ED Long Attenders Data'!$D:$D,">"&$B2,'ED Long Attenders Data'!$E:$E,"<0.0833",'ED Long Attenders Data'!$E:$E,">0.0417")

    Hope this all makes sense, I have been tweaking the logic in the counifs for 3 days and still can't get it right.

    So I figured if I added the columns E-AB to the data, I may be able to add up all the Yes statements, but this does not work either.

    I would rather fix the Countifs and pull the data into the table than use all the other columns E-AB, but I can't get the logic right.

    Any people in the department after 23:59:00 would appear on the next days report, but they would need to bring their LOS with them i.e. at 00:00:00 - 00:59:00 the next day, you would already appear as a 4-8hr or 8-12hr as appropriate.

    kindest regards

    M
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: problems calculating occupancy

    sorry cant look at all of it today but heres what i have done
    i changed how you calculate LOS Column E

    there is a rounding column in column F
    Floor is round down
    ceiling is round up
    not sure which you would want..


    just want to be clear in my understanding

    For your table
    if LOS is 2 hours between 1am - 2am
    do count 1 for 1am and 1 for 2am
    or
    do you put 2 in 2am?

    so 8 hours would between 1-9
    would be 1 in every hour cell between 1 and 9
    or
    do you just put 8 in 9am?
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: problems calculating occupancy

    if patient checks at 1:55am and leaves at 3:05am
    time stayed in total is 1 hour 15 mins right

    however...with the current state of your "yes"
    it would be counted in 0-1am 1-2am and 2-3am bracket
    thereby having 3 yes's for a 1:15 stay
    Even if you round up 1:15 it would only be 2 hours
    is this what is meant to happen?

    see attached with fill in table...is this in line of what you expected?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-10-2014
    Location
    Telford, England
    MS-Off Ver
    ms365
    Posts
    34

    Re: problems calculating occupancy

    Hi Humdingaling.

    It is a good question.

    This is one of the issues where people may only have a 5 min stay during a 1 hour slot, but they are shown as being in for the entire hour.

    I am choosing to use the attendance hour and not the discharge hour.

    I have attached my latest attempt which seems to be working.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: problems calculating occupancy

    Hi Mike

    if you are choosing to base on attendance hour
    with 174 lines of data having 514 data points dont make sense to me
    shouldn't there be 174 as well then?

    made some changes to your countif formula on table tab
    formula in m3 now reads
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    instead of doing the multiplication of time in the formula itself i just changed the headers in row 1

    as you are using arrival date as your countif...you will not i deleted cell a2 on data tab
    that is because it was reading as a line of data...if you want to keep that you will need to change your countif formula on table sheet to be limited certain range instead of A:A

    if you are having performance issues (this may happen if you start doing months instead of days)....you will need to limit your range
    either use a dynamic (either named range or some other method) for countif or manually change it ...or over count it by x amount
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-10-2014
    Location
    Telford, England
    MS-Off Ver
    ms365
    Posts
    34

    Re: problems calculating occupancy

    Hi Humdingaling,

    Thanks once again for your support, this is really good.


    Adding the times in row 1 is much neater

    the only issues I see are that we have people who suddenly appear as 1-2 hours.

    e.g. at 02:00 to 02:59 there are 2 people shown as 1-2 hours, but no one as <1 hr in the 01:00 to 01:59 block.

    to appear in the 1-2 hours you must have a LOS >1hr, so you must have been in the <1 hour, during the previous hour (if you know what I mean).

    if your length of stay is 4 hours and you arrived before 00:59, you would appear as <1 in 00:00 to 00:59, 1-2 in 01:00 to 01:59, 2-3 in 02:00 to 02:59 and 3-4 in the 03:00 to 03:59 time periods.

    So I put the subtraction element in the formula to ensure that you arrived in the previous hour.

    it would now read in M3;

    =COUNTIFS(Data!$A:$A,">="&$B3-M$1,Data!$A:$A,"<"&$C3-M$1,Data!$C:$C,">="&M$1) (where M1 = 0:00, N1 = 01:00 etc. etc.)

    With the above formula, M3 returns 4, and there are 4 people in the data with a LOS greater than 0, who arrived before 00:59. M2 returns 2, and there are 2 people who arrived before 01:59 with a LOS >0.

    I think I will call this solved.

    many, many thanks for putting me on the correct path and helping to work out the logic.

    Kindest regards

    M



    Mike

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: problems calculating occupancy

    great to hear

    glad to be of assistance

    Cheers
    hum

+ 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. Need more help pertaining to the hotel occupancy worksheet
    By TheVodkaDude in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-06-2013, 02:30 AM
  2. Inserting function that will rank rates of occupancy
    By roadrunnerside8 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-06-2013, 01:15 PM
  3. Excel 2007 : Formula issue - Occupancy Schedule
    By dramadog in forum Excel General
    Replies: 0
    Last Post: 06-15-2012, 10:58 AM
  4. Forecasting weekday occupancy between two years
    By titushanke in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-14-2005, 08:43 AM
  5. Calculating a Building\'s Occupancy Rate at Specified Times
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-23-2005, 12:21 PM

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