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

Double click IdealFlow.xlam to install Ideal Flow Excel Add Ins. This double click action would copy the file into folder C:\Users\{yourname} \AppData\Roaming\Microsoft\AddIns

If this action does not work, simply copy the AddIns file “IdealFlow.xlam” and the help file “IdealFlow.pdf” into folder C:\Users\{yourname}\AppData\Roaming\Microsoft\AddIns

 

Initiate Add-Ins

To initiate the Add-Ins, open Excel new workbook

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

   

Click Go button (after Manage: Excel Add-ins at the bottom). Add-Ins Window will appear.

Check IdealFlow from the Add-Ins available. If it is not yet available, click Browse button to locate the Add-Ins

 

 

If you use Browse window, locate the IdealFlow.xlam and click OK.

In the Add-Ins Window click OK button

If your installation is right, you will see new Menu Add-Ins with Ideal Flow in Menu Command

 

 

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.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.

You can press Random Example again (from Add-Ins menu > Ideal Flow > Random Example) to generate new random example.

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 to be 1, or the maximum flow to be 1, or you can set the total flow (kappa) to be any positive value. The last option is to set the ideal flow matrix to be an 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.

That’s it, enjoy exploring ideal flow.

 

If you would like to know more about this work, kindly read and cite 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:

Python code of ideal flow in https://github.com/teknomo/IdealFlowNetwork

Tutorial on the python code of ideal flow network is in http://people.revoledu.com/kardi/tutorial/Python/Ideal+Flow.html

See Also: Research Page on Ideal Flow Network

 

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