Curve fitting with formulas

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

Polynomials are "linear" functions and linear least squares regressions can be performed using the LINEST() function https://support.microsoft.com/en-us/...a-fa7abf772b6d You can also implement non-linear regressions in Excel, if your data requires a non-linear model. You have not provided any details, but the short answer to your question is, Yes, you can do curve fitting and/or data smoothing in Excel and other spreadsheets/

Something like this?? I fitted it to a quartic polynomial. In G7, copied down:

=INDEX(LINEST($C$2:$C$63,(ROW($C$2:$C$63)-ROW($C$2)+1)^{1,2,3,4}),1,ROWS(G$7:G7))

You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

I added the regression equation on the chart. However, these are rounded. the ones delivered by the formula are mathematically exact.

You have specified a line chart for your chart data, which means that excel will use count numbers (1,2,3,...) for the X values rather than the (unevenly spaced) time values in column A. What do you want to use for the x values in these regressions? Your LINEST() function will need a column containing these values.

Well spotted Mr S!!

I don't follow you. This is the sheet I posted in reply to you. What do you expect to see? Where do you expect to see it? The components of the LoBF are there, the fitted curve is shown...

Yep:

=SIGN($H$2)*(ABS($H$2*ROWS($F$2:F2))^4)+SIGN($H$3)*(ABS($H$3*ROWS($F$2:F2))^3)+SIGN($H$4)*(ABS($H$4*ROWS($F$2:F2))^2)+($H$5*ROWS($F$2:F2))+$H$6

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

That (like the graph you plotted) is fitted with 1,2,3, etc on the X-axis. it is up to you to determine if that is correct for your purposes.

Titters loudly. Yep. I fouled up there. So, I have removed the trendline and fitted the experimental points.

Possibly. I went as far as 4th. As far as I recall, 5th order looked worse, so I stopped. You can add a trendline and try it for yourself. Do you know how to do that?

Look at Cell H2, in my last file upload. It reads:

=INDEX(LINEST($C$2:$C$63,(ROW($C$2:$C$63)-ROW($C$2)+1)^{1,2,3,4}),1,ROWS(H$2:H2))

Red: your Y data range.
Orange: a "counter" that sets your X-data range
Cyan: tells Excel that it's a quartic fit. If, for example, you want a fifth order fit, use {1,2,3,4,5} instead.
Blue: another counter. First row you have the formula in will be the 5th order exponent, the next the 4th and so on.

don't forget that in your Excel version you may have to use an array formula in those cells. See post 5.

If you have problmes, shout. Otherwise, you're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.