# Fleet fuel consumption

1. ## Fleet fuel consumption

Hello dears

I want to calculate the fuel consumption for trucks per trip; however, I get the fuel data only when the truck refill the tank

So, i have 2 tables

First table (first tab) shows the truck No., date of refill and the consumption rate in the period between the last 2 refills
Second table (Second tab) shows trucks and date of trips

N.B. i calculate the consumption ( KM/ Liter) by dividing the difference between the current km counter and the previous km counter by the current gasoline refill quantity, so at each refill i calculate the consumption between the current and the previous refill

What i need to do is to allocate the consumption to trucks by trip, however not every trip date has a refill transaction on that date
so i want to take the consumption for the trip by checking in which on period it exist (period between 2 refill transactions) and allocate the consumption

2. ## Re: Fleet fuel consumption

Your data does not detail the milage of each trip.

Is this data available.

3. ## Re: Fleet fuel consumption

No the milage of each trip isnt available; however, i want to assign only a rete (km/L) which is available at every refilli for the period between the current refill and the previous refill.
so if the trip date is within this period it can take this rate which is available

4. ## Re: Fleet fuel consumption

I want to assign only the rate (km/l) not the mileages or the quantity of the refill

5. ## Re: Fleet fuel consumption

Try this formula in C2. Must be entered using Ctrl Shift Enter.

=INDEX('Fuel Fillings'!F:F,MAX(IF(INDIRECT("'Fuel Fillings'!\$B\$1:B"&MATCH([@[Trip Date]],'Fuel Fillings'!A:A))=[@Truck],ROW(INDIRECT("'Fuel Fillings'!\$B\$1:B"&MATCH([@[Trip Date]],'Fuel Fillings'!A:A))),0)))

6. ## Re: Fleet fuel consumption

i cant get the Ctrl Shift Enter part

7. ## Re: Fleet fuel consumption

What he means is that you paste that formula where you want it, then in the formula bar, put your cursor in the formula then hit the ctrl/shift/enter keys all at the same time, {} will appear on each side of the formula which will "activate" it. You CANNOT put those braces in the formula, you have to "activate" the formula using the ctrl/shift/enter keys.
hope that helps.

8. ## Re: Fleet fuel consumption

Ok thanks i got it, it doesnt work though

9. ## Re: Fleet fuel consumption

I'm not sure what Mehmetcik gave you but when I paste it into the trips tab cell C2 then activate it, it returns some #N/A and some values.
from what I can see those that return a value (like TR36) are returning the first value it finds in fuel filings that matches both the truck and the date. There are three TR36 in fuel filings and four in Trips.
the values returned are those that match 30-Sep-19 and stay with that date until the next date match which is 9-Oct-19.
So TR18 doesn't initially match (row 2 in Trips) but does after that because the dates of Oct 7, 8 and 9 are all on or after the date in the trips tab.

10. ## Re: Fleet fuel consumption

The results I checked look ok.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1