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
Bookmarks