cactus13
Automotive
- Jul 16, 2001
- 25
I have been bashing around with Excel again and have a peculiar situation, in my opinion I'm doing everything right but still Excel makes a difference. Welle here's what I want:
I have about 150 files with data. In these files there are about 50000 samples. In this data there are peaks, I want to detect the height of these peaks. Major problem is the trend that's underneath the data. I want this trend to be removed, so called baseline adjustment.
Just to try and solve the problem I did the following:
I copied the data to a new sheet, manually erased the peaks from the data and plotted the data. then I let Excel create a trend line through this dataset. A sixth order polynom gave me the best result.
I picked the formula and entered it in Excel because I want the values and not the description of the polynom so I can subtract the polynom results from the actual data leaving the peaks 'untouched'.
Using the same x-axis data the formula Excel produces very different results from what I expected when I calculated the values of the polynom. The graph does not in any way look like the trendline Excel suggested.
Excel gave me the following trendline:
y = -0.0023x6 + 0.1582x5 - 4.3364x4 + 61.02x3 - 463.93x2 + 1806.9x - 3319.1
which I entered in the first cell like this and than copied all the way down... the cell as A1 here changes needly for evey new row so that's not the problem.
= -0.0023*A1^x6 + 0.1582*A1^5 - 4.3364*A1^4 + 61.02*A1^3 - 463.93*A1^2 + 1806.9*A1 - 3319.1
The first 1500 samples it looks OK, but than the difference between my line (based on the Excel trendline equation) and the Excel trendline explodes to infinity...
Can some one give me a suggestion what might be wrong? Excel giving the wrong formula or is this some kind of a bug in Excel??
Cactus
I have about 150 files with data. In these files there are about 50000 samples. In this data there are peaks, I want to detect the height of these peaks. Major problem is the trend that's underneath the data. I want this trend to be removed, so called baseline adjustment.
Just to try and solve the problem I did the following:
I copied the data to a new sheet, manually erased the peaks from the data and plotted the data. then I let Excel create a trend line through this dataset. A sixth order polynom gave me the best result.
I picked the formula and entered it in Excel because I want the values and not the description of the polynom so I can subtract the polynom results from the actual data leaving the peaks 'untouched'.
Using the same x-axis data the formula Excel produces very different results from what I expected when I calculated the values of the polynom. The graph does not in any way look like the trendline Excel suggested.
Excel gave me the following trendline:
y = -0.0023x6 + 0.1582x5 - 4.3364x4 + 61.02x3 - 463.93x2 + 1806.9x - 3319.1
which I entered in the first cell like this and than copied all the way down... the cell as A1 here changes needly for evey new row so that's not the problem.
= -0.0023*A1^x6 + 0.1582*A1^5 - 4.3364*A1^4 + 61.02*A1^3 - 463.93*A1^2 + 1806.9*A1 - 3319.1
The first 1500 samples it looks OK, but than the difference between my line (based on the Excel trendline equation) and the Excel trendline explodes to infinity...
Can some one give me a suggestion what might be wrong? Excel giving the wrong formula or is this some kind of a bug in Excel??
Cactus