## 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 ;)