Add a value in the PREVIOUS row

[RensD]

I have a "Ride administration" App.
It writes it's data tot a sheet like this:

My challenge is that I want to alter a value in a PREVIOUS line when a new entry is created.
This is the value of 'Distance' (and the same for 'Costs per ride' and '19 ct/Km')
This is because I can only calculate the distance drive in the previous ride when the new start KM-value is entered.

Example: If I would enter row 6 in the example sheet, the I can only fill in the Distance value in row 5 when the KM-value in row 6 is known.

How do I change the value in a previous  row, based upon a new entered value.

[Hieran_Del · Accepted solution]

There are multiple ways to accomplish this. For example:

In a dynamic spreadsheet solution, you could have a formula in F2 that calculates the value if E3 is not blank. Then J2 and K 2 could have formulas that calculates their values if F2 is not blank. This assumes the rows are always sequential.

In a dynamic Appsheet solution, you could have 3 virtual columns that evaluate in a similar way, perhaps the "Distance" column evaluating the same row's "KM" column against a next highest "KM" column, if it exists. The "Costs per ride" and "19 km/ct" columns could evaluate with the same row's "KM" column. In this solution, it does not matter if rows are sequential.

If you want the data to not be dynamic (no virtual columns, no formulas), you could possibly string together a sequence of actions to update surrounding values, or perhaps use automation. Automation could call an Apps Script to recalculate all values in the spreadsheet, and then get the app user to sync again to see the changes. This solution depends on the Apps Script implementation, but can be written in a way to avoid the need for rows in sequence.

[RensD]

@Hieran_Del 

Thank you for your very useful insights.
I implemented an ArrayFormula() and that does the trick just the way I need it.

Thank you very much.

[Denny774]

Hey man,

make them goddam drivers enter the km-value before and after. Anything else doesnt make sense.

Cheers

[Hieran_Del]

Actually... that's probably the best and correct solution @Denny774.

[Markus_Malessa]

I would agree with the other poster to just have the driver enter the KM at the beginning and then at the end. It makes things more simplistic and this way you could introduce a calculation for un-accounted KM instead. Furthermore, I find it counterintuitive to update the distance in the row before when a new row is added, in my opinion a mileage log is almost always logging the ending mileage and then calculating the distance on the same row. It might make more sense to have a date column, a column for trip start time, a column for start KM, a column for trip end time, a column for end KM. Then have an action button on the started trip that opens an edit form using LINKTOFORM to generate the end time with NOW() and have the driver enter the end KM. You could have an initial value formula for start KM by pulling data from the previous row, but I think the driver would just leave that value vs actually verifying the odometer.