Fighting with Excel...

Is anybody handy with Microsoft Excel?  I have a feeling I'm fighting with it, because I normally program any data manipulation that I do in Java or C++.  I don't want to use scripting unless I have to, since the point of using Excel is that it *should* be more understandable to non-programmers.

I'm trying to figure out Pivot Tables, but only because that's what I think I need.

I have data in this format... let's say they're deliveries (they aren't).

Time,Amount of X,Amount of Y,Amount of Z,
03:40,2,0,4,
03:50,0,5,5,
03:50,0,5,0,
05:30,0,0,5,
06:40,2,0,6,
06:40,0,7,7,
06:50,0,8,0,
07:00,0,9,0,
07:00,3,0,7,
07:20,0,0,8,

And what I want is the sum of each Amount, for each 10 minute period between 3:40 and 7:20.  Notice that there are often two deliveries at the same time, but I'm interested in the total delivery at a given time.

In Excel I have added dummy rows for each 10min interval in my time range...

03:30,0,0,0,
03:40,0,0,0,
...etc...
07:10,0,0,0,
07:20,0,0,0,

And now I'm using that whole table as the source of a Pivot Table, with Time as the Row header, and "Sum of Amount of X/Y/Z" as three separate items in the data area.

But now I get...

Time,Data,Total,
03:30,Sum of Amount of X,0,
,Sum of Amount of Y,0,
,Sum of Amount of Z,0,
03:40,Sum of Amount of X,2,
,Sum of Amount of Y,0,
,Sum of Amount of Z,4,
03:50,Sum of Amount of X,0,
,Sum of Amount of Y,10,
,Sum of Amount of Z,5,
04:00,Sum of Amount of X,0,
,Sum of Amount of Y,0,
,Sum of Amount of Z,0,
etc...

When I'd actually like...

Time,Sum of Amount of X,Sum of Amount of Y,Sum of Amount of Z,
03:30,0,0,0,
03:40,2,0,4,
03:50,0,10,5,
04:00,0,0,0,
etc...

Does anybody have any suggestions?  I'll buy you an e-beer or something :)

Tom (a programmer)
Tuesday, February 3, 2004

Drag the "Data" pivot heading up and right a little, and let go. Excel will get the message that the entries are column headings.

A.T.
Tuesday, February 3, 2004

Yep, dragging "Data" over to where it said "Total" did the trick.

Thanks very much. 

Since you guys seem interested in these sorts of problem, you might be interested to know the next step in my problem is offsetting these values with a s-curve style distribution (in this example, each delivery is likely to be early by some predictable amount).

e.g. For each delivery type (X/Y/Z) I have a profile like this...

Time Minus,Proportion Arrived,
00:00,100%,
00:10,98.50%,
00:20,80.2%,
etc...
01:50,10%,
02:00,0%,

Last time I tried it, Excel said my formula was too long... I'll probably be back in a minute :)

Tom (a programmer)
Tuesday, February 3, 2004

I'm not completely sure what you're trying to do, but here's one approach that occurs to me. It's probably best to do the cumulative and percentage calculations before the pivot table. Also, simplify the calcuation by including extra columns for each stage: you can always hide them for printing/viewing. Sort the original data by 'time minus', calculate the cumulative deliveries in another set of columns, calcuate the percentages of the totals in a third set of columns.

A.T.
Tuesday, February 3, 2004

  Recent Topics

  Fog Creek Home