Pivot Table Calculations

[smartbhau]

Hi Guys,

I have about 600 rego numbers for which I need to figure out below.
1) Difference between minimum & maximum odo reading for unique rego numbers
2) Days between minimum & maximum odo reading for unique rego numbers
Below is an example of my data with 4 rego numbers
I thought Pivot table would work best with Min Max, but I can not figure out formula for the calculated field.
I tried =MAX('Odometer Reading')-MIN('Odometer Reading'), with Rego Number as my Row Label.

[TABLE="width: 269"]
<tbody>[TR]
[TD][TABLE="width: 430"]
<tbody>[TR]
[TD]Registration Number[/TD]
[TD]Transaction Date[/TD]
[TD]Odometer Reading[/TD]
[/TR]
[TR]
[TD]ABC000[/TD]
[TD]31/07/2014[/TD]
[TD]124204[/TD]
[/TR]
[TR]
[TD]ABC000[/TD]
[TD]01/08/2014[/TD]
[TD]92278[/TD]
[/TR]
[TR]
[TD]ABC000[/TD]
[TD]02/08/2014[/TD]
[TD]124885[/TD]
[/TR]
[TR]
[TD]ABC000[/TD]
[TD]06/08/2014[/TD]
[TD]125511[/TD]
[/TR]
[TR]
[TD]ABC000[/TD]
[TD]06/08/2014[/TD]
[TD]92887[/TD]
[/TR]
[TR]
[TD]ABC000[/TD]
[TD]12/08/2014[/TD]
[TD]93271[/TD]
[/TR]
[TR]
[TD]ABC000[/TD]
[TD]13/08/2014[/TD]
[TD]126178[/TD]
[/TR]
[TR]
[TD]ABC000[/TD]
[TD]18/08/2014[/TD]
[TD]126807[/TD]
[/TR]
[TR]
[TD]ABC000[/TD]
[TD]19/08/2014[/TD]
[TD]93966[/TD]
[/TR]
[TR]
[TD]ABC000[/TD]
[TD]20/08/2014[/TD]
[TD]127419[/TD]
[/TR]
[TR]
[TD]ABC000[/TD]
[TD]22/08/2014[/TD]
[TD]128054[/TD]
[/TR]
[TR]
[TD]ABC000[/TD]
[TD]22/08/2014[/TD]
[TD]94647[/TD]
[/TR]
[TR]
[TD]ABC000[/TD]
[TD]27/08/2014[/TD]
[TD]128725[/TD]
[/TR]
[TR]
[TD]ABC000[/TD]
[TD]29/08/2014[/TD]
[TD]129298[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]01/08/2014[/TD]
[TD]183413[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]06/08/2014[/TD]
[TD]183834[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]07/08/2014[/TD]
[TD]184252[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]12/08/2014[/TD]
[TD]184684[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]13/08/2014[/TD]
[TD]185191[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]15/08/2014[/TD]
[TD]185655[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]20/08/2014[/TD]
[TD]186096[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]21/08/2014[/TD]
[TD]186529[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]22/08/2014[/TD]
[TD]186901[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]25/08/2014[/TD]
[TD]187385[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]27/08/2014[/TD]
[TD]187743[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]28/08/2014[/TD]
[TD]188110[/TD]
[/TR]
[TR]
[TD]XYZ000[/TD]
[TD]01/08/2014[/TD]
[TD]148479[/TD]
[/TR]
[TR]
[TD]XYZ000[/TD]
[TD]07/08/2014[/TD]
[TD]148198[/TD]
[/TR]
[TR]
[TD]XYZ000[/TD]
[TD]10/08/2014[/TD]
[TD]149532[/TD]
[/TR]
[TR]
[TD]XYZ000[/TD]
[TD]13/08/2014[/TD]
[TD]150084[/TD]
[/TR]
[TR]
[TD]XYZ000[/TD]
[TD]18/08/2014[/TD]
[TD]150888[/TD]
[/TR]
[TR]
[TD]XYZ000[/TD]
[TD]20/08/2014[/TD]
[TD]151456[/TD]
[/TR]
[TR]
[TD]XYZ000[/TD]
[TD]25/08/2014[/TD]
[TD]152033[/TD]
[/TR]
[TR]
[TD]XYZ000[/TD]
[TD]27/08/2014[/TD]
[TD]153277[/TD]
[/TR]
[TR]
[TD]XYZ000[/TD]
[TD]29/08/2014[/TD]
[TD]152524[/TD]
[/TR]
[TR]
[TD]XYZ123[/TD]
[TD]31/07/2014[/TD]
[TD]29370[/TD]
[/TR]
[TR]
[TD]XYZ123[/TD]
[TD]04/08/2014[/TD]
[TD]29980[/TD]
[/TR]
[TR]
[TD]XYZ123[/TD]
[TD]07/08/2014[/TD]
[TD]30657[/TD]
[/TR]
[TR]
[TD]XYZ123[/TD]
[TD]12/08/2014[/TD]
[TD]31214[/TD]
[/TR]
[TR]
[TD]XYZ123[/TD]
[TD]15/08/2014[/TD]
[TD]31842[/TD]
[/TR]
[TR]
[TD]XYZ123[/TD]
[TD]19/08/2014[/TD]
[TD]32452[/TD]
[/TR]
[TR]
[TD]XYZ123[/TD]
[TD]20/08/2014[/TD]
[TD]33051[/TD]
[/TR]
[TR]
[TD]XYZ123[/TD]
[TD]22/08/2014[/TD]
[TD]33624[/TD]
[/TR]
[TR]
[TD]XYZ123[/TD]
[TD]25/08/2014[/TD]
[TD]34119[/TD]
[/TR]
[TR]
[TD]XYZ123[/TD]
[TD]26/08/2014[/TD]
[TD]34668[/TD]
[/TR]
[TR]
[TD]XYZ123[/TD]
[TD]27/08/2014[/TD]
[TD]35186[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[smartbhau]

Forgot to Mention, I am using Excel 2010.

 

[smartbhau]

Can some 1 please help!!!
Forgot to mention Please

 

[Fazza]

hi,

maybe quickest to set up is rego number as row field (LHS)

data fields, TWICE each, date and odometer
for each of date and odometer, one instance set to MIN and other to MAX

outside pivot table have formulas to give MAX-MIN for each of dates and readings

OK?

 

[smartbhau]

maybe quickest to set up is rego number as row field (LHS)

data fields, TWICE each, date and odometer
for each of date and odometer, one instance set to MIN and other to MAXOutside pivot table have formulas to give MAX-MIN for each of dates and readings

Hi Fazza, Thanks for your help. Work's, but not the ideal way of doing it as my data would change every month.
So wanted to see if there was any other way of doing it. I will try some formula's to see if it works that way.

Regards,

Mike

 

[ankita.sethi]

Hi,
Don't know if you have got the solution or not. But this is how I would do it -

Create a column to get the unique list of Registration Numbers (I did this in column G)
=INDEX($A:$A,MATCH(0,INDEX(COUNTIF($G$1:G1,$A:$A),0,0),0))
Drag this formula down to a couple of rows (let some #N/A's be there as they would convert into data if next time you have more values)


In the next column (column H) I would put max values by using array formulae -
{=MAX(IF($A$2:$A$47=G2,$C$2:$C$47))}
(use Ctrl+Shift+enter to complete this array formulae)

Put this formulae on the required number of rows.

Similarly I would bring min values and then calculate difference between them.

And then, moving on to find the number of days, use index and match to find corresponding values and then subtract.

Let me know if this helps or if you find a better solution yourself as well

 

[Fazza]

I've been busy at work lately; hence my delay in replying. What I offered initially was perhaps the easiest solution & I appreciate that may not be ideal. Though changing data each month I'd expect wouldn't be onerous.

Maybe you'd prefer to include SQL into the solution to achieve the result directly without formulas?

So either a query table (external data query), or pivot table if you prefer, using

SELECT [Registration Number], MAX([Transaction Date]) - MIN([Transaction Date]) AS [Days], MAX([Odometer Reading]) - MIN([Odometer Reading]) AS [Distance]
FROM [SheetName$]
GROUP BY [Registration Number]

Some steps to set it up. (Briefly.) Save data file. Open new file, start pivot table wizard ALT-D-P choosing external data at first step, follow wizard. Once you've picked your data file if there is a message about no visible tables choose 'options' then 'system tables' to see worksheet names. (I'm assuming data headers in row 1 & data under incontiguous block. If not, no problem btw.) At end of wizard choose option to edit in MS Query. Edit SQL to above - it is a simple text replacement. OK to see dataset, exit MS Query via 'open door' icon & finish pivot table.

regards