How do I create a worksheet function for polynomial data fit

G

Guest

I am trying to get a data fit (x,y) to y=ax2+bx+c using worksheet function.
If you could help me, I would appreciate very much. PS. I don't want to use
excel's matrix functions.

Thanks
 
G

Guest

Assuming you are looking to find the coefficients from a data set, you can
use the function "LINEST". For two columns of data (x in A column and y in B
column) and assuming 10 points of data (A1:B10):

Select 3 contiguous cells in a row. (i.e. C3:F3). Enter the following formula:

=LINEST(B1:B10,(A1:A10)^{1,2})

Press Ctrl-Shift-Enter. (Press these together or it won't work). Selecting
Cells C3:F3 afterwords and then clicking on the formula displayed above will
highlight the selected "X" and "Y" cells. You can drag the bottom right
corner of each area to include more or less data as desired. If LINEST covers
empty cells it will not work.

The other way is to use the X-Y graphing function, adding a trendline and
then displaying the equation. Add trendlines by right clicking the graphed
data set. Right-clicking on the displayed equation gives you the ability to
change the format to a number and increasing the values past the decimal to
increase accuracy of the equation. LINEST does this automatically without
needing a graph.

C3 will have the A value, D3 will have the B value and F3 will be C.
 
G

Guest

Thank you. I spent one whole day and trying to solve the problem using
matrixes and least square method. Thanks again
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top