If statement that subtracts current mileage from last time gas was purchased

Office of the Medical Director

Hi. I have a vehicle check-in/check-out sheet which lists the various info like who checked in and out the vehicle, times, dates, etc. In this sheet, we are asking if gas was purchased, and if so, please take a picture of the gas receipt which is submitted to the accounting department for reimbursement. So I already have the approve/deny automation working to send to the accounting department; however, they asked that we provide the mileage calculated since the last gas fill up, which I need help with calculating the mileage since the last fill. (below is a screenshot of my sheet). I created a new column "Mileage Since Last Fill-up" where I need to calculate the mileage since the last time someone entered that gas was purchased.

In this test data, we have two times that gas was purchased (lines 3 & 11). So here's my thoughts. If Gas purchased = "Yes", subtract Odometer reading Line 11 (67256) - Odometer reading Line 3 (36543) and enter that difference in the Mileage since last fill up column. My issue is that I don't know how to write the formula to subtract the previous time that Gas = Yes.

Here's what I have so far:

=IF([Gas purchased]@row = "Yes", [Odometer reading]@row-

The whole point to this calculation is purely because the accounting department wants to know how many mileage the vehicle has been driven since the last fill up, and I just need to all the mileage since the last fill-up field to the Approval/Deny Automation process that will email the accounting department

Paul Newcome

Try something like this...

=[Odometer Reading]@row - MAX(COLLECT([Odometer Reading]:[Odometer Reading], [Odometer Reading]:[Odometer Reading], @cell< [Odometer Reading]@row, [Gas Purchased]:[Gas Purchased], LEFT(@cell) = "Y"))

Note: Double check my spelling of your column names. For some reason my fingers really did not want to type "Odometer". Haha