nonlinear response scaling in excel?
nonlinear response scaling in excel?
(OP)
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!
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!





RE: nonlinear response scaling in excel?
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.
RE: nonlinear response scaling in excel?
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!
RE: nonlinear response scaling in excel?
I hope this helps..
RE: nonlinear response scaling in excel?
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 ;)