Originally Posted by Ether
You can do curve fitting in Excel (or OpenOffice or LibreOffice) using the "trendline" function.
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
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).
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.