Formula to calculate Fuel Consumption

**Biancodi** · 02-02-2008, 02:52 PM

I need some help in calculating automatically the fuel consumption per 100 kms in a worksheet that contains 31 rows (days of the month) and four columns as follows:
1. Date (representing each day of the month)
2. Fuel Tank filling in litres (usually shows 3-4 fillings in different days of the month)
3. Kilometres Reading at the moment of fuel tank top-up
4. The formula column, which needs to calcualate automatically the fuel consumption/100 kms whenever there are new entries in columns 2 and 3 as shown above.

Normally to calculate the fuel consumption/100 kms is:
x= litres x 100 kms / kms done

The kms done is calcualted by deducting the Current Kms Reading from the previous Kms Reading.

I have attached a zipped excel file as an example.

I would appreciate your help

Thanx

**shg** · 02-02-2008, 03:25 PM

I'd do it like this:

Please Login or Register  to view this content.

The formula in D3 and copied down is =100 * C3 / (B3 - B2)

**Biancodi** · 02-02-2008, 03:57 PM

Originally Posted by shg

I'd do it like this:

Please Login or Register  to view this content.

The formula in D3 and copied down is =100 * C3 / (B3 - B2)

Thanx for simplifying the table. My table should show the calendaric days of the month in succession (30 or 31 days). The question is how to make the automated formula when there are blanks in different rows, as the vehicle does not need to top up everyday of the month.

I would appreciate your help.

**shg** · 02-02-2008, 04:53 PM

In your spreadsheet in D4 and copy down,

=IF( C4="", "", 100 * B4 / (C4 - MAX(C$3:C3) ) )

**Biancodi** · 02-03-2008, 04:14 AM

Originally Posted by shg

In your spreadsheet in D4 and copy down,

=IF( C4="", "", 100 * B4 / (C4 - MAX(C$3:C3) ) )

Thanks for providing the formula...I tried it but did not give the expected results.

I have attached the excel form showing both the formula used as per your instruction and a new column showing the expected results, which do not match.

I would appreciate if you can have a look at it and see if there is any solution.

Thanx

**shg** · 02-03-2008, 01:20 PM

Put the formula in the first wookbook you posted.

**Biancodi** · 02-03-2008, 01:55 PM

Originally Posted by shg

Put the formula in the first wookbook you posted.

That's marevellous!!!

Sorry to keep bugging you...is there a way to link worksheets...I mean..to calculate the first topup of the second worksheet with the last one in the first worksheet (see the attached file)
- and the final...final...is there a way to link workbooks???

thank you very much...

**shg** · 02-03-2008, 02:05 PM

Sure there is, it would just be messy. That's why I suggested using the other format.

Why does it need to be arranged like this?

**Biancodi** · 02-04-2008, 12:31 PM

Thank you for helping me with the formula. I am working as logistician with NGO's in different countries and I want to have national logistics staff to do the data entry. The reason that I need all the calendaric days is that I also add other columns to the worksheet such fuel price, total fuel cost, maintenance etc.

In fact it would be useful for me to have workbook links rather than worksheet links, as I need several worksheets in one workbook.

Each worksheet will represent one vehicle and there can possibly by 5 to 10 vehicles (worksheets) in one workbook.

Each workbook will represent one month and I organise 12 months, as well as three, six, nine and twelve workbooks to give me automatic reports for the vehicles.

I have already linked the workbooks for the information that I mentioned above. The fuel consumption/100 kms is a new information that I am planning to add on the worksheets.

I have already protected the formula so that the national logistics staff can not enter dat in the formula columns.

I have attached a workbook to give you an idea what I am trying to do (the workbook does not have the columns for the Odometre and the fuel consumption/100 kms). The attached workbook calculates the fuel consumption, but only on monthly basis, not on each topup days. I will need the fuel calculation for each topup days in order for logistics to understand what particular topup day shows high fuel consumption.

I will appreciate if you can assist me in linking workbooks,

Thanks again