Tag Archives: solver

Solving a primary school math problem with Excel Solver

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

primarydifficultexcel6

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

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

Running the Excel Solver to obtain the result. On an Intel Core i5 it took almost 2 seconds!
primarydifficultexcel3

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

primarydifficultexcel8

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.

Advertisements

Black-Scholes formula in TI-84 Solver

Came across a ten-year old article from TI on working with the Black-Scholes pricing model in TI-84. In it, a couple of examples are given to utilize various features of the TI-84 to work with the equation to derive an European call option in the Black-Scholes model. One of these method being invoking the Solver.

Entering the equation again is considered quite cumbersome, and it is not quite sure how to archive the Solver equation for later use. After a couple of tries, it become obvious that the Solver wouldn’t work with a function stored in String at all. Fortunately alternative method is found to somehow persist the equation for later use.

The trick is to make use of the following build-in functions available in the TI-84:

  • String>Equ()
  • expr()

By making use of these two functions, the Solver will be able to handle the Black-Scholes equation which is stored in String properly. Firstly the equation must be stored in a String, and then by making use of String>Equ() function, the equation will be able to persist in one of the equation variables. In this form, the Solver will be happy to work with it in its entirety, which means all variables are considered. The equation stored in Str0 is converted to function Y0, and is then processed properly by the Solver as shown in the below screens. For persisting, this can be done in a program including the definition of the Black-Scholes formula itself.

How old are they?

On a local newspaper yesterday, this question is reported to be controversial for primary school examination since it is too difficult. It goes like this. There are two people and the older one said:

“When I was at your age, you were only 5.
When you become as old as I am now, I will become 71.”
The question is, how old are these two people?

It turns out, according to the newspaper, the question is not aimed at simultaneous equation at all, but to solve the problem by graphically dividing lines in proportion.

To solve in an overkill fashion by Nspire:
agequestion1

And this is also a refresher on how to solve simultaneous equations on TI-84 series using the rref function (reduced row echelon form). Define the equations in form of matrix (as [A] below), and then run the rref() function on it. The result matrix contained the solution to the problem.
agequestion2agequestion3

And just in case Excel is the only tool available, no worry, its solver will get you covered (!).agequestion4

Solver behaviour on HP Prime, 50g, 39gII, TI Nspire CX CAS, and Casio fx-9860GII

On comparing the differences in behavior of TVM calculations, the solver of HP Prime is the only calculator able to return, without any initial guess input, the approximate value of number of period in a peculiar form of future value finding equation, although with not small of an error of about 13%.

Edit: Added results from 50g and 39gII emulator, they both returned the same result (but not the same as the Prime) without being given any hints.

HP Prime:
penny-prime

HP 50g
penny-hp50g

HP39gII
penny-hp39giipenny-hp39gii2

TI Nspire: penny-nspire2

Casio fx-9860GII:
penny-casio1 penny-casio2

The original problem is from an classic HP calculator manual published in 1982. The problem goes like this (example 2 on page 145): If an employee is paid $0.01 every second, with an interest rate of 11.25% compounded every second, what will she get after a year? In the original document, this problem is used to illustrate the limitations numerical accuracy. Detailed explanation and workarounds can be found in the original document.