When collecting and plotting "noisy"
data it is often useful to have Microsoft Excel plot a Trend Line through it. If
that data is to be used for further work, it may be necessary to have an X-Y table
of the Trend Line. That is not easy to get and this paper will show how to do it.
The example below shows a microphone frequency response data
acquired by having a digital oscilloscope calculate the FFT of white noise. Even
though the primary data is an average of 100 FFT calculations, it is still pretty
jagged. But the 6th order polynomial Trend Line is a very good representation
of the actual response.
Since this microphone is intended to be used as a reference
mike for calibrated measurements, it is necessary to have an X-Y table of the
Trend Lines.
Excel does not provide that data but does provide information
in the form of the polynomial formula and the associated coefficients, which will
enable you to calculate that table yourself.
To get that formula printed on the graph, put the cursor on
the Trend Line and right-click. You will get a small window. Select "Format
Trendline". That yields a larger window. Select the "Options" tab
and check "Display Formula on Chart", then click OK.
In this example, the low & high frequency data are completely
separate. I chose only the high frequency Trend Line to get its formula. You will
get something that looks like this:
That leaves a couple of problems: The formula is partially
unreadable and the coefficients don't have nearly enough decimal places to yield
the proper result.
To fix that, click on the chart and shrink the "Plot
Area" so there is a lot of empty space on top of the chart. Then click on
the formula and move it to the top where it can be seen. It will look like this:
Next, Right click on the formula. You will get a small window.
Select "Format Data Labels". A larger window will appear.
Select the "Number" tab. Scroll down and select
"Scientific". Then increment the "Decimal places" to at least
5 or 6. Click OK and the formula coefficients will now have as many decimal places
as you selected, as shown below.
You see that the formula only calls for values of X. The Y
value information of the original data is embedded in the coefficients. That is
why very high resolution of those numbers is important. The "E" designates
scientific notation.
Now, choose an area on your spreadsheet to perform the calculations.
You will need three more columns than the order of the polynomial. For this 6th
order polynomial I used 9 columns. For this example, let's assume you have chosen
columns D through L.
In the first column (D), copy & paste the whole column
of X values of your data file. That will make life a little easier when you plot
it later
Designate the 2nd column (E) for the new Y values of the Trendline,
but leave it empty for the moment.
Now enter each term of the Formula into a separate cell along
the row of the first line of your X data. Let's call it row 6.
In the column (F) to the right of your blank Y value, type
the coefficient of the 1st term of the formula and multiply it by the cell of
your corresponding X value, raised to the appropriate power. Pay attention if
the coefficient is negative.
The 1st term (in column F), will look like this: = 1.800973E-25*D6^6
The 2nd term (in column G), will look like this: = -1.713713E-20*D6^5
Follow that pattern.
The next to last term (in column K) will look like this: = -5.261019E-5*D6
The last term (in column L) is a constant and has no X reference but watch the
+/-sign.
When those cells are filled, go to your blank Y column (E6)
and type the formula =SUM(F6:L6). That is your Y value corresponding to the first
value of X.
Now select cells E through L from the row you've just completed
and copy them to the bottom of your X-value data. Excel will set the correct row
numbers when you copy the formulas.
You are finished ! The first few rows of the calculation RESULTS
will look like this:
|
|
|
|
|
|
|
|
|
D |
E |
F |
G |
H |
I |
J |
K |
L |
Freq |
y |
6 |
5 |
4 |
3 |
2 |
1 |
k |
930 |
-0.01163 |
1.16521E-07 |
-1.19221E-05 |
0.000527 |
-0.01104 |
0.087734 |
-0.04893 |
-0.03992 |
940 |
-0.0106 |
1.24244E-07 |
-1.2577E-05 |
0.000551 |
-0.0114 |
0.089631 |
-0.04945 |
-0.03992 |
950 |
-0.00955 |
1.32388E-07 |
-1.32604E-05 |
0.000574 |
-0.01176 |
0.091548 |
-0.04998 |
-0.03992 |
960 |
-0.00849 |
1.40973E-07 |
-1.39731E-05 |
0.000599 |
-0.01214 |
0.093485 |
-0.05051 |
-0.03992 |
970 |
-0.00742 |
1.50016E-07 |
-1.47162E-05 |
0.000624 |
-0.01252 |
0.095443 |
-0.05103 |
-0.03992 |
The X & Y columns (D & E) are the values for your
Trend line.
Check that by plotting them.
If you haven't made any mistakes and you plot them in the
same chart that has the Trendline, you'll have to temporarily delete the Trendline
in order to see the new data plotted under it. But save the file before you do
that.
Good luck !
|