XL Functions

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)

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.

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

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.

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.

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.

Don't know, but found this:

http://www.blueleafsoftware.com/Products/Dagra/LinearInterpolationExcel.php

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!

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

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?

Visual Basic for Applications in Excel

Does this help?

http://excel-formulas.blogspot.com/2009/09/linear-interpolation-in-excel.html

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!

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/

Yes, it will.

http://octave.sourceforge.net/io/function/xlsread.html

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/

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.

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.

In Excel 2013:-

File

----Options

--------Add-ins

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

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

http://xongrid.sourceforge.net/

Thanks

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.

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.

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.

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) + e^x (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?

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.

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

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

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.

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

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!

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.

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.

Missed the point, didn't you?

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

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

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.

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.

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.

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.

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.

Brains?

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.

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

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 K_v. 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 K₁ and K₂, 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.

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

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

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 x⁴ but with changed coefficients. Order 6 has highest x⁵, 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 T⁵ 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*10³⁶ !! 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.

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:

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.

Thanks again for everybody's help.

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

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

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

Thanks, I'll try that

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

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

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.

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.

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.

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!

Start with the data you have:

Then, plot the data in a scatter plot:

Single-click on one of the data points with the left mouse button.

This selects the data series. Hover the cursor over one of the data points and click the right mouse button. This brings up a sub-menu.

Select 'Add Trendline'. A line is plotted more or less through the existing data. Usually (or I think maybe by default), the trendline is linear. This will not work here!

Left-click on the trendline to select it, and then right-click to bring up the sub-menu. Select 'Format Trendline'.

For this example, I selected Polynomial of Order 2. Also click the 'Display Equation on Chart' box.

You now have:

Next, copy or transfer the equation coefficients into another line in the original data set in the Cv column, so an entered valve position in the Valve Position column will calculate the Cv corresponding to it.

Specifically, enter into C9 the formula: =0.154*B9^2+10.459*B9-23.657

Enter 32 in cell B9, and you should get this:

Another interpolation at 82.3º:

In this example, I assumed that the Cv at 0º was 0. Notice that the curve did not fit the data all that well. This decreases confidence in the interpolated value, so use discretion about depending on it.

That would be OK if any of the trendlines available on XL gave a reasonable fit to the data points, but that may not be the case in a practical application.

If you enter any old set of data points and plot scatter with smooth lines, you get a nice curve. So XL must have the values between the plotted points in its memory. It's frustrating that AFAIK you can't access them, as I said in #17.

Sometime it may be better off using the Visual Basic module to get around that.

OK thanks, I might try it sometime.

I see on a previous post,... I had mentioned it... this is a somewhat old post...

That's right, Mikey kicked it off again

Not me! It was edwards42 !

Your first reply to me was well taken - I just haven't responded yet.

Sorry, yes, I hadn't noticed #59, thought yours was the first since 2015