Forum Discussion

vitoaiaco's avatar
vitoaiaco
Brass Contributor
Sep 24, 2024

Calculation of truck consumption

Hi, 

I'm kindly asking for your help in setting up the correct formula for the attached spreadsheet.

You will find in column A a list of trucks identified by fleet numbers. On a daily basis new columns are added to manually input information on diesel volumes erogated (Column D = "Diesel Ltrs") and a record of the mileage (Column E = "KM").

 

I need a formula that can find and subtract the latest and penultimate KM values for each truck details entered to be calculate the fuel consumption (km per litre) on the last trip performed by the truck. You will find a manually entered calculation in column C in the attached file for the first truck.

 

Your help would be highly appreciated,

 

Many Thanks

Vito

excel
Formulas and Functions
office 365
  • m_tarler's avatar
    m_tarler
    Sep 24, 2024

    vitoaiaco  first I must say I'm not a fan of you sheet set up. 

    a) It appears you insert columns to enter the latest data which can/will throw problems into calculation.

    b) you are using columns instead of rows (you have many more rows than columns)

    c) you are grouping data/columns instead of having individual entries.

    that all said the following formula will work:

    =LET(miles, FILTER(D6:AAA6,(D$3:AAA$3="KM")*(D6:AAA6<>"")), 
     gas, FILTER(D6:AAA6,(D$3:AAA$3="Diesel Ltrs")*(D6:AAA6<>"")), 
     (INDEX(miles,1)-INDEX(miles,2))/INDEX(gas,2))

    or you could use INDEX or CHOOSECOLS instead of FILTER

    That said, you may want or need to add a 'dummy' column D so the formula wont get shifted when you insert the columns.

    Lastly I question your calculation because I always do current milage - last milage / how much I need to fill the tank NOW as how much I used which assumes I top the tank off.  Your calculation using the prior gas amount works if you run the engine dry each time (which I sencerely doubt) or I'm just misunderstanding the data.

     

  • m_tarler's avatar
    m_tarler
    Steel Contributor
    Sep 24, 2024

    vitoaiaco  first I must say I'm not a fan of you sheet set up. 

    a) It appears you insert columns to enter the latest data which can/will throw problems into calculation.

    b) you are using columns instead of rows (you have many more rows than columns)

    c) you are grouping data/columns instead of having individual entries.

    that all said the following formula will work:

    =LET(miles, FILTER(D6:AAA6,(D$3:AAA$3="KM")*(D6:AAA6<>"")), 
     gas, FILTER(D6:AAA6,(D$3:AAA$3="Diesel Ltrs")*(D6:AAA6<>"")), 
     (INDEX(miles,1)-INDEX(miles,2))/INDEX(gas,2))

    or you could use INDEX or CHOOSECOLS instead of FILTER

    That said, you may want or need to add a 'dummy' column D so the formula wont get shifted when you insert the columns.

    Lastly I question your calculation because I always do current milage - last milage / how much I need to fill the tank NOW as how much I used which assumes I top the tank off.  Your calculation using the prior gas amount works if you run the engine dry each time (which I sencerely doubt) or I'm just misunderstanding the data.

     

    • vitoaiaco's avatar
      vitoaiaco
      Brass Contributor
      Sep 24, 2024
      Thank you! This works perfectly!

      Much appreciated
  • Steve_SumProductCom's avatar
    Steve_SumProductCom
    Iron Contributor
    Sep 24, 2024

    vitoaiaco I also think it would be easier if you set up your sheet a different way.  The Dates being in the header in a merged cell is a problem.

     

    A better way would be to set up a table like this:

    Then you can calculate the previous KM and Liters using a formula such as (the 5 near the end means to get the KM column).  Change it to a 4 to get the Liters.

    =IFERROR(INDEX(FILTER([Miloto No]:[KM],([Date]<[@Date])*([Truck No.]=[@[Truck No.]])),1,5),"")

     

    =IFERROR(INDEX(FILTER([Miloto No]:[KM],([Date]<[@Date])*([Truck No.]=[@[Truck No.]])),1,4),"")

     

    Calculate the Consumption as:

    =IFERROR(([@KM]-[@[Previous KM]])/[@[Previous Diesel Ltrs]],"")

Resources