+ Reply to Thread
Results 1 to 10 of 10

Fleet fuel consumption

  1. #1
    Forum Contributor
    Join Date
    05-14-2019
    Location
    Alex, Egypt
    MS-Off Ver
    365
    Posts
    254

    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

    Please find the attached sheet
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Fleet fuel consumption

    Your data does not detail the milage of each trip.

    Is this data available.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Contributor
    Join Date
    05-14-2019
    Location
    Alex, Egypt
    MS-Off Ver
    365
    Posts
    254

    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. #4
    Forum Contributor
    Join Date
    05-14-2019
    Location
    Alex, Egypt
    MS-Off Ver
    365
    Posts
    254

    Re: Fleet fuel consumption

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

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    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. #6
    Forum Contributor
    Join Date
    05-14-2019
    Location
    Alex, Egypt
    MS-Off Ver
    365
    Posts
    254

    Re: Fleet fuel consumption

    i cant get the Ctrl Shift Enter part

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    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.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  8. #8
    Forum Contributor
    Join Date
    05-14-2019
    Location
    Alex, Egypt
    MS-Off Ver
    365
    Posts
    254

    Re: Fleet fuel consumption

    Ok thanks i got it, it doesnt work though
    Attached Files Attached Files

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    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. #10
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Fleet fuel consumption

    The results I checked look ok.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Fuel Consumption Calculator
    By aman1561 in forum Excel General
    Replies: 3
    Last Post: 09-29-2015, 03:00 AM
  2. Fuel Consumption
    By josephgeoffreymark in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 09-26-2014, 12:53 PM
  3. Fuel Consumption
    By mroyce in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-09-2014, 10:57 AM
  4. Fuel Consumption
    By Puerto in forum Excel General
    Replies: 4
    Last Post: 04-17-2014, 08:22 AM
  5. Fuel consumption with 5 engine.
    By einar.a.petersen in forum Excel Formulas & Functions
    Replies: 33
    Last Post: 03-21-2014, 12:25 PM
  6. Fuel Consumption Calc
    By colinface in forum Excel General
    Replies: 7
    Last Post: 04-26-2009, 01:50 AM
  7. Fuel Consumption Figures
    By Santa-D in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-15-2006, 03:20 AM

Bookmarks

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