Find exact formula of a curve and fix the bumps in the curve

sampledata.xlsxI created a chart from my data points, and my excel "curve" is flat at the beginning and bumpy at the end. I want to be able to adjust the curve so that the beginning is not flat and the end is not bumpy. I am new to statistics, and looking for a way to adjust the data. Any help would be appreciated. Thanks. Also would like to get the exact formula of my bumpy curve not of the trendline. Thanks.

I attached a sample file (not my exact data, but a similar situation. So how do I find the exact formula of this curve and how to fix it to eliminate the flat and bumpy. Thanks.

To start, we would need a sample workbook of the data. Click on Go Advanced --> Manage Attachments to upload.

I have smoothed the data using the LOESS smoothing technique. This is a UDF created in the 22 Worksheet class module.

The main reason the trail end of your data is returning non-uniform results is because of the discourse in the y-axis starting at cell B29 (going from 23,24,25 to 30). The LOESS function (column D) smoothes the data to a 17 moving point regression (http://peltiertech.com/loess-smoothing-in-excel/ for more information), and cells B31:D33 show a forecast of what that data would look like if the trend were to keep going.

The equation derived from the trendline shown at the top of the chart is based on the LOESS values, not the actual Cost data.

Hope this helps!

Unless I'm mistaken, you already had the exact formula for the original line based on the trendline you had for your raw data. You had asked for a formula to fix the "curves and bumps", and that is what LOESS smoothing does.

Is the data empirical or theoretical? It appears to be empirical, in which the trendline formula is the derived theoretical formula for the raw data based on linear regression.

If you are unaware between the distinction between empirical and theoretical results, I suggest you do some quick googling and read up on it. It is imperative you know the difference, especially when working with data. The "theoretical" results are something that has been derived (eg. F=ma, V=IR, etc.). These are results in "ideal" situations, conditions which are almost never met. It is then necessary to take your "empirical" data, and see how much deviation there is from your "theoretical" calculations.

I say this because no empirical data should return the "exact formula" of the theoretical results due to the imperfect nature of our world.

Long story short, the trendline represents the closest approximation as to the behavior of the data you put into the chart. However, you must note that the trendline is only as good as the data - garbage data in, garbage analysis out.

Hope this helps!