Gareth, per your PM I have tried to come up with something for you.
My problem is I'm not sure how/where you were hoping to do the calculations... ie were you analysing a given table in detail or trying to return the results to say 2 cells (v. difficult using formulae -- easier with UDF)
Based on native formulae & analysis of a given timeframe in full I came up with the attached... not very elegant however and moreover I'm not convinced it's what you're trying to achieve.
On 'Frequent Service' sheet I added an additional column to create a Key based on Date / Location & Start Time... this makes subsequent calculations a little easier.
I created a 2nd sheet called 'Frequent Service Analysis' on which you would alter the following cells:
So if you wanted to analyse a subsequent table you would alter the above cells accordingly.
The subsequent table would then list the 60 periods and the results - ie count of buses and count of > 15 min intervals (based on Actual time column (ie value in I falls within period boundaries and J exceeds threshold))
It's very difficult to know what you want given you don't offer an expected results section... I appreciate it's difficult re: uploading files but it is equally difficult & time consuming for us to try and replicate your screen shots so as to generate a sample.
If you want the above condensed into 2 cells (ie one for count of periods where count of buses > 6 and another to hold late count) then let us know... but to reiterate I think VBA would be required to ease the pain so we need to know that (VBA) is viable before pursuing further.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks