Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Statistics (2003 sp2)

    Good morning

    I have a workbook with thousands of entries, 2 of the columns have an expected transit time and an actual time expressed as a numberfor example

    Expected Transit column = 1
    Actual transit column = 2

    One of my directors has asked this question

    Any chance of a quick excel summary given percentages of on time deliveries, percentage delivered within 1 day, 2 day, 3 day ect....?

    Any guidance on how I can achieve this would be appreciated

    Thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Statistics (2003 sp2)

    Create a third column that subtracts the expected transit time from the actual transit time.
    Next, create a pivot table based on the data table including the extra column.
    Add the difference column to the Row area, and also to the Data area.
    Excel will automatically choose Sum for the data field.
    Double-click it and select Count as summary function.
    Also click Options and select Percent of Total from the dropdown list.

    If you'd like more help, please post a small sample workbook.

  4. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Statistics (2003 sp2)

    Thanks Hans

    I have never used a Pivot Table before so I am not quite sure what it does or what is expected.

    Following your instructions I added a column to the attached example called 'delay' I highlighted M2:O75 and clicked Data and selected the Pivot table option. I then got a 3 stage wizard which I thought I had followed correctly (obviously not!!) and got this result on a new sheet which does not seem to tell me much.

    Any further advice greatly appreciated

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Statistics (2003 sp2)

    I changed the Delay column - if Expected = 1 and Actual = 3 I assume that Delay = 2, not -2.

    Here are the steps:
    - Select M1:O75.
    - Select Data | PivotTable and PivotChart Report...
    - The Pivot Table Wizard appears.
    - In Step 1, click Next >.
    - In Step 2, click Next >.
    - In Step 3, click Layout...
    - Drag the Delay button to the ROW area.
    - Drag the Delay button to the DATA area.
    - Excel will automatically create Sum of Delay.
    - Double-click the Sum of Delay button.
    - Choose Count as summary function; the name will change to Count of Delay.
    - If you wish, you can change the name.
    - Click Options >>.
    - From the Show data as dropdown, select % of Total.
    - Click OK to close the Pivot Table Field dialog.
    - Click OK to close the Layout dialog.
    - You can now specify whether you want the pivot table in a new sheet (the default).
    - Alternatively, you can select Existing sheet and select a cell to act as upper left corner.
    - Finally, click Finish.

    Note: pivot tables aren't updated automatically when the data change. Click anywhere in the pivot table, then click the Refresh Data button on the Pivot Table toolbar (the exclamation mark) or select Data | Refresh Data.

    See attached version.
    Attached Files Attached Files

  6. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Statistics (2003 sp2)

    Hans,

    Very clever. This approach allows you also to make a quick frequency count of items occuring in a single column (e.g. a long time series of riverflows)

    Thanks and regards,

    Teunis

  7. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Statistics (2003 sp2)

    Fantastic, Thanks Hans

    That helped me out of a hole

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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