Ideal Flow Matrix Excel Add In

By Kardi Teknomo, PhD.

Ideal Flow is a steady state relative flow distribution in a strongly connected network where the flows are conserved. This Excel Add Ins is useful to compute ideal flow and the statistics of ideal flow in Microsoft Excel.

Download

Click this link to download and unzip it into a folder.

 

Requirements:

To use the Ideal Flow Excel Add-Ins, you need the following requirements:

  • Desktop or Laptop computer
  • Windows 10
  • Microsoft Excel 2013 and above

 

Specification

  • Maximum Nodes: 25 nodes
  • Maximum Links: 625 links
  • Maximum Matrix size: 25 by 25

 

Installation

  1. Copy the two files:

    1. AddIns file "IdealFlow.xlam" and
    2. the help file "IdealFlow.pdf" into folder
      C:\Users\{yourname}\AppData\Roaming\Microsoft\AddIns
      Such that you can open the help file from About Window.
  2. Open Excel, enable the macro when you are prompted to.

Initiate Add-Ins

To initiate the Add-Ins, open Excel new workbook

  1. Go to menu File> Options. In Excel Option Window, select Add-Ins from the left panel.

    2.    

  2. Click Go button (after Manage: Excel Add-ins at the bottom). Add-Ins Window will appear.
  3. Check IdealFlow from the Add-Ins available. If it is not yet available, click Browse button to locate the Add-Ins

    4.  

  4. If you use Browse window, locate the IdealFlow.xlam and click OK.
  5. In the Add-Ins Window click OK button
  6. If your installation is right, you will see new Menu Add-Ins with Ideal Flow in Menu Command

    7.  

Tutorial

In this section, you will learn how to use Ideal Flow Excel Add-Ins in a very simple manner.

  1. Open Microsoft Excel. Select Blank Workbook
  2. Click cell A1. Go to Add-Ins menu > Ideal Flow > Random Example. You will get random example of input and output of Ideal Flow.

  3. Cells A1 to E5 are an input matrix of random example. The structure of the input matrix is always irreducible (to represent strongly connected network). The value of the input matrix can be binary (0, 1) matrix, stochastic matrix (sum of each row is one) or capacity matrix (any non-negative matrix, as long as the matrix structure is irreducible). Your random example would not be the same as the following figure but it will be located at the same place.

  4. Cells H1 to L5 are the output matrix. The output matrix is always the ideal flow matrix of the input matrix. Cells M1 to M5 are the sum of rows of the ideal flow matrix. Cells H6 to L6 are the sum of columns of the ideal flow matrix. You can see the sum of rows of ideal flow matrix is always equal to the sum of columns. This matrix property that the sum of rows is equal to the sum of columns is called premagic property. Cell M6 is the total flow of the ideal flow matrix.

    5.  

  5. Cells H8 to I10 represent the output indicators of ideal flow matrix. Kappa represents the global scaling. Because Ideal Flow represent relative flow, you can apply any global scaling. This global scaling indicator would be the same as the total flow. Entropy of the stochastic matrix that form ideal flow matrix is also one of the indicator. Entropy Ratio is another indicator of network efficiency. When you use adjacency (0,1) matrix as your input the program will assume of uniform distribution and therefore you will always obtain entropy ratio of 1.

  6. Cells H12 to I18 represents the statistics of output ideal flow matrix. You will see the sum of flow would be always equal to kappa. The sum, minimum and maximum, average and standard deviation of the ideal flow would be dependent of the global scaling. However, the coefficient of variation of the flow would be constant for the same input matrix, regardless the global scaling that you use.

  7. You can press Random Example again (from Add-Ins menu > Ideal Flow > Random Example) to generate new random example.
  8. Optionally, you can also change the random example to either adjacency matrix (by default), stochastic matrix, or capacity matrix. To change this kind of option, you go to Add-Ins menu > Ideal Flow > Settings to show the Setting Window and click Example tab.

  9. To apply the different type of global scaling, go to Add-Ins menu > Ideal Flow > Settings to show the Setting Window and click Scaling tab. You can set the minimum flow or the maximum flow or the total flow (kappa) to be any number larger than 1. The last option is to set the ideal flow matrix to be Min Integer. This last option is not always working in Excel if your least common multiple (LCM) of the denominators is super large because Excel does not allow very large number. Nevertheless this show a proof of concept that ideal flow is a relative rational positive numbers and therefore they can always be converted into integers.

  10. You can also locate the input and output matrices in different locations aside from A1. Go to Add-Ins menu > Ideal Flow > Settings to show the Setting Window and click Setting tab. You can also specify the number of nodes (= the matrix size) here.

  11. Optionally, if you do not want to include everything in the output indicators, you can go to Add-Ins menu > Ideal Flow > Settings to show the Setting Window and click Output tab. Check or uncheck the indicators that you want to include or exclude.
  12. That’s it, enjoy exploring ideal flow.

 

Citations

If you would like to know more about this work, kindly read and cite any of the following papers:

  • Teknomo, K. and Gardon, R.W. (2017) Intersection Analysis Using the Ideal Flow Model, Proceeding of the IEEE 20th International Conference on Intelligent Transportation Systems, Oct 16-19, 2017, Yokohama, Japan
  • Teknomo, K. (2017) Ideal Relative Flow Distribution on Directed Network, Proceeding of the 12th Eastern Asia Society for Transportation Studies (EASTS), Ho Chi Minh, Vietnam Sept 18-21, 2017.
  • Teknomo, K. (2017) Premagic and Ideal Flow Matrices. https://arxiv.org/abs/1706.08856
  • Gardon, R.W. and Teknomo, K. (2017) Analysis of the Distribution of Traffic Density Using the Ideal Flow Method and the Principle of Maximum Entropy, Proceedings of the 17th Philippine Computing Science Congress, Cebu City, March 2017
  • Teknomo, K. (2015) Ideal Flow Based on Random Walk on Directed Graph, The 9th International collaboration Symposium on Information, Production and Systems (ISIPS 2015) 16-18 Nov 2015, Waseda University, KitaKyushu, Japan.

Visit also:

Menu

  

  • Click anywhere in the spreadsheet and then click Random Example to create random example starting from that cell.
  • Change the input cells and click Calculate to recalculate the ideal flow matrix and the output indicators

Context Menu

Do right click anywhere in Excel and you can also see context menu of ideal flow.

Settings Window

There are four panels in the Ideal Flow Matrix window

  1. Settings: to locate the location of input matrix and output matrix and total nodes
  2. Scaling: to set global scaling (kappa = total flow)
  3. Output: to set the output indicators
  4. Example: to set the random examples

You can change the global scaling of ideal flow based on the following options:

  1. The minimum flow of any link is set to be equal to any number larger than 1, or
  2. The maximum flow of any link is set to be equal to any number larger than 1, or
  3. The total flow of all link is set to be equal to any number larger than 1, or
  4. The flow of any link is set to be minimum integer (based on LCM)

Several optional output can be produced based on the computation of ideal flow:

  1. Network Entropy
  2. Network Entropy Ratio
  3. Sum of Rows
  4. Sum of Columns
  5. The statistics of the link flow (min, max, sum, average, standard deviation, coefficient of variation)
  6. Global scaling (Kappa), which is equal to total flow.

You can create random example based on the following:

  1. Adjacency matrix of random network. Adjacency matrix is a binary matrix to represent the directed link between nodes in the network.
  2. Stochastic matrix. Markov probability matrix where each row has the sum of one.
  3. Capacity matrix. Each link now has integer weight called capacity.

Click Random Example button after your choice of random example.

Latest Changes

Version 0.2 - Global scaling of Min Flow and Max Flow is now can be set beyond 1