+ Reply to Thread
Results 1 to 3 of 3

Calculating complex Fuel Cost based on Individual Name, Engine CC, Fuel Type and Date.

  1. #1
    Registered User
    Join Date
    12-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    19

    Question Calculating complex Fuel Cost based on Individual Name, Engine CC, Fuel Type and Date.

    Good Day excellians.

    So I am making a spreadsheet to attempt to make our fundraising activities more efficient and profitable. However, I have hit another wall in regard to facilitating an auto-calculating, auto updating fuel cost calculator. It gets quite complicated...

    test workbook.xlsx

    'Overview':
    The table shows fuel prices per mile, based on fuel type, engine CC and date. The fuel prices are based on government advisory costs which change quarterly. I would like to be able to add new entries in every quarter in a new row, and have the current fuel cost update based on the current date.

    'Accounts':
    This is where information is entered upon the completion of our fundraising, with ever two columns constituting one teams entry. The rows in red in column A are the only significant entries for this problem. Notice the "fundraiser" and Driver" rows have drop down lists for the drivers names.

    'Drivers':
    This is where the drivers details are stored, including their engine cc and fuel type. Notice that column A is populated based on the criteria in column E of 'Active' or 'Inactive'. This populates the drop-down list in the 'Accounts' sheet.


    PRIMARY PROBLEM:
    The issue that is perplexing me is how to calculate the fuel cost in the sheet 'Accounts', row 28.

    It should calculate the total cost of fuel based on the mileage (Accounts, R5), Driver Name and correlating engine CC+Fuel type, the correlating Cost-per-Mile (Overview Table), all ultimately dependent on the date.

    As the fuel prices would be updated in new rows on the fuel price table, the formula would need to change based on dates.



    I have been struggling to find a solution but its perplexed me. Perhaps I am misguided and there is a simpler solution? Any help would be enormously appreciated!!


    (Minor Problem):
    When setting up the dropdown lists, I check ignore blanks in the data validation box, but the dropdown still has blanks. Anyone know why?


    Thanks for reading, and thanks in advance to anyone who may be able to help!

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Calculating complex Fuel Cost based on Individual Name, Engine CC, Fuel Type and Date.

    Before we get into the Excel aspects of this problem we need to get back to fundamentals.. Do you have an algebraic equation for fuel costs or are you talking a statistical approach ?
    Elegant Simplicity............. Not Always

  3. #3
    Registered User
    Join Date
    12-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Calculating complex Fuel Cost based on Individual Name, Engine CC, Fuel Type and Date.

    Hi AndyLitch,

    If I am understanding you correctly, I think the formula terms would be:

    Driver Name, Engine CC, Fuel Type, and date = fuel price per mile (based on the table in 'Overview')

    Fuel price per mile X mileage = Fuel cost


    I have been trying to research a way to first bring in the different criteria to determine the price per mile, which is above my level, but then to introduce the dates too. jeez...

+ 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. Calculation Fuel cost automatic
    By yukioh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-25-2013, 10:05 PM
  2. [SOLVED] Fuel type fill base on kind of fuel.
    By yukioh in forum Excel General
    Replies: 2
    Last Post: 01-18-2013, 02:12 AM
  3. [SOLVED] Use of Imbedded formulas including VLOOKUP, INDEX and MATCH to determine fuel cost.
    By haloperidal in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-22-2012, 03:19 AM
  4. Calculating a fuel surcharge
    By hackbrew in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-23-2008, 10:00 AM
  5. fuel log calculating...need help
    By valdostaflying in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-02-2006, 03:23 PM

Tags for this Thread

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