Pivot Table Problem

[Peter Hindley]

This may sound like an easy question but it has been
puzzling me for days!!
Any Help on this would be great

3 meter numbers, All different readings taken on
different dates. On each visit all 3 are read. I would
like to be able to have a pivot table where the
difference between the readings taken is shown. Based on
chronological order following the date read column to
sort by.

My data will look like this -

Column A Column B Column C Column D
Little Richard SP C61L 00578 15/12/2004 63587
Little Richard SP C66L 12364 15/12/2004 63033
Little Richard SP C66L 09812 15/12/2004 95529
Little Richard SP C61L 00578 15/01/2004 63597
Little Richard SP C66L 12364 15/01/2004 63061
Little Richard SP C66L 09812 15/01/2004 95568
Little Richard SP C61L 00578 15/02/2004 63647
Little Richard SP C66L 12364 15/02/2004 63172
Little Richard SP C66L 09812 15/02/2004 95754

I need the output of days difference between readings on
each meter. And a total difference between the current
and last reading for the corresponding meter number.

So for the above I would like to show 31 days for the
difference on all meter numbers and then for each meter
number a difference of

C61L 00578 - 10
C66L 12364 - 28
C66L 09812 - 39

C61L 00578 - 50
Etc..

Then to calculate the amount used per day for each meter
number

Peter

 

[Bernie Deitrick]

Peter,

Below is the best you'll be able to do in one view.

Sum of Column D Column C
Column A Column B 12/15/2004 1/15/2005 2/15/2005
Little Richard SP C61L 00578 10 60
C66L 09812 39 225
C66L 12364 28 139


If you right click on the Sum of Column D button, you can choose "Field
Settings", then the Options... button, and then use "Show Data", select
"Difference From" and then "Column C" (as the base field), and choose the
12/15/2004 date. Unfortunately, you cannot use successive columns as the
base field. But you can change the base field date to get successive values,
if you don't mind a bit of manual work. (I was guessing that the dates in
column C are DD/MM/YYYY, and that you meant to use 2005 for the last 6
dates..... Note that my dates are M/DD/YYYY, due to my settings)

HTH,
Bernie
MS Excel MVP

 

[Debra Dalgleish]

Set up a pivot table with Name and Meter Number in the row area, Date in
the column area, and Reading in the data area.

Drag another copy of Date to the data area

Right-click on the Date heading in the data area
Choose Field Settings
Click the Options button
From the 'Show data as' dropdown, choose 'Difference from'
For the base field, select Date.
For the base item, select (previous)
Click OK

Right-click on the Reading heading in the data area
Choose Field Settings
Click the Options button
From the 'Show data as' dropdown, choose 'Difference from'
For the base field, select Date.
For the base item, select (previous)
Click OK