nonlinear response scaling in excel? 5

[DVictor]

I'm a Instrument Tecnician at a Manufacturing Plant in NE Pennsylvania w/ a modicum of excel experience.

I'm using a datalogger which creates a timestamped row of values in a .CSV file of the raw, unscaled output from a sensor. To be clear one row is the time stamp and the adjacent row is the value.

During testing against a known standard I discovered the sensor was not linear and a simple math formula in excel would not work for scaling.

Is it possible in Excel to create a scaling formula (somehow taking various samples and plot the x/y values into a curve) that would account for the nonlinear nature of this sensor?

A push in the right direction would be greatly appreciated!

 

[GregLocock]

Yes. The easiest way is to plot the polynomial trend of your calibration curve and use that formula in your spreadsheet. (Look "trend" up in help, it is easy)

Another way that involves less complex maths but more messing about is to build a lookup table and then interpolate to find your reading.

Doubtless someone somewhere makes an add-in to do this.

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[DVictor]

Sorry I'm so dense but if I plot the curve with an Array Formula using the TREND function how do I apply the resultant formula to my data?

Also the help file says that TREND, "Returns values along a linear trend". Is there a way to perform a polynomial interpolation of the cal coordinates?

That would be awesome if a plugin exists that would automate this job but google returned nothing that looked appropriate.

Thanks for the help in advance. My spreadsheet kungfu is weak!

 

[briand2]

Just plot the data on a chart then right-click the curve and choose "Add Trendline". You are presented with options such as linear, logarithmic, etc; you decide which is likely to give the best fit. Under the Options tab, select "Display Equation On Chart". Click OK. The calculated curve will be drawn along with the original curve (so you can see how good the match is). Because the equation is displayed on the chart, you can just use that equation in your spreadsheet (but replacing x and y with your cell references).

I hope this helps..

 

[ColourfulFigsnDiags]

Another way, if you do not have a straight forward polynomial trend (eg the Cv vs blade angle curve for a control valve) is to do a least squares regression and solve it using the solver function.

For example, say you know that from your experimental data that the Cv vs. blade angle curve for a control valve looks sometyhing like a plot of y=arctan(x) ...

Enter blade angle and Cv data into adjacent columns (say starting at, $A$5 and $B$5). In the next column type a generic formula refering to your experimental blade angle (eg. cell $C$5 '=$C$1*(atan($C$2*$A5+$C$3))+$C$4') fill this down and shove some dummy values for the constants in cells C1:C4 ...

Now in the next column take the difference of your formula and the experimental values and square it ... eg. in $D$5 '=($C5-$B5)^2' . Fill this down for all your experimental data.

Total all these differences in another cell (eg. $D$1 '=sum($D$5:$D$42)'). Clearly if your parameters in C1:C4 make a perfect fit for your experimental data, this sum should be zero.

So now you use the excel 'Solver' function (under the tools menu- you may have to include it as an 'addin' if you do not see it. Then simply use Solver to minimse your sum of differences (Cell $D$1) by changing your constants cells $C$1:$C$4 ...

With this method you can fit ANY type of equation you can think of. It takes a little practice, but it is a method well worht learning.


I'll give you one guess as to why I've just used this method