# Pivot Table Calculations

#### smartbhau

##### New Member
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.

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

<tbody>
</tbody>

<tbody>
</tbody>

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### smartbhau

##### New Member
Forgot to Mention, I am using Excel 2010.

#### Fazza

##### MrExcel MVP
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

##### New Member

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

##### Board Regular
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

##### MrExcel MVP
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

Code:
``````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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,279
Messages
5,836,367
Members
430,424
Latest member
ALHK022

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back