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 provided additional menu pane within Excel for complex optimization operations.
Optimization results and log of this benchmark run of a non-linear data fitting sample from the MSF, based on an NIST sample.
Goals setting screen.
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.
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.
Since the last installment with a controversial primary school mathematical question, another interesting but no less challenging question is being popularized recently on local social media. The question involved finding the value of 9 single digit variables, namely A to H and P, that will fulfill the following simple calculation, where A to H are all of different values, and
As reported in local newspaper, this question originated from a primary school for provoking student’s mathematical thinking and is not in standard syllabus. Yet it is challenging even for university graduates.
It is not the intention here to discuss the pedagogy of this problem, but only to explore solving this interesting problem using Excel without any programming.
The Excel Solver is able to solve this question by modelling it into constraints. Usually for Solver problems the variables are modeled in single cell values, but since this question mandated some intrinsic properties that are not easy to be defined in the Solver dialog, a special matrix is used instead of single cell value like in the below so that constraint of each variable carries different value can be defined.
The various constraints are modeled in the Solver dialog. Each sum of row must be one implies only one digit is selected for each run for A to H. Likewise each sum of column must be equal or less than one implies no duplicate digits for each run. Integer constraint is also applied. The target cell containing the equation of PPP – (AB – CD + GH) is set to zero. Simplex-LP method is selected for this problem, while it was believed that evolutionary methods better suit the model as the matrix has high resemblance of genes.
Running the Excel Solver to obtain the result. On an Intel Core i5 it took almost 2 seconds!
A more descriptive diagram showing the relationship between cells that the model is comprised of. The column next to the Sum of row one are sumproduct cells that will naturally return the values represented by each variable according to the selected single digit value.
Excel file download
The Excel Solver is not just versatile but also is fun in itself to work with tricky problem like this “primary school” question.