Index/Match/Sum Problem?

[daanackerman]

Hi Friends,


I have two different tables (matrices) consisting of a stop to stop demand matrix for 30 stops (in the top table, for example, the demand between station 2 and 6 is 18passengers) and another providing the VARYING distances between these stops (in the bottom table the distance between stop 2 and 6 is 1.69km). I would like to compile a summary of the data in these matrices and wondered whether a sort of a "frequency table" can be compiled or whether index/match/sum formula should be used to arrive at the result I need. The result must be a table summarising the various distances between the stops and the number of passengers traveling these distances as summarised in the "Result table" as set out below


RESULT TABLE


DISTANCE PASSENGERS

0.34 0

X Y

X Y

X Y

X Y

1.69 18

1.85 23


Distance (X above) is a list of all the distances between stops in table 2

Passengers (Y above) are the total number of passengers in table 1 traveling the distance in table 2 (there might be more than one combination of stations with the same distance between them and this is why I need the sum of these passengers).


Can anybody assist in sorting out this problem.


Regards


Dan

 

[Hui]

Dan


If you have the two Tables as you described (Distances and Passengers)

I think all you would need is a list of the Unique Station-Station ID's

Then you can retrieve the data from the Distances and Passengers tables

Then sort the list according to distance


You will probably do an Index/Match to retrieve the data from the two tables

As it will enable you to look up the Source/Destination stations as rows/columns


If you want some more help upload the data somewhere, refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[daanackerman]

Hi Hui,


Thanks for the reply. I hope this will help and trust that I have done it correctly to upload the data.


http://www.2shared.com/file/BUZUQNC6/EXAMPLE_DEMAND_1.html


Regards


Dan

 

[NARAYANK991]

Hi Dan ,


I am confused by your data in conjunction with your post ; can you please clarify ?


Your first table shows 23 against FROM STOP = 1 and TO STOP = 3 ; I assume this means that 23 people get in at the very first stop , wanting to get down at stop 3 ; is this correct ?


If this is so , then the total number of people who get in at the first stop is the SUM(B5:AC5) ; is this correct ?


Secondly , your second table lists the distances between stops ; nowhere do I find a distance 0.34 ; from where have you got this value ?


Can you give more figures in your result table , instead of Xs and Ys ?


Narayan

 

[daanackerman]

Hi Narayank,


Thanks for the questions. Now it seems like somebody is looking at this again!


On your first question: You are correct in your assumption. The total that actually got on at the very first stop is 174 (sum(b5.ac5).


On the second question: I used this as example but it is eg the distance between stops 25 and 26.


I can add more examples but I wanted to illustrate how the table should look like in the end so that I know how many passengers traveled a particular distance.


More examples would look like this

13.3 km (stops 7-23, 15-30, 23-5, 30-13) PAX 8

14.7 km (stops 8-26, 11-29, 26-6, 29-9) PAX 18


Regards


Dan

 

[NARAYANK991]

Hi Dan ,


I am sure that there quite a few in this forum who are scratching their heads over your problem !


Regarding your second clarification , I think now things are more complex. You say that you have got 0.34 as the distance between stops 25 and 26 ; fine , I have verified that it is so. However , for the further examples you have given that 13.3 occurs so many times within the second table , you need to ensure that the numbers are identical ; I see that in several places , the numbers differ by a minute amount e.g. one value is 0.64 , whereas the other identical number is entered as 0.6400000003.


Narayan

 

[NARAYANK991]

Hi Dan ,


Try this :


1. Convert your second table , the one having the distances , into a column vector , in any unused column , say column AK. To do this , in AK2 , enter the following formula :

[pre]

=OFFSET($C$40:$AD$67,MOD((ROW()-ROW($AK$2)),ROWS($C$40:$AD$67)),TRUNC(ROW()-ROW($AK$2))/ROWS($C$40:$AD$67),1,1)

[/pre]
Copy this down till AK785 ; this is because the range C40:AD67 has 28 rows and 28 columns ; 28 * 28 = 784 , and AK2:AK785 is 784 cells.


2. In AL2 , enter the following formula :


=SUMPRODUCT(($C$40:$AD$67=AK2)*($C$6:$AD$33))


Copy this down to AL785.


Now , against any distance , for e.g. 13.32 , you will find in column AL , the total number of passengers who travelled this distance.


Narayan