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

1. ## 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. ## 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 ?

3. ## 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...

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