Folks,
I am trying to work out the number of people on a train at each stage of a journey. My data is in the form of a list of tickets and includes Departure Station and Arrival Station, where each record represents a ticket sold. I have numbered the stations sequentially. I need to somehow come up with the number of passengers on board at each station along the train's journey. Say, for example, there are 10 stations on this particular line. Each record then gets a Departure Station Number(DSN) and an Arrival Station Number(ASN). With the stations then listed in order in the adjacent columns, I have done a simple if formula in each column which is =if(AND(DSN>=StationOrderColumn, ASN<=StationOrderColumn),1,0)) where StationOrderColumn is the number of each station in the journey.
I can't use excel jeanie here at work so hopefully the simple grid below will be understandable.
DSN ASN 1 2 3 4 5 6 7 8 9
1 9 1 1 1 1 1 1 1 1 1
3 7 0 0 1 1 1 1 1 0 0
5 6 0 0 0 0 1 1 0 0 0
7 9 0 0 0 0 0 0 1 1 1
4 7 0 0 0 1 1 1 1 0 0
2 4 0 1 1 1 0 0 0 0 0
Total 1 2 3 4 5 4 4 2 2
If you then wanted to know how many people were onboard at station 5, the sum of whats in the column named 5, is the answer.
Is there a better way of doing this considering, I have 8 different routes, with up to 30 stations on each route?
I am trying to work out the number of people on a train at each stage of a journey. My data is in the form of a list of tickets and includes Departure Station and Arrival Station, where each record represents a ticket sold. I have numbered the stations sequentially. I need to somehow come up with the number of passengers on board at each station along the train's journey. Say, for example, there are 10 stations on this particular line. Each record then gets a Departure Station Number(DSN) and an Arrival Station Number(ASN). With the stations then listed in order in the adjacent columns, I have done a simple if formula in each column which is =if(AND(DSN>=StationOrderColumn, ASN<=StationOrderColumn),1,0)) where StationOrderColumn is the number of each station in the journey.
I can't use excel jeanie here at work so hopefully the simple grid below will be understandable.
DSN ASN 1 2 3 4 5 6 7 8 9
1 9 1 1 1 1 1 1 1 1 1
3 7 0 0 1 1 1 1 1 0 0
5 6 0 0 0 0 1 1 0 0 0
7 9 0 0 0 0 0 0 1 1 1
4 7 0 0 0 1 1 1 1 0 0
2 4 0 1 1 1 0 0 0 0 0
Total 1 2 3 4 5 4 4 2 2
If you then wanted to know how many people were onboard at station 5, the sum of whats in the column named 5, is the answer.
Is there a better way of doing this considering, I have 8 different routes, with up to 30 stations on each route?