Trying to Adapt Formula for Picking up reoccurances of unique values

[NicGreen]

Hi All,

I had some great help creating the attached sheet which essentially lists the amount of unique values within a field and then counts whether or not that value occurs in the next row.

I've been using it for model i use to calculate how long a vehicle parks in an area. Attached is a formula based approach which outlines what i'm using.

I know its formula based rather than VBA so it is a little slow but it will do the job for what im intending.

Essentially my issue are as follows;

• at the moment i cannot expand the rows any further to increase the time period (number of rows) that the sheet calculates. Currently it counts up to 9 rows (peach area in attached sheet). I need it to count up to 16 (that is the time period extends from 9am to 5pm not 9am to 12.30pm). I get a #num error when i try to change the code from

DEC2BIN(SUM(TEXT(MMULT(TRANSPOSE(ROW(Data)^0), (Data=$O5)*(DataColors=P$1)),"1;;0;")*{256,128,64,32,16,8,4,2,1}),9)

to

=DEC2BIN(SUM(TEXT(MMULT(TRANSPOSE(ROW(Data)^0), (Data=$O5)*(DataColors=P$1)),"1;;0;")*{512,256,128,64,32,16,8,4,2,1}),10)

Im not sure where i am going wrong.

The other issue i face is that when the parking pattern reads 111101111 (where 1 means a vehicle was parking in the space and 0 means there wasnt one) it should record this as 2 instances of a vehicle parking for a 2 hr period (each character in the set equals 30min). I have provided comments in the sheet to highlight this.

Hope you can let me know where i have gone wrong so I can learn from this. I did initially have some help setting up the formulas by some great members on here so while i have an understanding of how the formulas work I am not 100% sure on what exactly is going on.

 

[NARAYANK991]

Hi ,

That is the basic problem with solving problems using complex formulae !

Using simpler logic , and using helper cells / columns will , in the long run , give you a better perspective of how to use Excel to solve problems , rather than how to create complex formulae using Excel.

In this case , I think the problem is with the DEC2BIN function , on which the Excel help says :

If number < -512 or if number > 511, DEC2BIN returns the #NUM! error value.

Thus , in your case , you are exceeding this limit.

To solve your problem , to which the formula was developed by Sajan , I suggest since Sajan has been inactive on this forum for a long time , you explain your problem in detail , upload a file which has enough data to cater to all possible situations , and then we can think of a VBA solution which is maintenance-friendly.

Narayan

 

[NARAYANK991]

Hi ,

I suggest that we go about it this way.

Can you upload a file which has the maximum amount of data , without a single formula ?

Once the data is available , in what ever format it is , we first look at whether any changes can be made as far as the data layout is concerned for the purpose that we have in mind ; a lot of times , a minor change in the data structure can give enormous benefits in data processing.

Can you explain what your data consists of ? As far as I can recollect and make out from your uploaded data file , there are the following :

Time Slots , which are of half-hour duration ; can you confirm whether this duration is fixed , or is there a possibility that it can change to say 15-minute time slots or can it be a mixture , some slots being of half-hour duration , while others can be of one-hour duration ? Think of all the possibilities.

Vehicle IDs , which identify which vehicle occupied a slot ; if a vehicle occupies more than one slot in a day , does it have the same ID ? If not , how do we associate an ID with a particular vehicle ?

The four colors RED , BLACK , GREEN , BLUE ; I am not able to recollect the significance of these colors ; can you explain , and confirm that only these 4 colors will be used ? Or is there is a possibility that more colors will be used ?

Using all of this data , what is the output expected ?

If you can give a comprehensive explanation , we can develop a proper application , which will fulfill your present objective , and accommodate any future requirements.

Narayan

 

[jeffreyweir]

Hi Nick. I think I helped you with this some time ago. Do you have the original thread where you posted this?

 

[jeffreyweir]

Ahh, found them:
http://forum.chandoo.org/threads/duration-of-stay-table-solved.11051/
http://chandoo.org/forum/threads/duration-of-stay-v2.15217/#post-94158

 

[NicGreen]

Hi All,

Thanks for your replies so far.

Jeffrey, Yes those are the links to the original post.

As requested I will provide you with a run down on each component shortly. Just a bit of background first.

Please note that while this project I am working on is for my work, I nor the business i work for, gain any monies for the project. I work for a local government department (I'm the graduate) so essentially any efficiencies created in the sheet benefit the residents I work for.

Now onto the good stuff.

The sheet basically consists of 3 areas shown below;

• Input Area
  
• Processing Area
• Output Area

The components that make up the input area are as follows

Time Slots/Intervals
Essentially within any study we look at both a Study period (i.e how many hours in a day should the study be taken) and the Study Intervals (i.e at what interval should we record vehicles within the area).

Earlier models developed were based on a 2hr intervals from 9am-5pm (i.e. 9am, 11am, 1pm etc) However as I progress into different studies the interval is changing. So to answer your question Narayank, the interval is dynamic and not fixed. Within any one excel sheet the length of the interval (i.e 30min, 1hr or 2hr etc) wont change but the spreadsheet would need to be able to cater for both different length of intervals as well as a different number of intervals.

Vehicle ID's
Each vehicle is given a unique 4 digit ID. If a vehicle changes slot it will still be given the same ID. Generally the Unique ID is a random combination of numbers and letters that is assigned to the vehicles license plate.

Colours
In the past the colours have represented different parking restrictions within the one parking area. When I complete the study I break up the study area into different sections which are all represented by an individual excel sheet. Within any parking area/ sheet there may be a combination of parking restrictions.

At this stage I have always broken up the parking areas in a way to ensure no more than 4 different restrictions exist (i.e 1hr, 2hr, loading zone, disabled bay). Colours were used as it enables the sheet to differentiate areas without having say 15 different sections set up for all the possible parking restriction types.

Within the sheet you will note in the input area there are a specific number of rows starting with each colour. Each row represents a single parking space within the parking area. I.e 10 rows of red = 10 car parks. If red was to represent a 2hr zone this would mean that within this parking area there are ten 2hr parking spaces.

Previously SirJB7 i believe worked on a VBA file for me that worked quite well, however given these studies seem to be becoming more and more complex I require the sheet to be more and more dynamic and intelligent. See attached sheet for previous work. Please note I can provide comments on this sheet as well as needed.

Now moving onto the output.

The two main objectives of the study are to determine both the Occupancy Rate (that is the percentage of car parks that are full for any given parking area) and the duration of stay (that is if a vehicle parked in the area, how long did it stay there for)

To break this down further I am also looking to determine the occupancy rate and duration of stay, within any given parking restriction (aka colour).

Then for all the different parking areas/sheets I compile the results for both occupancy rate and duration of stay.

Finally,

I understand this is quite a tough sheet. At present I have been able to work through the issues I face but as I progress the sheet requires more and more altering. I fully understand anyone's hesitation around completing the work and that is why I have been quite frank about what my background is and how any process improvements will either directly or indirectly benefit myself.

Thanks Again,