Category Archives: Excel

Classic Linear Programming in Microsoft Solver Foundation for Excel

The Supply Chain optimization is a classic sample in linear programming. The Microsoft Solver Foundation comes with an example on solving this problem in Excel.
solver-foundation2

The data are categorized and neatly listed out in one worksheet, while the Model pane provided interface for constraints settings. Unlike in the standard Excel Solver, the modelling is a bit more complex, but the interface helped by providing an organized and consistent user interface in Excel.

solver-foundation3

Advertisements

Microsoft Solver Foundation for Excel

The Microsoft Solver Foundation (MSF) provides a rich set of optimization library and a modelling framework, all in an easy to use GUI through Excel. To make it developer friendly, MSF made available its library through API implemented in several programming languages.

Using MSF in Excel is very straight forward, thanks to its well designed user interface. For example, in the classic shipping optimization example, the model inputs are grouped in parameters, constraints, and goals, and are presented in side tabs.

msfb-1

Comparison of Microsoft Solver Foundation and the built-in Excel solver can be found in a previous installment.

Exploring optimization problems in Excel

Excel is able to solve optimization problems. Two commonly available tools are the build-in Solver tool and the Excel plugin for Microsoft Solver Foundation (MSF). The former is not installed by default but can be easily enabled through the Excel Options menu. The latter is a separate download available from Microsoft.

For a simple comparison of the performance of the two, the non-linear data fitting example from the MSF is used as benchmark.
msf-1

MSF provided additional menu pane within Excel for complex optimization operations.
msf-2

Optimization results and log of this benchmark run of a non-linear data fitting sample from the MSF, based on an NIST sample.
msf-10
msf-3

Goals setting screen.
msf-5

Model Display.
msf-6

On the other hand, the built-in Solver offered a simpler interface but still provide detailed reports, including answer, sensitivity, and limits reports in separate spreadsheets.
msf-7

msf-8

The built-in Excel Solver offered easy to use interface, while the Microsoft Solver Foundation is more capable for complex problems and modelling.

The NelderMead solver is selected in this benchmark by the MSF. Check out this previous installment for details of running Nelder-Mead on TI Nspire. The same data set is performed on the Nspire using Nelder-Mead to obtain the following results.
msf-9
msf-11

Visualizing WordPress stats with Excel Power Map

Power Map is a feature available in recent release of Microsoft Excel providing easy to use visualization of data in geographical form as well as temporal animation.

In a sample usage to understand where the readers of this blog are from this year, the WordPress stats data from 2016 are imported into Excel Power Map. WordPress already offered graphical presentation of visitor origin in a nice world map. For animating the data through a whole year by month, Excel Power Map is a nice and easy to use tool with just a little bit more effort to create such animation.

The statistic page should be familiar to all WordPress user. Download the data for each month as CSV and store on local drive.
powermap0

Now prepare the data by consolidating them all in a single Excel worksheet. I used the good ole awk as the source files are in plain text CSV to save some copy and paste efforts. For the animation to work, a new column indicating the time of data (e.g. data for USA in January 2016) has to be added for the Power Map to handle temporal attribute, and this column can not be in formula form.
powermap1a

When the source data are ready, select the whole region and click the 3D Map button under the Insert menu.
powermap2a

Now the Power Map UI will be displayed. A globe map will be displayed by default but since I preferred a flat map form instead of 3D so I clicked on the Flat Map button.
powermap3

The Power Map user interface works pretty much like a pivot table. You drop the dimension on the pre-defined attribute settings pane on the right hand side to tell Power Map how you would like the map to present your data. To create animation, just drag the date column to the “Time” section as the attribute and Power Map will be able to render accordingly.
powermap4

Now that everything is ready, the animation play nicely and Power Map will also be able to create the animation file.powermap5

Doing matrix multiplication in TI Nspire and Excel

In TI Nspire, matrix multiplication can be accomplished in the Calculator page. The process is intuitive and easy with the visual templates.

Firstly use the matrix template to define one:
matrixnspire1matrixnspire2

Then simply do multiplication after filling in the matrix elements.
matrixnspire4

Doing so in Excel required the use of the MMULT() function. A little trick is needed for this function for returning answer in array format – by pressing CTRL+SHIFT+ENTER at the same time after inputting the function for the cells selected as output array.

First step, fill in the matrix with the elements into respective cells.
matrixexcel1

Mark the output region to use the array function. In this example it is also a 3×3 matrix.
matrixexcel2

Apply the MMULT() function on the upper left corner cell of the region selected.
matrixexcel3

The trick is here, when finished typing in the function, press CTRL+SHIFT+ENTER instead of just ENTER. The answer from the function will then be populated in the selected cells, and the formula will be displayed with curly brackets enclosed, like  {=MMULT(B2:D4,F2:H4)}.matrixexcel4

Confidence Interval calculation in TI Nspire and Excel

In TI Nspire, calculation of Student’s t confidence interval can be achieved using data or statistics input. The menu can be accessed from “Statistics > Confidence Interval > t Interval”.
t-confidenceinterval1

In Excel, the same function does not have a separate menu item, but instead is included in the Descriptive Statistics dialog with a check box of “Confidence Level for mean”, and a percentage input box next to it. Unlike in Nspire, lower and upper are not included in the output.
t-confidenceinterval3

Simple Excel conditional RAG icons comparing yearly fluctuations

The conditional icon set in Excel is a great feature for visualizing data values. A simple usage will be showing up / down arrows in red / amber / green color. In its default Excel will do the calculation across the cells to determine which icon to use. This is nice but for some other usage it might be necessary to have some customization and it can be achieved with the respective Conditional Formatting dialog. For some usage, however, the restrictions imposed in this dialog like relative reference prohibited the presentation of certain commonly used intuitions.

For example, the data below depict sales value country-wise. Using automatic icon set in defaults, Excel will not present the desired icons in the way of showing sales moving up or down for each country, but instead it will show the icons by the yearly value.

excelautoicon1

A simple solution is to add a calculation column using the if() function to return either -1, 0, or 1 corresponding to the sales value comparison by country. The auto formatting icon can then be applied to this column. With a final touch of column width setting, the result is nicely presented.

excelautoicon2