Statistics (2003 sp2)

[stevehocking]

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

[HansV]

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.

[stevehocking]

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

[HansV]

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.

[Teunis]

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

[stevehocking]

Fantastic, Thanks Hans

That helped me out of a hole

Cheers

Steve