Calculating Fuel Consumption

Jacques Hollands

Hi there,

I manage a transport fleet and want to use Smartsheet to track the vehicles' fuel consumption. It is calculated by subtracting the kilometres from the previous fill-up from the current fill and dividing this variance by the litres of the current fill-up.

See attached spreadsheet. The problem is that fuel fill-up entries are mixed for the various vehicles, so it is not as simple as using the kilometres in the row below. I need to be able to obtain the kilometres for the same vehicle number, so it may mean I have to go down several rows.

Can anyone perhaps assist me on showing me how to do this? I would be most grateful.

Take care,

Jacques

Fuel Consumption.xlsx

Paul Newcome

If you use a system generated Created (date) type column, you can use the date/time stamp to reference the last entry for the vehicle.

=INDEX([Odometer [KMS]]:[Odometer [KMS]], MATCH(MAX(COLLECT([Created (date)]:[Created (date)], [Created (date)]:[Created (date)], @cell < [Created (date)]@row, [Vehicle No]:[Vehicle No], [Vehicle No]@row)), [Created (date)]:[Created (date)], 0))

This will pull the value from the Odometer column for the previous entry based on the Created (date) column for the vehicle number that is entered on the current row.

EDIT:

Then you would subtract the above from the current Odometer data

=[Odometer [KMS]]@row - INDEX([Odometer [KMS]]:[Odometer [KMS]], MATCH(MAX(COLLECT([Created (date)]:[Created (date)], [Created (date)]:[Created (date)], @cell < [Created (date)]@row, [Vehicle No]:[Vehicle No], [Vehicle No]@row)), [Created (date)]:[Created (date)], 0))

And divide by Litres

=([Odometer [KMS]]@row - INDEX([Odometer [KMS]]:[Odometer [KMS]], MATCH(MAX(COLLECT([Created (date)]:[Created (date)], [Created (date)]:[Created (date)], @cell < [Created (date)]@row, [Vehicle No]:[Vehicle No], [Vehicle No]@row)), [Created (date)]:[Created (date)], 0))) / [Litres [l]]@row

Additional EDIT (sorry for the afterthoughts):

Depending on your region, you may need to swap the commas out for semicolons.

=([Odometer [KMS]]@row - INDEX([Odometer [KMS]]:[Odometer [KMS]]; MATCH(MAX(COLLECT([Created (date)]:[Created (date)]; [Created (date)]:[Created (date)]; @cell < [Created (date)]@row; [Vehicle No]:[Vehicle No]; [Vehicle No]@row)); [Created (date)]:[Created (date)]; 0))) / [Litres [l]]@row

Paul Newcome

My field names were based on your example you provided. If you want to give me your actual field names, I can help with updating those if you would like. Or if you are able to get it working, feel free to post the final formula.

The layout of the formula should be correct. Now that you have the correct delimiter, it SHOULD be just a matter of updating to the appropriate column names that you are using.

That way we have a correct solution here that can be flagged as "helpful" so that it may be able to help others.

Paul Newcome

Ok. When you are able, let's try some troubleshooting...

=INDEX([Vehicle No]:[Vehicle No], MATCH(MAX(COLLECT([Created (date)]:[Created (date)], [Created (date)]:[Created (date)], @cell < [Created (date)]@row, [Vehicle No]:[Vehicle No], [Vehicle No]@row)), [Created (date)]:[Created (date)], 0))

This will tell you the vehicle number we are pointing at in the overall formula.

Paul Newcome

If you use a system generated Created (date) type column, you can use the date/time stamp to reference the last entry for the vehicle.

=INDEX([Odometer [KMS]]:[Odometer [KMS]], MATCH(MAX(COLLECT([Created (date)]:[Created (date)], [Created (date)]:[Created (date)], @cell < [Created (date)]@row, [Vehicle No]:[Vehicle No], [Vehicle No]@row)), [Created (date)]:[Created (date)], 0))

This will pull the value from the Odometer column for the previous entry based on the Created (date) column for the vehicle number that is entered on the current row.

EDIT:

Then you would subtract the above from the current Odometer data

=[Odometer [KMS]]@row - INDEX([Odometer [KMS]]:[Odometer [KMS]], MATCH(MAX(COLLECT([Created (date)]:[Created (date)], [Created (date)]:[Created (date)], @cell < [Created (date)]@row, [Vehicle No]:[Vehicle No], [Vehicle No]@row)), [Created (date)]:[Created (date)], 0))

And divide by Litres

=([Odometer [KMS]]@row - INDEX([Odometer [KMS]]:[Odometer [KMS]], MATCH(MAX(COLLECT([Created (date)]:[Created (date)], [Created (date)]:[Created (date)], @cell < [Created (date)]@row, [Vehicle No]:[Vehicle No], [Vehicle No]@row)), [Created (date)]:[Created (date)], 0))) / [Litres [l]]@row

Additional EDIT (sorry for the afterthoughts):

Depending on your region, you may need to swap the commas out for semicolons.

=([Odometer [KMS]]@row - INDEX([Odometer [KMS]]:[Odometer [KMS]]; MATCH(MAX(COLLECT([Created (date)]:[Created (date)]; [Created (date)]:[Created (date)]; @cell < [Created (date)]@row; [Vehicle No]:[Vehicle No]; [Vehicle No]@row)); [Created (date)]:[Created (date)]; 0))) / [Litres [l]]@row

Jacques Hollands

Oh wow, Paul, that is a heck of an effort you put in answering me. I truly appreciate it. I haven't had a chance looking at it, but will do so later tonight. I will give feedback once I see how that would work.

In the meantime, I want to thank you sincerely for assisting.

Regards,

Jacques

Paul Newcome

Happy to help! 👍️

Jacques Hollands

Hi Paul,

Thank you kindly for your assistance. The formula didn't work off the bat as you were correct that we had to change the ; into ,

In addition, we had to change some the field names and we are close, really close. It doesn't get the correct vehicle's previous fill-up, but we will get it right. Although I am rather savvy with Excel formulas, I have not yet tried the combination of the INDEX and MATCH functions that many people recommend over the VLOOKUP function. Seems to be the case in SmartSheet too.

We will continue to try to get the formula 100% correct as there are great learning opportunities in trying to figure out the correct syntax. However, if you don't mind, I will contact you again if we can't get it right.

Thanks and stay well,

Jacques

Paul Newcome

My field names were based on your example you provided. If you want to give me your actual field names, I can help with updating those if you would like. Or if you are able to get it working, feel free to post the final formula.

The layout of the formula should be correct. Now that you have the correct delimiter, it SHOULD be just a matter of updating to the appropriate column names that you are using.

That way we have a correct solution here that can be flagged as "helpful" so that it may be able to help others.

Jacques Hollands

Hi Paul,

Yes, I did change some of the field names, but that was easy to correct. The delimiter too. I am finding that I am not referencing the correct vehicle for my previous fill up. I have to tie up a few issues for our lockdown (Corona), but will look at it later this afternoon. I will then give you feedback.

And I will most certainly mark the solution as helpful as soon as I get it to work.

Take care,

Jacques

Paul Newcome

Ok. When you are able, let's try some troubleshooting...

=INDEX([Vehicle No]:[Vehicle No], MATCH(MAX(COLLECT([Created (date)]:[Created (date)], [Created (date)]:[Created (date)], @cell < [Created (date)]@row, [Vehicle No]:[Vehicle No], [Vehicle No]@row)), [Created (date)]:[Created (date)], 0))

This will tell you the vehicle number we are pointing at in the overall formula.

Rainier Hollands

Hi Paul,

So I ran it now with the formula you provided above. As you will see in the screenshots, the error does lies with the trucks repeating themselves. It's constantly truck 14 and 16 repeating itself.

screenshot 2.png

Snip it smartsheets.png

This is the formula we are currently using.

=INDEX(Odometer:Odometer, MATCH(MAX(COLLECT(Date:Date, Date:Date, @cell < Date@row, Truck:Truck, Truck@row)), Date:Date, 0), 0)

Jacques Hollands

Hi Paul, thank you very much for your solution above. My son, Rainier, and I have managed to use your formula (solution) to good effect. It has greatly enhanced our solution.

You have put in a considerable effort and we appreciate the assistance.

Stay well,

Jacques