Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Problem
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 "Peter Hindley" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Problem
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 Peter Hindley wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem inserting calculated pivot fields into Pivot Table (2007) | Excel Discussion (Misc queries) | |||
Pivot Table in Excel 2007 : Problem with % in Table | Excel Discussion (Misc queries) | |||
Pivot table problem... | Excel Worksheet Functions | |||
Pivot Table problem | Excel Programming | |||
Pivot Table Problem | Excel Programming |