Minimun & Maximum Kilometers IF

[Guest]

H
I need to calculate Kilometers traveled, Fuel used & consumption of vehicle (fleet of vehicles) but am stuck on extracting the Minimum and Maximum kilometers from column (also starting fuel or kilometers of some are zero)

In column C, I have vehicle Registration Numbers from day 1 to 31 of month, non-alphanumeric, as they arrive, some filling up 3, 7 or 25 times per month, next, I have column D, with Kilometers, then column G, with litres of fuel pumped

In column AB, I have all vehicle reg.No's (whether they used fuel or not) in alphanumeric order. In next column AC, I then extracted fuel pumped for month with formula =SUMIF(C7:C156,AB7,G7:G156), (AB7 being say reg. No. CPB249GP) next to relevant reg. No

Now I need the first & last (MIN & MAX) kilo reading from column D next to Registration No. in columns AD & AE so I can calculate fuel used and consumption

Can someone assit me please, I'm a newbie
Theuns

 

[Peo Sjoblom]

=MAX(D2200)

=MIN(D2200)

if you need the vehicle with max and min

=INDEX(AB2:AB200,MATCH(MAX(D2200),D2200,0))

do the same for min

--

Regards,

Peo Sjoblom

Hi
I need to calculate Kilometers traveled, Fuel used & consumption of

vehicle (fleet of vehicles) but am stuck on extracting the Minimum and
Maximum kilometers from column (also starting fuel or kilometers of some are
zero).

In column C, I have vehicle Registration Numbers from day 1 to 31 of

month, non-alphanumeric, as they arrive, some filling up 3, 7 or 25 times
per month, next, I have column D, with Kilometers, then column G, with
litres of fuel pumped.

In column AB, I have all vehicle reg.No's (whether they used fuel or not)

in alphanumeric order. In next column AC, I then extracted fuel pumped for
month with formula =SUMIF(C7:C156,AB7,G7:G156), (AB7 being say reg. No.
CPB249GP) next to relevant reg. No.

Now I need the first & last (MIN & MAX) kilo reading from column D next to

Registration No. in columns AD & AE so I can calculate fuel used and
consumption.

 

[Guest]

Hi Peo
Thanks for the reply, but the results are not what I need. Maybe I should redefine my question.

In Column C (C7:C156), I have the registration numbers of the vehicles as they fill up each day. They are not sorted(non-alphabetical). They are listed as the days go by for the month. {(C1:C6) is used for description etc. of column}.

In Column D (D7156), I have the odometer(speedo) readings of the vehicles, corresponding to Reg. no. in column C.

In Column AB (AB7:AB73), I have all the vehicle Reg. No's sorted in alphanumeric order.

What I now need, is to get the first(MIN) speedo reading for each Reg. No.(vehicle), and the last(MAX) for the same Reg. No. in the AF(min) & AG(max) COLUMNS.

Thus, AF7 will look up the Reg. No. next door in AB7, and then scroll through C7:C156 to find all entries for that specific Reg. No. (from AB7), then write the MIN kilos in Column AF7.
Same for MAX.

Your Formula gave me a Reg. No., not related to to MIN or MAX.
The MIN MAX formulas by themselves work, but gives the MIN for the (C7:C156) total month, I need for each individual vehicle.

Regards
Theuns


----- Peo Sjoblom wrote: -----

=MAX(D2200)
.......
if you need the vehicle with max and min

=INDEX(AB2:AB200,MATCH(MAX(D2200),D2200,0))

Peo Sjoblom

Hi
I need to calculate Kilometers traveled, Fuel used & consumption of

vehicle (fleet of vehicles) but am stuck on extracting the Minimum and
Maximum kilometers from column (also starting fuel or kilometers of some are
zero).month, non-alphanumeric, as they arrive, some filling up 3, 7 or 25 times
per month, next, I have column D, with Kilometers, then column G, with
litres of fuel pumped.in alphanumeric order. In next column AC, I then extracted fuel pumped for
month with formula =SUMIF(C7:C156,AB7,G7:G156), (AB7 being say reg. No.
CPB249GP) next to relevant reg. No.Registration No. in columns AD & AE so I can calculate fuel used and
consumption.