• 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 V2

N

NickyG

New Member
HiGuys, Sajan and Narayan really helped me out with my last request however now that my details are getting a bit more complicated i need my excel sheet to do some more work. Below is a link to the updated sample excel. What I would like to do is group the unique plates by time restriction. Within the excel sheet the input data is recorded in different colours, these different colour represents a different parking restriction within the park, be in red for 2 hour zone black for all day etc.. Is it possible for the method used in the sheet to further break down the data into these groups so on top of having the break up parking durations for the entire car park it will also have it for each time restriction depicted by colour?


https://www.dropbox.com/s/vyttdquh1gqomj1/Duration%20of%20Stay%20V2.xlsx
 
Good day NickyG


Your title may restrict help from forum members as they will think it is to do with the origanal question which they read and will think they cannot offer more so will not answer.

As it is a diffrent question even if it is related to the original post a clearer title would attract more viewers
 
Hi Nic ,


It is not possible to use formulae to identify the color of characters within a cell , unless you use Excel4Macro functions.


What I can suggest is that you use a macro to split the data onto multiple worksheets based on the color of the characters within a cell , and then use Sajan's formulae in each of those multiple worksheets.


If this is OK with you , I can write the macro.


Narayan
 
NickyG


Apologise are not needed, it is just that by burying a diffrent question in a post or with the same title will restrict your forum members from viewing and maybe helping :)
 
Another approach - if the layout of red,black and green cells doesn't change - is to assign a named range to the cells with red font, a named range to the cells with black text, and a named range to the cells with green font. Then you can use a formula.
 
On re-reading the question, I see that the layout of red, black, and green won't remain constant. That said, you could use 3 separate input tables, and have Sajan's original formula point across all of them, and the have a separate formula for each of the three individual tables.


On another matter, some of these plates appear twice in the same time-slot. e.g. X784 and A125 both appear twice in the first timeslot.
 
Hi Nic,

Are you looking to obtain a breakdown similar to the following?


Color Black

< 2 hrs, 2-4 hrs, 4-6 hrs,...


Color Red

< 2 hrs, 2-4 hrs, 4-6 hrs,...


etc.


As Narayan pointed out, determining the color of a cell is not possible with formulas. But what if the source data is structured slightly differently?


Instead of coloring the cells, could you have a column on the left indicating the color (or grouping)? (This would also require that each source data row match the color indicated in the left-most column. This strategy may cause blank cells in between rows and in between columns, but that might be OK.)


While Narayan is pursuing a VBA based solution, it might be good to explore what is possible from a formula perspective also, atleast for academic reasons.


Cheers,

Sajan.
 
Hi Sajan ,


I am not suggesting a VBA-based solution !


What I suggested is just segregate the input data , color-wise , into separate worksheets , so that your earlier formula , which is already working , can be used in each of the worksheets.


Narayan
 
Hi Nic,

I tweaked the formula for determining the parking pattern for a color as follows:

=DEC2BIN(SUM(TEXT(MMULT(TRANSPOSE(ROW(Data)^0), (Data=$K5)*(DataColors=L$1)),"1;;0;")*{16,8,4,2,1}),5)


enter with Ctrl + Shift + Enter


The formula also accounts for some cars being in multiple parking lots during the same time period.


The rest of the formulas remain the same.


I have uploaded a sample worksheet that shows the formulas in action. (I have only copied the formulas to some of the rows.) To add additional colors, just repeat the color sections. In the source data, the colors can be in any order, and repeated if needed.

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


Cheers,

Sajan.
 
Yes Sajan, I believe your option may work, I'll tweak it a little bit and show you guys the results. Thanks for the ideas.
 
Hi Sajan ,



The formula also accounts for some cars being in multiple parking lots during the same time period.




Too much of Harry Potter !


Narayan
 
Hi Narayan,

I was actually picturing their parking lots all connected and on a slope... essentially, cars roll down from one lot to another... hence the reason why the same car visits multiple lots in the same time period! :)


-Sajan.
 
You must log in or register to reply here.
Back
Top