Results 1 to 8 of 8

Thread: Visual Display of Vehicle Numbers over Three Years

  1. #1
    Neophyte IanDGreen's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2007

    Visual Display of Vehicle Numbers over Three Years



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Everyone,

    I am attempting to visually illustrate the fluctuation of vehicle movement to aid a Disabled Driver Parking problem.

    The data involves ----

    (a) vehicles present (or not) at two different locations. (Upper and Main)
    (b) number of vehicles recorded at half-hourly intervals throughout each (24hr) day of the week. (An Upper and a Main value (even zero) required at each interval)
    (c) 2D-Column graph to display the comparative variations/concentrations over a three-year period. (Upper and Main per Half-Hour x 24 hours x 7 days x 3 years.)

    Quite honestly I haven't a clue how to achieve it and my brain has gone dead after compiling all the data.

    I'd be grateful if someone could point me in the right direction for graphing that lot please.

    Thank you,

    Ian

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,612
    Articles
    0
    Excel Version
    365
    Sounds complicated.
    A good answer would need a representative file from you to be attached.

  3. #3
    Neophyte IanDGreen's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2007
    Hi p45cal,

    Thanks for responding to this problem.

    You are quite right -- it is complicated.

    However, realising its complexity, I am currently working out how the data can become more "finely tuned", hopefully resulting in a reduction in possible Groups and Comparators.

    I don't have a file ready just yet and don't know whether I should close down this thread and return with a new enquiry at a later date.

    What would you suggest please?

    Ian

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,612
    Articles
    0
    Excel Version
    365
    Continue with this thread, it's not that old, I'm subscribed to it so I'll be alerted when a new message appears in it.

  5. #5
    Neophyte IanDGreen's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2007

    Sample Data File as Requested

    Hi p45cal,
    Herewith the example data for the Disabled Parking analysis/illustration I am trying to produce.


    There are worksheets similar to this one for each day of the week and, in an ideal world, I would like to display a comparison/graph to incorporate all days at once.



    To try and reduce the "times" for recording vehicle presence, I have produced TimeZones so, over a period of 24 hours, there are now only 6 Zones involved. (A19-C:25)



    Although there is an obvious distinction between numbers in a "Main" and "Upper" location, there is no comparison necessary as they do not compete.



    I am hoping it will be possible to display --



    {On Monday 12th September, in 2016, in TimeZone 5, in the "Main" location, 7 vehicles were present, whilst on the same date, in the same TimeZone, in the "Upper" location only 3 were present (or no data available)} with a matching display for 2017 & 2018 too --- the whole thing being repeated to cover the complete calendar year for the three year span.



    I realise there is an enormous amount of data to be displayed and, if necessary, the graphing could be reduced to displaying one month at a time over the three year span.



    Hope it all makes sense?

    Best regards,

    IanSample_Data_Formula_Disabled_Parking_10092018_001.xlsx

  6. #6
    Neophyte IanDGreen's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2007
    Hi p45cal,
    I hope this doesn't turn out to be a duplication for I thought I sent this reply yesterday -- but will now assume it is down to my grey cells weakening that gave me the thought.

    Herewith the example data for the Disabled Parking analysis/illustration I am trying to produce.


    There are worksheets similar to this one for each day of the week and, in an ideal world, I would like to display a comparison/graph to incorporate all days at once.

    To try and reduce the "times" for recording vehicle presence, I have produced TimeZones so, over a period of 24 hours, there are now only 6 Zones involved. (A19-C:25)

    Although there is an obvious distinction between numbers in a "Main" and "Upper" location, there is no comparison necessary as they do not compete.

    I am hoping it will be possible to display --

    {On Monday 12th September, in 2016, in TimeZone 5, in the "Main" location, 7 vehicles were present, whilst on the same date, in the same TimeZone, in the "Upper" location only 3 were present (or no data available)} with a matching display for 2017 & 2018 too --- the whole thing being repeated to cover the complete calendar year for the three year span.

    I realise there is an enormous amount of data to be displayed and, if necessary, the graphing could be reduced to displaying one month at a time over the three year span.

    Hope it all makes sense?
    Attached Files Attached Files

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,612
    Articles
    0
    Excel Version
    365
    I've only just seen your post - it was in moderation and I don't know why!
    I'll try to get time to look at it soonest. Can you confirm you haven't already solved it?

    In the meantime, a taster, attached is your file with a copy of your sheet with the data re-arranged with a bit of cut and pasting (this could be automated too) and a pivot table in the vicinity of cell H1.
    I think you're using Excel 2007 so there are interesting additions since that version (slicers) that could make it more user-friendly to filter your data within the pivot table, but the possibilities with how the data are shown are huge.

    I don't see how you get: {On Monday 12th September, in 2016, in TimeZone 5, in the "Main" location, 7 vehicles were present, whilst on the same date, in the same TimeZone, in the "Upper" location only 3 were present (or no data available)} because your raw data don't support that.
    I've left the pivot table filtered for those criteria and I only get 3 vehicles in the 'Main' section.
    Last edited by p45cal; 2018-09-24 at 07:26 PM.

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,612
    Articles
    0
    Excel Version
    365
    oh well… looks like IanDGreen lost interest.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •