lunchmaio.blogg.se

The solver works by varying parameters to optimize (=minimize) a function. We have included everything we need to fit our function parameters. Our basis spreadsheet showing the necessary columns and the inserted formulas for the sigmoid calculation (black), the error calculation (blue), and the sum of the errors (red). In the end, the spreadsheet should look like in the following figure.įigure 1. If not already done, sum up the values of column ∆F by the sum–macro. We use the square function to get rid of the sign of this difference. It is just the difference between the measure F and the calculated F. Next, we fill the ∆F column with the following formula. For the parameters take the corresponding cells – don’t forget to fix them! If you have marked a cell in a formula, you can press F4 (in Excel) or SHIFT+F4 (in OO–Calc) to fix the cell.į_ + t \\ For pH take the corresponding cell of the first column. Just put the formulas below into the F (calc.) column. Fill them with values of 1.0 (for a, b, c, d, and f) and 0.0 (for t). Last, add a list of parameter cells labeled a, b, c, d, f, and t. Below the last column add a cell for the sum of ∆F. Besides, add another two columns labeled F (calc.), and ∆F. Open a blank spreadsheet in Excel or OO–Calc and insert these values in two columns pH and F (meas.).

We take the points from the article last week: But, I think the solver is a very handy feature and, therefore, I want to give here a short introduction into using it for fitting a sigmoid function to a set of data.įirst, we need a set of data. It is possible to do such fits for nearly any function using the solver, though, but only a few people I’ve met knew how to do this or that it is even possible. However, both lack the possibility to fit sigmoid functions, automatically. Personally, I use Origin/QtiPlot only for plotting and Excel/OO–Calc for evaluation/calculation, because both programs are much more comfortable and powerful.

So, people use software such as Origin or QtiPlot to fit. Nevertheless, it is hard to guess the parameters for a given problem. Last week, I posted an article about sigmoid functions and how to use them.