Log In
Register
Home
New Member
Excel Expert
November 22, 2023
Offline
I have a table of scheduled trains for a long trip. On each row there are a few formulae which reference a field on the previous row, such as comparing the departure time of a train with the arrival time of the previous train (on the row above).
Currently I do this using the RC of the cell in the row above such as: IF([@DepTm] < S10, …etc) when this formula is on row 11.
Is there a way to reference [ArrTm] in the previous row, instead of using the RC reference?
I did try a relative name: Arrival defined as $S10 then IF([@DepTm] < Arrival, ...etc) as described in https://www.myonlinetraininghub.com/relative-named-ranges but it must be redefined when a column is added or deleted from the table.
See Answer
Moderators
January 31, 2022
Offline
In this case I would add an "index" column containing sequential numbers starting at one. These will be the row numbers for each part of the trip. Then you can use the INDEX function to pick-up the previous ArrTm in its column.
The attached file contains a small example. You can add rows and columns and shift the columns around and even rename them.
In this example I came up with the following formula to calculate transfer time based on departure time minus previous arrival time. I guess you would want to do something like that as well.
=MOD([@DepTm]-IF([@Index]=1,[@DepTm],INDEX(Table1[[#All],[ArrTm]],[@Index])),1)
This is what's happening set-by-step:
=MOD( Dep - Arr, 1) sees to it that you can calculate duration when the departure is past midnight.
then, if the row number (i.e. index) = 1, there is no previous arrival time. Thus it will be equal to departure time.
In all other cases the previous arrival time will be based on the index number of the entire ArrTm column including its header. So, on row 2 the ArrTm selected will the one from row 1 as the first element in the array Table1[[#All],[ArrTm]] will be the header and the 2nd element will be the arrival time of row number 1.
See if you can apply this method to your own table. If you get stuck come back here and upload your file. That would make it easier to help.
Answers Post
The following users say thank you to Riny van Eekelen for this useful post:
Anders Sehlstedt
1 Guest(s)
