×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# nonlinear response scaling in excel?5

## 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!

### RE: nonlinear response scaling in excel?

2
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.

### RE: nonlinear response scaling in excel?

(OP)
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.

### RE: nonlinear response scaling in excel?

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..

### RE: nonlinear response scaling in excel?

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!