Quote:
Originally Posted by Ether
You can do curve fitting in Excel (or OpenOffice or LibreOffice) using the "trendline" function.
See this Excel spreadsheet. |
If the model you want to fit is not supported by "trendline" -- for example you think
y=p1+p2/(x-p3) might fit the data better than, say, a polynomial -- you can create and fit your own model using
Tools|Solver as shown in
this spreadsheet.
[edit1]
Hat tip to Joe Ross: In Excel, cell E1 can be coded directly as sumxmy2(B1:B20,C1:C20) instead of creating cells D1:D20 (if a least-squares fit objective function is desired).
[/edit1]]
[edit2]
Note that not only the
model but also the
objective function is user-configurable using the tools|solver modeling technique. For example, suppose that instead of minimizing the sum of the squares of the model errors (least squares fit) you want to find the parameters which produce the minimum maximum absolute error. This is easily accomplished by making cells D1:D20 equal to the absolute model error (for each data point), and making cell E1 equal to the maximum of cells D:D20. Then use tools|solver to minimize cell E1. Note: this does not work well (or at all) in Excel because of the algorithms used; and in OpenOffice takes several minutes (instead of seconds) but does find a solution. For the spreadsheet linked in this post, OO3.3 found the
following solution for minimum maximum error: y = -0.2651 -0.419013/(x-1.3281551). Here's a
graph of the solution. The solid black line is the raw data. The dashed black line is the inverted data. The solid red line is the linearization function, which uses only 2 adds and 1 divide.
[/edit2]