Using the LARGE function with a parameter

[andybrst]

What I am trying to do is find the last (largest) odometer reading for each vehicle, from a list of multiple vehicles with multiple records. Is this possible to do using the Large function?

The end result is to calculate the MPG for each car.

Can anyone help?

 

[Andrew Poulsom]

You can use the MAX function, but where are the vehicles and their odometer readings?

 

[andybrst]

The vehicle details and readings are on a separate sheet, imported from our fuel card provider.

The reason for me wanting to use LARGE as opposed to MAX is because sometimes the odometer reading is input incorrectly by the garage personnel. I was going to try & build an IF statement that would use the 2nd highest value if the highest value was a ridiculous figure (I have an instance where a car has done over 400,000 miles according to one garage!)

 

[Andrew Poulsom]

To give you a formula I will need worksheet and range references.

 

[andybrst]

Hi, sorry was called away on short notice.

Reg Invdate Qty Type Transdate TransMiles
AV11JMO 06/06/2011 63.1 DIESEL 02/06/2011 8773
AV11JMO 06/06/2011 66.46 DIESEL 27/05/2011 8183
AV11TYU 06/06/2011 40.33 DIESEL 28/05/2011 1276
AV60OXM 06/06/2011 64.59 DIESEL 01/06/2011 22627
DE08XFD 06/06/2011 65.6 DIESEL 31/05/2011 90559
GU11YOC 06/06/2011 36.98 DIESEL 02/06/2011 10238
GU11YOC 06/06/2011 37.89 DIESEL 01/06/2011 8967
GU11YOC 06/06/2011 59.46 DIESEL 29/05/2011 8459
HD08KNU 06/06/2011 64.28 DIESEL 02/06/2011 96774
HD08LEU 06/06/2011 56.34 DIESEL 31/05/2011 96254
HG07WDN 06/06/2011 50.41 DIESEL 31/05/2011 96124
HN57UGH 06/06/2011 58.73 DIESEL 30/05/2011 12824
HN57UGH 06/06/2011 63.09 DIESEL 02/06/2011 12586
L111SCL 06/06/2011 49.33 DIESEL 30/05/2011 15009
LL60BVF 06/06/2011 41.8 DIESEL 27/05/2011 13673
LL60BVF 06/06/2011 43.49 DIESEL 02/06/2011 14147
LM60GYB 06/06/2011 19 DIESEL 30/05/2011 22392
LM60GYB 06/06/2011 21.27 DIESEL 31/05/2011 22611
LM60GYB 06/06/2011 41.89 DIESEL 27/05/2011 22180
LM60GYB 06/06/2011 44.32 DIESEL 02/06/2011 23083
LN58WGZ 06/06/2011 63.55 DIESEL 31/05/2011 72674
LP60FTX 06/06/2011 41.03 DIESEL 31/05/2011 8619
LR08ONN 06/06/2011 65.05 DIESEL 01/06/2011 87783

 

[barry houdini]

So assuming you have regs in A2:A100 and miles in F2:F100 you can use a formula like this for the MAX for a specific reg

=MAX(IF(A$2:A$100=H2,F$2:F$100))

confirmed with CTRL+SHIFT+ENTER

where H2 contains the specific reg

To exclude unusually high values, e.g. anything over 200,000 you can amend like this

=MAX(IF(A$2:A$100=H2,IF(F$2:F$100< 200000,F$2:F$100)))