Returning MIN/MAX value with a twist

[MugsyAU]

Hi all,

I have a very large table which has a lot of fuel usage data for a fleet of vehicles. What I need to do is to create two formulas which, for every Registration number, return the highest and lowest odometer reading for each registration number. What I will need to filter out though is where odometer readings end up being '0' (either because the row is recording a card surcharge, or there was no odometer reading recorded for that fuel purchase). An example of the data I am working with is as follows (there's more columns with info such as cost spent, number of litres purchased, vendor driver, etc but shouldn't be needed to make the formulas work):

Reg Numbr	Odometer	Product
ABC111	1000	Diesel
ABC111	1498	Diesel
ABC111	1944	Diesel
ABC111	0	Card Admin Charge
ABC222	20000	Unleaded
ABC222	20500	Unleaded
ABC222	21020	Unleaded
ABC222	0	Unleaded

What I want the formulas to return is as per columns 4 and 5 below:

Reg Numbr	Odometer	Product	1st odometer reading	Last odometer reading
ABC111	1000	Diesel	1000	1944
ABC111	1498	Diesel	1000	1944
ABC111	1944	Diesel	1000	1944
ABC111	0	Card Admin Charge	1000	1944
ABC222	20000	Unleaded	20000	21020
ABC222	20500	Unleaded	20000	21020
ABC222	21020	Unleaded	20000	21020
ABC222	0	Unleaded	20000	21020

I've been playing with combinations of VLOOKUP, MIN, MAX, MATCH, INDEX without success as yet.

[rylo]

Hi

D2: =MIN(IF(($A$2:$A$9=A2)*($B$2:$B$9<>0),$B$2:$B$9,""))
E2: =MAX(IF($A$2:$A$9=A2,$B$2:$B$9,""))

Both these formulas have to be array entered (ctrl, shift, enter)

Copy down as required.

rylo

[alansidman]

You will need to combine Max and Min with an If function. Look here for examples: http://blog.contextures.com/archives...x-if-in-excel/

[MugsyAU]

Thanks mate. Worked like a charm.