Ahmad Fattahi

Performing optimization on PI data using PI DataLink and Microsoft Excel Solver - Complete solution

Blog Post created by Ahmad Fattahi Employee on Sep 22, 2010

OK it’s time to revisit the “diet problem” we saw in my earlier post in more detail. The first thing to do is to add the Solver add-in to Microsoft Excel to use it. It is shipped as part of Microsoft Excel but may not be added by default.


The next step is to build up the problem set up. We need to dedicate a cell to every variable in the problem. In this example every price, limit, quantity, or decision variable has to have its own cell on the data sheet. So, we allocate 2 cells to the decision variables, being the units of each mineral purchased, and units of each substance in the two minerals. We also need to know how much of each substance we currently have. These are calculated as a function of the two previous quantities, i.e., units of each mineral times the amount each mineral contains, all summed up for the two minerals.


We also create cells for the minimum levels of the substances as well as the minerals (not to go below zero!). The objective function, i.e. the cost, is defined as a function of the prices and units purchased very easily.


A key point here is that any of these parameters can come from a PI server through PI DataLink. In fact, I have decided to get the unit mineral prices from PI. You would need to change those two cells to point to your PI server before you can use the attached file. But in general the amount of each substance in each mineral or the grades or even the constraint limits could come from a PI server.


When everything is set up we choose the “Data” tab on Excel and choose “Solver”. All the steps to define the problem for the optimization engine are very intuitive at this point. The only explanation is regarding the algorithms used. Since this is a linear optimization program, a Simplex LP would suffice. However, for nonlinear or non-smooth programs, which are more complicated, GRG Nonlinear or Evolutionary methods are also provided. Once you click on “Solve” you will have the optimal results on your Excel sheet in a matter of seconds!








Don't forget to make the PI server references point to your PI server on the Excel sheet.