1. ## Fuel Spreadsheet Date Issue

Hello all,

I have been working on miscellaneous spreadsheets for a friend of mine who is an independent owner operator truck driver. To say the least, he is extremely thorough and follows every number in his equipments performance and profit margins. This is the first spreadsheet I have put together for him that I have stumped myself. Our goal with the fuel tracking spreadsheet is to be able to input fuel stops as they happen and have the spreadsheet automatically calculate several variables he has chosen to follow along during his trips. There is a sheet for the truck fuel stops, the DEF fuel stops (which is the emissions fuel for newer trucks that is required), and lastly, the reefer unit fuel stops for the trailer's refrigeration units fuel. The problem I am incurring is that when my friend makes two fuel stops in a single day, I am unable to correctly calculate the DEF's burn rate between DEF fuel stops. In using the date as a range variable between fuel stops, the burn rate is calculated correctly until there is two entries of the same date. Then I am unable to figure out how to tell the burn rate formula to only use one of the two same date entries. The basic calculation for the DEF burn rate is: =the DEF gallons purchased at DEF fuel stop / the sum of the truck gallons since the last DEF fuel stop. For example: on 1/12/18 there was a DEF fuel stop where 6.513 gallons of DEF fuel was purchased. Prior to 1/12/18, the last DEF fuel stop was 1/11/18. The total truck fuel gallons purchased between 1/11/18 and 1/12/18 was 158.913 gallons (95.054 on 1/11/18 and 63.859 on 1/12/18. 6.513 / 158.913 = 0.0409 rounded to 4.1%. Here is the actual Excel formula I am using for this equation between the two sheets:

DEF Burn Rate Formula: =IFERROR(SUM(I3/SUMIFS('5401'!\$H\$2:\$H\$3001,'5401'!\$B\$2:\$B\$3001,">="&B2,'5401'!\$B\$2:\$B\$3001,"<="&B3))," ")

This formula works great and is accurate until I have two truck fuel stops in the same day. I do not know how to distinguish a variable between the two dates and between to tell the DEF burn rate which fuel stop in that same day to use. Here are two screenshots of both the truck fuel sheet (pictured first) and the DEF fuel sheet (pictured second). Any assistance or help somebody could provide to overcome this hump would be greatly appreciated. I have been working on this spreadsheet for three months and have wrapped my brain around it several times and have not been successful. The Excel Forum was my last resort as I wanted to try and figure this out on my own but I am beginning to believe a fresh set of eyes would help! Please let me know if anymore information about the spreadsheet is needed to help.

Thanks all!

Sincerely,

BaconFanatic2

TruckFuel.png
DEFFuel.png

2. ## Re: Fuel Spreadsheet Date Issue

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

3. ## Re: Fuel Spreadsheet Date Issue

I am still struggling to attach my spreadsheet through the provided the steps. Is there another way I can attach it and provide it to be looked at?

4. ## Re: Fuel Spreadsheet Date Issue

Please see attached. Sorry! I finally figured it out

5. ## Re: Fuel Spreadsheet Date Issue

Can you tell us where to look specifically? Also we need to know what you are expecting the results to be.

My initial thought is that you could add a time to the date field.

6. ## Re: Fuel Spreadsheet Date Issue

Good evening,

What I am looking for is to be able to calculate the DEF burn rate correctly when there are two or more truck fuel stops in the same day. Does that make sense?

7. ## Re: Fuel Spreadsheet Date Issue

See point 2 in post #2 - I still can't find your manually mocked up results.

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