Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Calculating Fuel Consumption
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
Best Answers
-
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
-
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.
-
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.
Answers
-
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
-
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
-
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
-
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.
-
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
-
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.
-
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.
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)
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!