smartbhau

New Member
Joined
Aug 4, 2014
Messages
11
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 NumberTransaction DateOdometer Reading
ABC00031/07/2014124204
ABC00001/08/201492278
ABC00002/08/2014124885
ABC00006/08/2014125511
ABC00006/08/201492887
ABC00012/08/201493271
ABC00013/08/2014126178
ABC00018/08/2014126807
ABC00019/08/201493966
ABC00020/08/2014127419
ABC00022/08/2014128054
ABC00022/08/201494647
ABC00027/08/2014128725
ABC00029/08/2014129298
ABC12301/08/2014183413
ABC12306/08/2014183834
ABC12307/08/2014184252
ABC12312/08/2014184684
ABC12313/08/2014185191
ABC12315/08/2014185655
ABC12320/08/2014186096
ABC12321/08/2014186529
ABC12322/08/2014186901
ABC12325/08/2014187385
ABC12327/08/2014187743
ABC12328/08/2014188110
XYZ00001/08/2014148479
XYZ00007/08/2014148198
XYZ00010/08/2014149532
XYZ00013/08/2014150084
XYZ00018/08/2014150888
XYZ00020/08/2014151456
XYZ00025/08/2014152033
XYZ00027/08/2014153277
XYZ00029/08/2014152524
XYZ12331/07/201429370
XYZ12304/08/201429980
XYZ12307/08/201430657
XYZ12312/08/201431214
XYZ12315/08/201431842
XYZ12319/08/201432452
XYZ12320/08/201433051
XYZ12322/08/201433624
XYZ12325/08/201434119
XYZ12326/08/201434668
XYZ12327/08/201435186

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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?
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top