View Single Post
#5
05-15-2012, 01:43 PM
 Ether systems engineer (retired) no team Join Date: Nov 2009 Rookie Year: 1969 Location: US Posts: 5,049
Re: paper: Team 1114 Controls and Programming Overview 2012

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]

Last edited by Ether : 05-15-2012 at 04:49 PM.