Curve Fitting using the\\"SOLVER\\" tool in Excel

Computing Techniques

Curve Fitting using the “SOLVER” tool in Excel

The following illustrates an Excel spreadsheet being used to fit an exponential decay curve through the experimental points in A7 to B15.

The fitted values for the curve are calculated in C7:C15 by entering the formula

=A*exp(-k*A7) in C7 and then copying it down through C8 to C15. It is convenient to name1 cells G7 as A and G8 as k. It will be necessary to set initial values in A and k by guesswork or wisdom!

In order to perform a least squares fit to the data, the squares of the differences are calculated in D7 to D8 and the sum of squares in D17. The best fit can then be

accomplished by changing the values of A and k in order to minimise the contents of D17.This process can of course be carried out by making successively better guesses, but the ‘Solver’ tool automates the process and hence makes life much easier. In order to do this choose 2 and enter the values as shown. Then press Solve.

The exponential function is used here as an illustration but the same process may be used to fit other functions.

1) To name a cell first select it, then

2) If the Solver tool is not in your toolbox select and tick the Solver selection

button.

K Gregson