CR4® - The Engineer's Place for News and Discussion®


Previous in Forum: PLC Scaling for TC Type T   Next in Forum: Canon MG2210 Printer Driver for Windows 10
Close
Close
Close
58 comments
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118

XL Functions

11/17/2015 9:54 AM

Does anybody know a way of interpolating from a list of numbers in XL?

E.g. I have Kv (or Cv in US) figures for a butterfly valve given for disc angles 0°, 10° …..90°. I would like to enter an angle and get corresponding Kv, either linearly interpolated, or better still from a smoothed curve. Or the other way round. XL can do a plot of the data, showing either straight or smoothed lines, so the information must be in there somewhere.

This seems to me such a useful feature I'm surprised it isn't a standard function (unless it is and I can't find it). After all, XL has a huge list of functions, many of which I have no idea when or how to use!

__________________
Give masochists a fair crack of the whip
Register to Reply
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be "almost" Good Answers:

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, rate them!
Guru
Hobbies - CNC - New Member Hobbies - DIY Welding - New Member Engineering Fields - Electromechanical Engineering - New Member

Join Date: Aug 2007
Posts: 18984
Good Answers: 336
#1

Re: XL functions

11/17/2015 10:10 AM

This seems to me such a useful feature I'm surprised it isn't a standard function

So is a iterations feature for heat transfer. (other then financial iterations feature)

__________________
phoenix911
Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#5
In reply to #1

Re: XL functions

11/17/2015 11:29 AM

We could no doubt think of a few we'd like, but this below is from link in JavaHead's #3. So I'm not alone

Before we even get into the obtuse EXCEL calculation, let's point out that there is no excuse for Microsoft Excel not to have this function built in. MathCAD has it (and tons more cool functions!), MathCAD calls it the LINTERP function.

__________________
Give masochists a fair crack of the whip
Register to Reply
Guru
Hobbies - CNC - New Member Hobbies - DIY Welding - New Member Engineering Fields - Electromechanical Engineering - New Member

Join Date: Aug 2007
Posts: 18984
Good Answers: 336
#6
In reply to #5

Re: XL functions

11/17/2015 11:35 AM

thanks, also one can pass along the iterations to VB functions. But I never really looked into that.

The only reason I brought that up, about 20 years ago, I wrote a heat exchanger sizing program to specify shell and tubes.

The owner of the company wanted it to be in excel, I spent a lot of time trying to make the iterations work (without doing repetitive coding for the iterations), and after a lot of failures, I took a another path.

oh, btw, at the time, I did want to write it in Mathcad, or even 'C'.

__________________
phoenix911
Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#12
In reply to #6

Re: XL functions

11/17/2015 12:18 PM

Yes, in Mathcad it's easy enough. The trouble is Mathcad users over here are a bit thin on the ground, but everybody has Excel.

__________________
Give masochists a fair crack of the whip
Register to Reply
Guru
Hobbies - CNC - New Member Hobbies - DIY Welding - New Member Engineering Fields - Electromechanical Engineering - New Member

Join Date: Aug 2007
Posts: 18984
Good Answers: 336
#13
In reply to #12

Re: XL functions

11/17/2015 12:22 PM

that how it was with me, this was in the 90's. Surprising enough Mathcad is not that wide spread with Mechanical Engineers, but to each their own...

btw, I haven't has Mathcad is a very long time.

__________________
phoenix911
Register to Reply
Guru
Engineering Fields - Optical Engineering - Member Engineering Fields - Engineering Physics - Member Engineering Fields - Systems Engineering - Member

Join Date: Apr 2010
Location: Trantor
Posts: 5201
Good Answers: 620
#16
In reply to #5

Re: XL functions

11/17/2015 2:46 PM

I have my own copy of Mcad 2000 that I use all the time. I've been using different version of Mcad since the 90s. I love it, and when I'm forced to use Excel I feel like I've lost 50 IQ points. The interpolation functions are critical to what I do at work.

I've been able to migrate Mcad 2000 through Win XP and Win 7. I worry that when Win 10 hits I'll lose it. I'll avoid that 'upgrade' as long as I can.

__________________
Whiskey, women -- and astrophysics. Because sometimes a problem can't be solved with just whiskey and women.
Register to Reply
Guru
Technical Fields - Project Managers & Project Engineers - New Member

Join Date: Aug 2006
Location: Midwestern United States
Posts: 830
Good Answers: 73
#2

Re: XL functions

11/17/2015 10:11 AM
__________________
Reuters - Investigators found that the recent thread derailment in CR4 was caused by over-weight creatures of lore and request that membership DON'T FEED THE TROLLS.
Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#7
In reply to #2

Re: XL functions

11/17/2015 11:46 AM

Thanks but it's not quite what I'm looking for. I already did it for lists of 10, by ratio-ing the intervals, which works, but it needed loads of nested IF statements and I was hoping for something more direct. The approach with the MATCH functions might be a bit simpler, but not much.

One of the further links said he'd used a macro to do it with 1620 values, clearly out of the question with IF statements!

__________________
Give masochists a fair crack of the whip
Register to Reply
Guru
United Kingdom - Member - Not a New Member Hobbies - Musician - New Member Hobbies - Fishing - New Member

Join Date: May 2006
Location: Reading, Berkshire, UK. Going under cover.
Posts: 9559
Good Answers: 456
#3

Re: XL functions

11/17/2015 10:32 AM

Probly have to be a bit crafty with VBA, but if you knew how, you could use the built-in charting tools to fit a regression line, extract the coefficients of the equation, and use it for interpolating. It's easy enough to do it handraulically (plot graph, fit trendline of your choice, tick the "Display Equation on Chart" box and Bob's yer uncle).

__________________
"Love justice, you who rule the world" - Dante Alighieri
Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#8
In reply to #3

Re: XL functions

11/17/2015 11:53 AM

Yes, no problem if there is an equation that links the 2 lists to sufficient accuracy, that would be the ideal situation. But in the general case there isn't.

BTW what's VBA?

__________________
Give masochists a fair crack of the whip
Register to Reply
Guru
Hobbies - CNC - New Member Hobbies - DIY Welding - New Member Engineering Fields - Electromechanical Engineering - New Member

Join Date: Aug 2007
Posts: 18984
Good Answers: 336
#9
In reply to #8

Re: XL functions

11/17/2015 12:02 PM

Visual Basic for Applications in Excel

__________________
phoenix911
Register to Reply
Guru

Join Date: Apr 2010
Posts: 5759
Good Answers: 577
#4

Re: XL functions

11/17/2015 10:40 AM
Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#11
In reply to #4

Re: XL functions

11/17/2015 12:13 PM

Not much I'm afraid. I used the formula at each point in my approach, the difficulty is inputting all the IF statements to identify the point. There has to be an easier way!

__________________
Give masochists a fair crack of the whip
Register to Reply
Guru

Join Date: Apr 2010
Posts: 5759
Good Answers: 577
#14
In reply to #11

Re: XL functions

11/17/2015 1:54 PM

Matlab has a good interpolation function. If you're like me and don't want to shell out for the cost of a Matlab license, there's an open source look-alike called octave that runs a lot of Matlab code. Matlab will import excel data. I don't know if octave will do that or not.

https://www.gnu.org/software/octave/

Register to Reply
Guru

Join Date: Apr 2010
Posts: 5759
Good Answers: 577
#15
In reply to #14

Re: XL functions

11/17/2015 2:36 PM
Register to Reply
Guru

Join Date: Aug 2005
Location: Hemel Hempstead, UK
Posts: 4143
Good Answers: 230
#10

Re: XL functions

11/17/2015 12:04 PM

Clearly linear interpolation is simple arithmetic.

If you want non linear interpolation, you need to know what form the equation takes. Then use solver to fit the equation to the data points.

See here

http://ms-office.wonderhowto.com/how-to/do-nonlinear-interpolation-with-excels-solver-tool-357707/

__________________
We are alone in the universe, or, we are not. Either way it's incredible... Adapted from R. Buckminster Fuller/Arthur C. Clarke
Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#17
In reply to #10

Re: XL functions

11/17/2015 2:47 PM

The arithmetic is simple, it's putting in all the nested IF statements that's time-consuming. I believe XL allows up to 30 nested IFs, but I'd get bored well before that!

I'm not sure about needing an equation for non-linear interpolation. When you do an X-Y chart with smoothed lines, XL does a nice curve whether or not the data can be linked by an equation. It must have the data points in its memory somewhere, I'd just like to access them, even the linear case usually good enough.

Thanks for the link, interesting, but I can't find the solver on my XL, can you point me to it? Nearest thing I can see is What if/Goal seek. But I don't think it would be any direct help with what I'm trying to do.

__________________
Give masochists a fair crack of the whip
Register to Reply
Guru
Hobbies - Musician - Engineering Fields - Chemical Engineering - New Member Engineering Fields - Control Engineering - New Member Engineering Fields - Instrumentation Engineering - New Member

Join Date: Jan 2007
Location: Moses Lake, WA, USA, Thulcandra - The Silent Planet (C.S. Lewis)
Posts: 3950
Good Answers: 175
#21
In reply to #17

Re: XL functions

11/17/2015 6:26 PM

With several Excel installations I have had, it didn't load with the rest of the package.

The solver module is an add-in which can be accessed several different ways, depending on which version of Excel you have.

I would search Excel help for Solver.

Solver is great for curve fitting, given that you know the basic form of the function, but I don't know if it would help for your particular need.

__________________
"Reason is not automatic. Those who deny it cannot be conquered by it. Do not count on them. Leave them alone." - Ayn Rand
Register to Reply
Guru

Join Date: Aug 2005
Location: Hemel Hempstead, UK
Posts: 4143
Good Answers: 230
#25
In reply to #17

Re: XL functions

11/18/2015 5:41 AM

In Excel 2013:-

File

----Options

--------Add-ins

------------Solver Add-in

----------------Go

__________________
We are alone in the universe, or, we are not. Either way it's incredible... Adapted from R. Buckminster Fuller/Arthur C. Clarke
Register to Reply
Guru

Join Date: Apr 2010
Posts: 5759
Good Answers: 577
#18

Re: XL Functions

11/17/2015 3:15 PM
Register to Reply Score 1 for Good Answer
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#19
In reply to #18

Re: XL Functions

11/17/2015 3:25 PM

Thanks

__________________
Give masochists a fair crack of the whip
Register to Reply
Associate

Join Date: Sep 2015
Location: Texas
Posts: 50
Good Answers: 4
#20
In reply to #19

Re: XL Functions

11/17/2015 5:02 PM

I have done many variations of data processing programs using Excel worksheets without needing to resort to a VB macro by using two methods.

The first and most common is by using the data curve plotting and curve fitting equation (and, a word of caution here, particularly for the powers equations, they can be only approximate in some cases so you should always do a trial plot using the equation to see how closely that result matches the displayed fitted curve. Sometimes a bit of adjusting of the equations constants is required because the equation is a text display and those constants not displayed with the full number of accuracy digits).

The second method is to create a lookup table to identify the above and below data values using the => and =< functions respectfully, to capture the boundary data values and then use a standard interpolation calculation using your input values with those boundary values to get your desired data value result. (The same method can be used for two dimensional values using a two dimensional lookup table, and I have used that to interpolate steam table sections; but, that is a bit more complicated, so if you need that format, I can send you a sample of that worksheet).

No if statements are required for using either of these methods

If you want to pursue either of these methods in Excel then I will be glad to assist.

Register to Reply Score 1 for Good Answer
Guru

Join Date: Mar 2012
Location: Out of your mind! Not in sight!
Posts: 4007
Good Answers: 92
#24
In reply to #20

Re: XL Functions

11/18/2015 12:38 AM

Careful with the Lookup functions for this. It does not properly work if you do not capture the exemptions properly.

Do you do a linear interpolation between the boundary points? I assume you find these with the lookup function.

__________________
Common Sense Dictates
Register to Reply
Associate

Join Date: Sep 2015
Location: Texas
Posts: 50
Good Answers: 4
#36
In reply to #20

Re: XL Functions

11/18/2015 10:23 AM

Yes, that will be a linear interpolation and its accuracy as related to the a nonlinear curve between the reference boundry points will depend on the spacing of the reference data points. For just that reason, I prefer to use the XL graphing and curve fit equation functiuons for interpolating nonlinear data.

Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#42
In reply to #20

Re: XL Functions

11/18/2015 2:50 PM

Using data curve plotting to find an equation is fine if an equation exists to acceptable accuracy. But often there isn't one. Even if there is, it may only be useful one way. E,g. if it's of the form y = x*sin(x) + ex (and I doubt whether XL would find that, but you might get it by other means) it's simple to get y if x is known, but to get x if y is known goal seek or something would be needed.

You mention steam tables - some time back I needed to have saturated steam pressure as a function of temperature, and found P(T) = A*e^(B*C/(C + T)). Found A, B and C using Mathcad and it gives excellent agreement with tabulated values. I just tried a range of T = 0 - 100°C (tabulated values) on XL to see what it would do. Best fit was exponential, P(T) = D*e^(F*T) but fit was nowhere near as good as my formula. I tried (years ago) to find a formula giving saturated steam density (or specific volume) but didn't have much luck!

I can see that by finding the data points next below and above the one I'm interested in, for both variables, I can then interpolate using simple algebra. That's how I did it using nested IF statements. Perhaps I'm being slow, but I can't see how to use => and =< functions to do it. I'd be interested in your sample worksheet, thanks. Can you post it here or do you want me to send my email address?

__________________
Give masochists a fair crack of the whip
Register to Reply
Commentator

Join Date: Mar 2007
Location: PA, USA
Posts: 83
Good Answers: 2
#22

Re: XL Functions

11/18/2015 12:10 AM

Typically I've used some add-in cubic spline functions to smooth and obtain interpolated results. SRS1 Cubic Spline is one case that I remember.

__________________
"For a successful technology, reality must take precedence over public relations, for nature cannot be fooled." -Richard Feynman
Register to Reply
Commentator

Join Date: Mar 2007
Location: PA, USA
Posts: 83
Good Answers: 2
#32
In reply to #22

Re: XL Functions

11/18/2015 9:54 AM

EXAMPLE OF CUBIC SPLINE (not sure whether inserted image will show up):

__________________
"For a successful technology, reality must take precedence over public relations, for nature cannot be fooled." -Richard Feynman
Register to Reply
Guru
Hobbies - CNC - New Member Hobbies - DIY Welding - New Member Engineering Fields - Electromechanical Engineering - New Member

Join Date: Aug 2007
Posts: 18984
Good Answers: 336
#33
In reply to #32

Re: XL Functions

11/18/2015 9:57 AM

did you insert it?(using the camera icon), or did you paste it.

__________________
phoenix911
Register to Reply
Commentator

Join Date: Mar 2007
Location: PA, USA
Posts: 83
Good Answers: 2
#45
In reply to #33

Re: XL Functions

11/18/2015 4:32 PM

First I tried simply pasting a *.png image file.

Then I tried the camera icon, browsing to a *.jpg file.

Neither showed up in the preview, nor in the actual posting.

At any rate the add-in has a Bessel, Cubic, Linear, and One-Way spline functions. In the simple test the Bessel and One-Way were the same. The non-lineary splines looked more reasonable.

Perhaps one could take the actual tabulated data, leave out every other value, and then see which spline does the best job of duplicating the skipped data.

__________________
"For a successful technology, reality must take precedence over public relations, for nature cannot be fooled." -Richard Feynman
Register to Reply
Guru
Hobbies - CNC - New Member Hobbies - DIY Welding - New Member Engineering Fields - Electromechanical Engineering - New Member

Join Date: Aug 2007
Posts: 18984
Good Answers: 336
#34
In reply to #32

Re: XL Functions

11/18/2015 9:59 AM

I used to use quattro pro (spread sheet) way back when for curve fitting.... I think its now Corel.

__________________
phoenix911
Register to Reply
Guru

Join Date: Mar 2012
Location: Out of your mind! Not in sight!
Posts: 4007
Good Answers: 92
#23

Re: XL Functions

11/18/2015 12:33 AM

For an exponential function (maybe works for sinusoidal as well) I do the following:

1. insert data table

2. draw graph

3. establish trend line

4. show formula of trend line

5. Create cell using the formula and have the angle you want to interpolate to as x (this is an empty cell )

6. do a goal seek on the formula using your value

7. The empty cell will become your angle

8. include the angle and associated data point in the graph as a point to double check its on your line/trendline.

Sorry if I knew I would automatise the goal seek. But it works otherwise.The important bit is that the trendline fits over your data curve. If not its an approximation only.

Good luck!

__________________
Common Sense Dictates
Register to Reply
Commentator

Join Date: Sep 2014
Posts: 59
Good Answers: 2
#26

Re: XL Functions

11/18/2015 7:57 AM

There is an excellent reference book entitled " Excel for Scientists and Engineers" that addresses curve fitting methods. I if you are doing linear interpolation you can write a spreadsheet in 5 to 10 minutes depending on how fancy you want the formatting to be. Save the spreadsheet and use it next time you have data to analyze. Complaining about Microsoft not putting in a function strikes as lazy and petty. Spend your time learning how to do work instead of complaining here.

Register to Reply Score 2 for Off Topic
Guru
Hobbies - CNC - New Member Hobbies - DIY Welding - New Member Engineering Fields - Electromechanical Engineering - New Member

Join Date: Aug 2007
Posts: 18984
Good Answers: 336
#27
In reply to #26

Re: XL Functions

11/18/2015 8:01 AM

Complaining about Microsoft not putting in a function strikes as lazy and petty.

ahhh, since you didn't notice, that is why the question was posed. And frankly, I, as well as others are learning from the exchanges here.

Spend your time learning how to do work instead of complaining here.

Will you please stop complaining.... maybe learning something will help you.

__________________
phoenix911
Register to Reply
Commentator

Join Date: Sep 2014
Posts: 59
Good Answers: 2
#28
In reply to #27

Re: XL Functions

11/18/2015 8:11 AM

Missed the point, didn't you?

Register to Reply Score 2 for Off Topic
Guru
Hobbies - CNC - New Member Hobbies - DIY Welding - New Member Engineering Fields - Electromechanical Engineering - New Member

Join Date: Aug 2007
Posts: 18984
Good Answers: 336
#29
In reply to #28

Re: XL Functions

11/18/2015 8:21 AM

No, your suggestion of Excel for Scientists and Engineers was taken.

But yes, someone did miss the point, and still is.

__________________
phoenix911
Register to Reply
Commentator

Join Date: Sep 2014
Posts: 59
Good Answers: 2
#30
In reply to #29

Re: XL Functions

11/18/2015 8:43 AM

Yes, you are still missing the real issue here. It's called not knowing how to do research. Yes, I know how to do it. Hint: Google is your friend.

Register to Reply Score 1 for Off Topic
Guru
Hobbies - CNC - New Member Hobbies - DIY Welding - New Member Engineering Fields - Electromechanical Engineering - New Member

Join Date: Aug 2007
Posts: 18984
Good Answers: 336
#31
In reply to #30

Re: XL Functions

11/18/2015 8:58 AM

Some still don't get it, don't they.

Given the choice, while you may rely on Google, congratulations.

This forum, and the people that exchange here, people that I know, I have confidence in interacting with these people that have practical experience that they are willing to share, such as this.

Or are you saying you haven't learn anything here.

And btw, while you rely on google, google is not my friend, google is a tool to me, nothing more.

__________________
phoenix911
Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#44
In reply to #26

Re: XL Functions

11/18/2015 3:11 PM

Have you read my #5?

I try to help others on this forum when I can, and if I want to ask for help I will. This is one way, Google is another. I just said I'm surprised it isn't a standard function, that's an opinion, not a complaint, but I'm not bothered how it strikes you.

__________________
Give masochists a fair crack of the whip
Register to Reply
Active Contributor

Join Date: Mar 2012
Posts: 20
Good Answers: 4
#35

Re: XL Functions

11/18/2015 10:02 AM

IdeaSmith (#23) has the correct answer. The trend line allows you to choose from several forms of equations for a best fit.

Those who fondly mentioned Mathcad may be excited to know of the free version of Mathcad ( search for PTC Mathcad Express) that is the simple version of their standard (expensive) product. It seems to be the "real thing" for most of the work that I need to perform.

Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#43
In reply to #35

Re: XL Functions

11/18/2015 2:55 PM

The problem is, sometimes none of the forms of equations give anything approaching a good fit. Not surprising in the real world. Then you have to think of something else.

__________________
Give masochists a fair crack of the whip
Register to Reply
Guru

Join Date: Mar 2012
Location: Out of your mind! Not in sight!
Posts: 4007
Good Answers: 92
#47
In reply to #43

Re: XL Functions

11/18/2015 8:57 PM

Brains?

__________________
Common Sense Dictates
Register to Reply
Power-User

Join Date: Oct 2009
Posts: 177
Good Answers: 4
#37

Re: XL Functions

11/18/2015 10:29 AM

This equation will produce a polynomial equation of desired degree. It is excellent for interpolation (and completely falls apart for extrapolation at anything beyond linear).

=INDEX(LINEST($HJ$123:$HJ$131,$HI$123:$HI$131^{1,2,3,4,5,6}),1,1)

You will need to populate n+1 cells with this equation (work vertically). For instance, a 6th degree polynomial will require 7 cells to be populated: 6 for the individual multipliers, 1 for the constant. Bump the final digit by one each time.

=INDEX(LINEST($HJ$123:$HJ$131,$HI$123:$HI$131^{1,2,3,4,5,6}),1,1)
=INDEX(LINEST($HJ$123:$HJ$131,$HI$123:$HI$131^{1,2,3,4,5,6}),1,2)
=INDEX(LINEST($HJ$123:$HJ$131,$HI$123:$HI$131^{1,2,3,4,5,6}),1,3)
=INDEX(LINEST($HJ$123:$HJ$131,$HI$123:$HI$131^{1,2,3,4,5,6}),1,4)
=INDEX(LINEST($HJ$123:$HJ$131,$HI$123:$HI$131^{1,2,3,4,5,6}),1,5)
=INDEX(LINEST($HJ$123:$HJ$131,$HI$123:$HI$131^{1,2,3,4,5,6}),1,6)
=INDEX(LINEST($HJ$123:$HJ$131,$HI$123:$HI$131^{1,2,3,4,5,6}),1,7)

The 1,1 cell will produce the number to multiply by x^6. 1,2 times x^5, etc. If you look, the values Excel provides when you show the pretty curve equation on the graph with "Add Trendline..." are the rounded versions of what this produces.
Reduce the count in the {} brackets to reduce the order of the polynomial. Reduce the number of cells as well.

Hope this helps.

Register to Reply
Associate

Join Date: Sep 2015
Location: Texas
Posts: 50
Good Answers: 4
#38

Re: XL Functions

11/18/2015 11:14 AM

Can you post a display of your data for the problem?

Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#39
In reply to #38

Re: XL Functions

11/18/2015 12:46 PM

I was about to reply to your earlier posts but saw this one. I don't usually have much joy with copying things on to CR4 but this time I thought it had worked, then when I previewed it the table had vanished! So I've typed the data in. It's for a butterfly valve 250mm dia, column 1 disc angle, Column 2 valve Kv. I put it on XL and none of the options give much of a fit.

0° 0

10° 33

20° 128

30° 257

40° 429

50° 772

60° 1201

70° 2273

80° 3904

90° 4300

Also I started a thread recently about rectangular wire springs that you posted on. I've put the Roymech data on XL, and for constants K1 and K2, as function of b/t, the equations it comes up with, one power, one log give a pretty good fit. But I'm interested in the general case.

__________________
Give masochists a fair crack of the whip
Register to Reply
Guru

Join Date: Mar 2012
Location: Out of your mind! Not in sight!
Posts: 4007
Good Answers: 92
#48
In reply to #39

Re: XL Functions

11/18/2015 9:06 PM

Looks like a polynomial formula fits your data (order 6)So use the formula you get and use the technique I described above.

__________________
Common Sense Dictates
Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#50
In reply to #48

Re: XL Functions

11/19/2015 10:12 AM

OK thanks.

This is getting interesting! I put the data in XL and got the same chart, except that on yours for some reason some of the coefficients are given to more decimal places. I then calculated Kv vs angle from the polynomial (using your coefficients) and got

0 -5.4815
10 23629.106
20 4674467.9
30 18386571
40 -1.29E+09
50 -7.47E+10
60 -1.23E+12
70 -6.26E+13
80 -1.68E+15
90 -3.01E+15

Unless I'm doing something wrong, that's not much use, as I'm sure you'd agree.

Apart from that, perhaps I'm being unrealistic, but it seems unsatisfactory to have to go to order 6 to represent something, even if it worked. Also, with the valve example, more likely to use it to find disc angle when Kv is known, from flow and ΔP. Plotting it that way round, order 5 gives fair results. Oddly, trying various orders, highest power is same as the order (as on your chart) up to order 4, but order 5 has highest power x4 but with changed coefficients. Order 6 has highest x5, but the fit goes haywire. To be fair, in practice disc angle is usually kept between about 20 and 65° for good control, and if that is plotted maybe a better fit can be found. I'll try it sometime.

This reminds me of something years ago by the UK Water Research Centre, a formula to give solubility, C of oxygen in water as function of temperature T (at 1 atm). This went to T5 if memory serves, and some of the coefficients were like 0.000***, easy to enter wrongly, specially on a hand calculator. Not only that, but while it gave reasonable results for T between about 5 and 25°C it went wild outside that range. Apparently they weren't aware of the formula C (mg/l) = 468/(31.6 + T) which gives good results over a much wider range and is so simple I didn't even need to look up the constants, I can remember it from way back.

For the hell of it, I tried putting solubility on XL to see what it made of it. Using figures from minus 10 - 40°C, solubility from 468/(31.6 + T), an order 3 curve looked pretty good at first sight. I started to wonder why WRC used order 5. But when I calculated C using the order-3 equation the results were miles out. Order 5 was a bit better, but not right, it gave C falling from 0 - 25°, then increasing again, which it doesn't. And for T = -10° it gave C = 3*1036 !! If there's a way of getting the coefficients to greater accuracy than given on the chart, which might improve things, I haven't found it.

So I'm a bit sceptical about the polynomial method.

__________________
Give masochists a fair crack of the whip
Register to Reply
Power-User

Join Date: Oct 2009
Posts: 177
Good Answers: 4
#51
In reply to #50

Re: XL Functions

11/19/2015 10:36 AM

I used the method I presented above, took the Linest option to set the x intercept to 0, and got this graph:

The points presented are your data. The red line is the 6th order polynomial. Here's the data and the calculated points:

Data6th
000
103361
2012878
30257272
40429487
50772707
601,2011,201
702,2732,312
803,9043,879
904,3004,305
Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#54
In reply to #51

Re: XL Functions

11/19/2015 11:38 AM

OK thanks for that apologies all round! I'd dropped a cod on the XL front in entering a formula . Correcting that and following JohnDG's tip altered it to 10 decimal places, and got table below, which isn't too bad.

00-5.48147
103361.26838
2012878.64336
30257271.3767
40429486.4644
50772706.3751
6012011199.38
7022732307.003
8039043868.592
9043004283.005

Thanks again for everybody's help.

__________________
Give masochists a fair crack of the whip
Register to Reply
Power-User

Join Date: Oct 2009
Posts: 177
Good Answers: 4
#57
In reply to #54

Re: XL Functions

11/19/2015 5:29 PM

If you want to be rid of that negative for 0 degrees, set the y-intercept to 0.

Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#58
In reply to #57

Re: XL Functions

11/19/2015 5:43 PM

Point taken, though in the case of a valve you wouldn't need a Kv value at angle 0°.

__________________
Give masochists a fair crack of the whip
Register to Reply
Guru
United Kingdom - Member - Not a New Member Hobbies - Musician - New Member Hobbies - Fishing - New Member

Join Date: May 2006
Location: Reading, Berkshire, UK. Going under cover.
Posts: 9559
Good Answers: 456
#52
In reply to #50

Re: XL Functions

11/19/2015 10:38 AM

Right-click on the equation - one of the options lets you format the number display (number of decimal places etc.)

__________________
"Love justice, you who rule the world" - Dante Alighieri
Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#53
In reply to #52

Re: XL Functions

11/19/2015 10:45 AM

Thanks, I'll try that

__________________
Give masochists a fair crack of the whip
Register to Reply
Guru

Join Date: Mar 2007
Location: City of Light
Posts: 3960
Good Answers: 181
#55
In reply to #50

Re: XL Functions

11/19/2015 11:48 AM

The Kv* column presents values computed by a polynomial approach!My approach is a bit different than the curve fit procedures based on minimizing the errors. It is also limited to a number of points but it brings as you see a not too big difference between entry values and computed values. It is in fact very simple:A polynomial is defined as P(n)=Σai*Xî with "i" from 0 to n. The problem is to obtain the "ai" coefficients. This is again very simple if one has n+1 values pairs available.The n+1 pairs build a linear system (with n+1 equations) with respect to "ai" which can be solved by classical methods with a simple matrix computation. The result is as you see.In this example I used all values but zero, the resulting polynomial is 8th degree.As long as you stay in the used domain there are no problems.

A bit of mathematics. All computations made on XL sheet with the matrix functions of XL (inversion and product).

Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#56
In reply to #55

Re: XL Functions

11/19/2015 2:36 PM

That looks pretty good! I'll try to get my head round the details sometime. Thanks

__________________
Give masochists a fair crack of the whip
Register to Reply
Guru

Join Date: Mar 2007
Location: City of Light
Posts: 3960
Good Answers: 181
#40

Re: XL Functions

11/18/2015 12:56 PM

Use the "trend fit" command which can give you -if you ask for- the equation of the curve fit and the correlation coefficient R².

When you make the choice for the curve type let equation and R² be displayed and make the choice for the curve which corresponds to the highest R² value

This is an example of what you cab obtain. The 3rd column is the error between orriginal values and those obtained with the trend fit curve.

Register to Reply
Guru

Join Date: Jul 2005
Location: Stoke-on-Trent, UK
Posts: 3888
Good Answers: 118
#41
In reply to #40

Re: XL Functions

11/18/2015 2:15 PM

Thanks, and thanks to various other posters who made similar comments. I know how to use this feature of XL. The problem is it's only useful if XL can find an equation that fits the data reasonably well. But very often it can't, that's when something like Mathcad's interp function would save a lot of time.

You could play with the data I put in #38 to see an example.

__________________
Give masochists a fair crack of the whip
Register to Reply
Guru

Join Date: Mar 2007
Location: City of Light
Posts: 3960
Good Answers: 181
#46
In reply to #41

Re: XL Functions

11/18/2015 5:04 PM

XL makes the best fit by minimizing the errors square sum. It is a general procedure but not all experimental curves can be satisfied all over the range by same equation.

One of the simplest procedures is to generate a second degree curve through 3 successive points or a 3rd degree one through 4 successive points. This is simple by using XL, easier for the 2nd degree which you can consider as an osculating circle.

This is valid only for the region limited by the chosen points. It is any way better than a linear interpolation which does not considers the curvature between the points but only the secant.

Register to Reply
Guru

Join Date: Nov 2007
Location: Eastern Arizona mountains on Route 666 about a mile from God's country
Posts: 1677
Good Answers: 121
#49

Re: XL Functions

11/19/2015 9:57 AM

http://www.vbcode.com/

Here is a link to tutorials on Visual Basic Code which is used in creating EXCEL that will explain many different methods for modifying algorithms arguments to achieve whatever DATA manipulations needed by the user.

If you will invoke the "HELP" function in EXCEL and follow the step-by-step instructions given to access VBC it will make the process much clearer to follow.

It most likely will take several "trial-and-error" attempts to perfect the process.

Good luck!

__________________
They said; "Brain size?" I heard; "Train size?" so I said: "I'll take a small one, thank you."
Register to Reply
Register to Reply 58 comments
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be "almost" Good Answers:

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, rate them!
Copy to Clipboard

Users who posted comments:

Carl Van Wormer (1); Codemaster (17); GameJudge (3); IdeaSmith (4); JAlberts (3); JavaHead (1); JohnDG (2); Mikerho (1); N&P (3); nick name (3); pdef1949 (3); phoenix911 (9); Randall (2); Rixter (4); SHOCKHISCAN (1); Usbport (1)

Previous in Forum: PLC Scaling for TC Type T   Next in Forum: Canon MG2210 Printer Driver for Windows 10

Advertisement