• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Duration of Stay Table [SOLVED]

Status
Not open for further replies.
N

NickyG

New Member
Hi All, It's Nic again, After having such a great experience with my first problem and recieving such great results from the question I figured I'd ask another. I've been racking my brain in order to somehow create a table that can identify how many cars have parked for certain durations from data. Please refer to the following link for excel sample https://www.dropbox.com/s/rw9a3v3zi3u2ajk/Duration%20of%20Stay%20sample.xlsx


The Scenario: Essentially I've completed a parking study for a town to ensure current parking conditions are adequete. To do this I would go around the town and count the cars within 21 different car parks around town and record the last 4 characters of their number plates.


What I Need: From this I have to work out the duration of stay for individual vehicles. I would somehow like to calculate the count of cars that stayed for <2hrs , 2-4hrs, 4-6hrs & 6-8hrs. Now I could do it manually but that would take an awful lot of time. Any ideas?
 
Hi Nic,

Assuming that the data range with the license plates is "data"


The following formula would return the unique list of license plates (formula shown for cell K2)

=IFERROR(INDEX(Data, MATCH(1,(COUNTIF(K$1:K1, INDEX(Data, 0, MIN(IF((COUNTIF(K$1:K1,Data)=0)*(Data<>""), COLUMN(Data)-MIN(COLUMN(Data))+1))))=0)*(INDEX(Data, 0, MIN(IF((COUNTIF(K$1:K1,Data)=0)*(Data<>""), COLUMN(Data)-MIN(COLUMN(Data))+1)))<>"" ),0), MIN(IF((COUNTIF(K$1:K1,Data)=0)*(Data<>""), COLUMN(Data)-MIN(COLUMN(Data))+1))),"")


enter with Ctrl + Shift + Enter


Then in cell L2, put the following formula to obtain the amt of time (in hours) spent by that car:

=(MAX(IF(MMULT(TRANSPOSE(ROW(Data)^0), N(Data=K2)), Timings))-MIN(IF(MMULT(TRANSPOSE(ROW(Data)^0), N(Data=K2)), Timings)))*24


enter with Ctrl + Shift + Enter


Now that you have the time spent per car, you should be able to determine which of those fall within the time periods desired (such as <2hrs, 2-4 hrs, etc.). You can then add them up to get your results.


I am sure this approach can be optimized, and calculated without using helper columns. But I will do that another day, since I should be going to bed now!


By the way, I would suggest leaving license plate text blank instead of capturing "No Plate", etc.


Cheers,

Sajan.
 
Hi Sajan,


I thank you for your response.


The formula is quite complicated If possible it would be great if someone could impliment it into the sample data and upload it. The reasoning for the no plate was to ensure that the car was counted even though its license plate was missing.
 
Hi Nic ,


Since your data is capturing the presence of a car at various times , if a car is not present in any time slot , does it mean that it has left ? For example , the car L128 does not figure in any time slot of 11 am and later ; does this mean that the car was parked for less than 2 hours ?


Secondly , is it possible that the same car can come into the car park twice in a day ? What I mean is , suppose we consider L128 as not present at 11 am , is it possible that it can come back in the 3 pm time slot ?


Thirdly , what is the calculation with cars whose numbers appear in the 5 pm slot ? Or are there more such slots round the clock ?


Lastly , can you not use helper columns ? If you can , probably the formulae will be simple.


Narayan
 
Hi Narayan,


In regards to your questions.


1: Yes you are correct, If the car is not listed in the 11am,1pm,3pm,5pm slot it means it has left.


2: Yes it is true, If the car was to come back at say the 3pm slot it would be considered to be a seperate stay I.e. If the car was recorded at 9am but not 11am that would be 1 stay, then if it returned at 1pm and was recorded at both 3pm & 5pm it is assumed it stayed for the entire 4hour period.


3: After 5pm no more recordings were taken it is assumed that the large majority of these cars left after this time. Therefore the cars that arrive at 5pm are assumed to have left by 7pm therefore being only recorded for 2hours.


4: It doesnt bother me if helper columns are needed or not. What ever is simpler for you. I'll simply plug my recorded data into this file (when it works) and transfer that data to a seperate file.. It doesnt have to be pretty.. as long as it works.


Hope that helps
 
Hi Nic ,


I think point 2 is difficult to take care of using formulae ; Sajan can confirm whether his formula takes multiple parkings into account ; VBA will be easy. Is it acceptable ?


Narayan
 
Hi Nic,

The second formula above did not account for multiple visits, but accounting for the multiple visits is straightforward. Replace the second formula above with the following:

=SUM(MMULT(TRANSPOSE(ROW(Data)^0), N(Data=K2))*(COLUMN(Timings)^0)*2)


enter with Ctrl + Shift + Enter


For your sample data, I got the following results:

[pre]
Code: 
< 2 hrs	2-4 hrs	4-6 hrs	6-8 hrs
24	11	5	11
[/pre]
See the formulas in action in the following doc:

http://speedy.sh/Fr2re/Chandoo-NickyG-Calculate-Parking-Durations.xlsx


Cheers,

Sajan.
 
Hi Narayan,

The formula returns an array with a 1 for the car being present in a timeslot and 0 if the car is absent. As such, if the result is {1,0,1,1,1} it suggests that the car was present for the first timeslot, absent for the second timeslot, and present for the remaining timeslots. Looking at the second formula, it could be simplified to be

=SUM(MMULT(TRANSPOSE(ROW(Data)^0), N(Data=K2))*2)


Have you identified a specific case where the formula is not working?


-Sajan.
 
Hi Sajan ,


My understanding of Nic's requirement is that the application should count the visits ; theoretically a car could have a maximum of 3 visits ; presence at 9 , 1 and 5 , absence at 11 and 3 ; in this case , the count should be 3 in the <= 2 hours slab.


Can you verify whether this is happening ?


Similarly , if a car is present at 9 , absent at 11 , present at 3 and 5 , the count should be 1 for <= 2 hours , and 1 for 2 - 4 hours.


Narayan
 
Hi Narayan,

I now understand what you are asking... essentially, if the same car visits multiple times, should the duration be aggregated or should it be considered as distinct visits. The above formula aggregates them. But I suppose the requirement could be interpreted how you stated it above -- as distinct visits. (i.e. if a car has multiple, non-consecutive visits, treat each occurence like a new car.)


If your interpretation is the correct requirement, then the formula will need to be reworked.


Regards,

Sajan.
 
Hi Sajan ,


If we go by the fact that multiple visits can be taken as one , then to get the number of hours all we need to do is use a COUNTIF. Multiply this result by 2 to get the total number of hours.


Narayan
 
Hi Sajan & Narayan,


Yes as suggested you guys are on the right track with each visit being counted individually rather than aggregated.


Sajan, As you have shown if your formula complies to the aforementioned criteria which I think it does consider this job solved.
 
Hi Nic ,


Can you clarify what you want ? I gave two examples ; is my understanding correct ? Sajan has confirmed that if my understanding is correct , then his formula will need to be reworked.


I am repeating my two examples here ; please clarify / confirm.



My understanding of Nic's requirement is that the application should count the visits ; theoretically a car could have a maximum of 3 visits in the period 9 to 7 ; presence at 9 , 1 and 5 , absence at 11 and 3 ; in this case , the count should be 3 in the <= 2 hours slab.


Similarly , if a car is present at 9 , absent at 11 , present at 3 and 5 , the count should be 1 for <= 2 hours , and 1 for 2 - 4 hours.




Narayan
 
Hi Narayan,

You are absolutely right about the COUNTIF(). I was exploring an approach and ended up with the above formula... but did not review it to see if the approach was still warranted.


Hi Nic,

The above formula did not treat multiple, non-consecutive visits as distinct visits. As such, I have modified the formulas and re-uploaded the workbook:

http://speedy.sh/37BUu/Chandoo-NickyG-Calculate-Parking-Durations.xlsx


Essentially, I added a helper column called "Parking Pattern" with the following array formula:

=DEC2BIN(SUM(MMULT(TRANSPOSE(ROW(Data)^0), N(Data=J4))*{16,8,4,2,1}),5)


Then, to count the number of distinct visits per time period, I used the following array formula:

=SUM(N(LEN(TRIM(MID(SUBSTITUTE($K4,"0",REPT(" ",100)),(ROW($A$1:INDEX($A:$A,1+LEN($K4)-LEN(SUBSTITUTE($K4,"0",""))))-1)*100+1,100)))*2=L$3))


The sample output looks as follows:

[pre]
Code: 
#                          < 2 hrs	2-4 hrs	4-6 hrs	6-8 hrs	8-10 hrs
32	14	3	4	6
Unique	Parking Pattern	       2	4	6	8	10
SAJAN	10011	               1	1	0	0	0
C383	11111	               0	0	0	0	1
M081	11000	               0	1	0	0	0
X705	11111	               0	0	0	0	1
B785	11111	               0	0	0	0	1
LIR1	10000	               1	0	0	0	0
K480	11000	               0	1	0	0	0
C732	11011	               0	2	0	0	0
O624	11111	               0	0	0	0	1
M222	11111	               0	0	0	0	1
H669	11010	               1	1	0	0	0
U945	10000	               1	0	0	0	0
K108	10010	               2	0	0	0	0
[/pre]
Cheers,

Sajan.
 
Hi All,


Yes Narayan, The formula needed to be reworked to show as you said and your understanding below is correct


'My understanding of Nic's requirement is that the application should count the visits ; theoretically a car could have a maximum of 3 visits in the period 9 to 7 ; presence at 9 , 1 and 5 , absence at 11 and 3 ; in this case , the count should be 3 in the <= 2 hours slab.


Similarly , if a car is present at 9 , absent at 11 , present at 3 and 5 , the count should be 1 for <= 2 hours , and 1 for 2 - 4 hours.'


Sajan has completed this and the formula works great. I'll do some minor tweaking to see if i can get it to auto expand and count through the use of tables. Thanks for the Help
 
Jeff,

Thanks for your kind words!


To quote Ron Weasley in Harry Potter and the Deathly Hallows: "Always the tone of surprise"!! :)


Since the solution needed "2d" array handling, the above approach seemed the most logical. (I am certain that there is a matrix based solution also, but I haven't had a lot of time to think through that.)


-Sajan.
 
Hi all thanks for the worksheet it worked great. However I now wish to use the sheet for over 3200 car parks which means from experience the sheet will run really slow. Could this be reworked into VBA code?
 
Hi Nic ,

Can you upload your sample workbook with proper data and proper output , so that testing the macro will be straightforward ?

Narayan
 
Status
Not open for further replies.
Back
Top