Please add an INTERPOLATE function. For vector or array data.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please add this function which may be used either for a vector set of data or
for a 2 dimensional array of data.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
 
You have SLOPE, INTERCEPT and LINEST, making an interpolation is rather easy
from there.
best wishes
 
Bernard Liengme wrote...
You have SLOPE, INTERCEPT and LINEST, making an interpolation is rather easy
from there.
Click to expand...
....

LINEST would be gross overkill. Besides, FORECAST and TREND would be
far easier than any of these. The problem is that using it to
interpolate in a table (TBL) for a value (v) involves expressions like

TREND(OFFSET(TBL,MATCH(v,INDEX(TBL,0,XvalCol)),YvalCol-1,2,1),
OFFSET(TBL,MATCH(v,INDEX(TBL,0,XvalCol)),XvalCol-1,2,1),v)

You may like these, but they are a bit long. When they're general like
this, they eat 3 levels of nested function calls. A simpler approach
would be handy, but I suspect we won't see it in Excel until a few
months after Hell organizes an Ice Hockey league.
 
I would agree with the OP on this one. LINEST etc. are curve fittin
functions, not interpolating functions. Sometimes when you have
table of data, it is easier/preferable to approximate the function as
series of straight lines connecting the data points (similar to what
line chart/XY chart does) rather than attempt to fit the data to
curve
 
Which you can do with LINEST etc. or more directly with TREND or
FORECAST by including just the relevant surrounding points (instead of
the entire data set) for a given interpolation.

Jerry
 
Correct you can use the built in functions for interpolation easil
enough, once you've located the interval containing the desired value.
And if you have a small dataset and/or only need to perform th
interpolation a few time, manually searching through the dataset fo
the interval wouldn't be a problem. On the other hand, if you have
large dataset or need to perform several interpolations, manuall
searching for the interval and adjusting formulas accordingly get
tedious. As Harlan points out, composing a formula to search for th
interval then interpolate is not a "pretty" formula. I wanted a
interpolate function when I converted to Excel from Quattro Pro (whic
had a built in interpolation function), so I wrote a UDF, but it woul
be nice if Microsoft would include it as a built in function.
 
As nice as it would be for MS to meet every need for every function
from every customer, XL is flexible enough to allow one to add new
capabilities.

For a few options for piecewise linear interpolation, see a discussion
"Graph look up" in .excel.charting. You can search the google.com
archives of the XL newsgroups for a post by Dave Braden for code based
on cubic splines and visit www.xlrotor.com for code based on Bezier
curves.


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Tushar Mehta wrote...
As nice as it would be for MS to meet every need for every function
from every customer, XL is flexible enough to allow one to add new
capabilities.
Click to expand...

The logical implications of this argument are that because Excel is
extensible in terms of functions, Microsoft need never add another.

As for meeting needs, when was the last time anyone who uses Excel
regularly needed any of the 3 Bessel functions? And for those who
do need Bessel functions, it would have been nice if Microsoft had
allowed for fractional parameters.

Linear interpolation is a commonly needed bit of functionality. Not,
it'd seem, as frequently requested as summing cells by color or font
attributes, but much more common than finding solutions to
cylindrical harmonics.

It'd be good for Microsoft to add some built-in functions to Excel.
But, if you're going to be an absolutist about this (denying the need
or usefullness of any more functions), perhaps we could discuss new
capabilities that would make Excel *MORE* *CONSISTENT*.

Top of my list would be rewriting the formula parser (which probably
hasn't been touched since Excel 4) to accept nested function calls
more than 7 levels deep. As I've pointed out several times, Excel has
no trouble calculating such formulas, but it won't allow them to be
created or edited. How can they be? They can be created by other,
non-Microsoft spreadsheets, saved in .XLS format (so the file format
in addition to the recalc engine isn't the problem here), and opened
in Excel. Would that be a useful new feature?
For a few options for piecewise linear interpolation, see a discussion
"Graph look up" in .excel.charting. You can search the google.com
archives of the XL newsgroups for a post by Dave Braden for code based
on cubic splines and visit www.xlrotor.com for code based on Bezier
curves.
Click to expand...

Of course there are ways to do it. It's just that they're bulky and
nonintuitive. There are ways to calculate depreciation using only the
arithmetic functions and lots of cells for intermediate calculations.
Why does Excel include DB, DDB, SLN, SYD and VDB functions?

Your argument is flawed.
 
My internal reaction to Tushar's smug answer on this HUGE oversight with
Excel is much closer to indignation than Harlan's answer. This function
would be useful to businessmen, engineers and scientists across the spectrum.

Shame on the Excel managers for screwing up on this one. Apparently they've
lost touch. Too bad the competition isn't stronger to shake them up a little
bit.
 
tskoglund wrote...
....
Shame on the Excel managers for screwing up on this one. Apparently they've
lost touch. Too bad the competition isn't stronger to shake them up a little
bit.
Click to expand...
....

Never, ever accuse Microsoft of losing touch until you see their
revenues
decline quarter-to-quarter for a full year. Microsoft does what it
needs to do
AND NO MORE in order to keep the revenues flowing. The history of the
last two
Office 'upgrades', at least from the Excel perspective, is that they
don't
need to do much to get companies and individuals to upgrade. It'd be
irrational for them to do more. The irrational parties are anyone who
upgraded
from Office 2000.

You're absolutely right about the benefits of competition. The only
good news
here is that Microsoft did such a good job with its older versions
compared to
the dearth of new/interesting features in the the two latest versions
that
they do face competition . . . from their own older versions.

You want to make Microsoft actually WORK for their money? Don't upgrade
to
Office 2006, especially if it requires an OS upgrade to do so.
 
I wish it were that simple. But Office is a virtual monopoly, and the
legions of employess that work for organizations that standardize on it have
no vote.

I suppose the executives are "in touch" if the company remains profitable,
but the out-of-touchTushar Mehta's of Microsoft are giving me tremendous
encouragement to support and buy alternative products at least for home use.
How hard can it be to add an INTERPOLATE function? 2 hours of programming
time maybe?

At any rate, thanks for your humbling rebuttal to Tushar.
 
All politics and tirades about Microsoft's practices and market share aside:
As an engineer I frequently use tabular data from various published
references. Now I could develop a UDF for the original differential equation
from which the tabular data was created. (Then why did I buy the book?) Or
I could use SLOPE, LINEST and so forth as discussed in some of the notes
above in this thread. (But working out those nested formulae would take ten
times as long as doing it by hand on a piece of scratch paper.) Or I could
(and did) write my own UDF to do a simple linear interpolation between two
data points, something like =MYINTERPOLATE(Y-Data,X-Data,X-Point). But by
far the easiest way for me would be if it already existed as a standard
function available with Excel. I must confess, I really like Excel; it has
tremendous capabilities and has taken me way beyond where I could go, back in
the bad old days of sliderules. And I think the guys that built in all its
features and functions have done an amazing job. They thought of solutions
to problems I haven't even imagined yet. It just surprises me that not only
did they miss this obvious winner, but seem to want to argue about how it is
not worth doing. We seem to be forgetting that the fundamental purpose of
this tremendous spreadsheet program is to provide an EXCEL-lent tool for
scientists, engineers, business managers and the like to use in the execution
of their business. Making widgets or whatever. We engineers and business
managers do not simply exist to provide a market for a software company. A
lesson I've learned from over 25 years of marriage: RECOGNIZE THE CORRECT
ANSWER WHEN TOLD! Excel should have an easy-to-use interpolate function.

And while they're at it, how about making it easy to categorize your own
UDFs and have them available from a master file of some sort? It would be
great to be able to cluster the many complex UDFs I've written into groupings
like "Steam Formulae", "Combustion Formulae", and so on without having to go
through the relatively painful process it now requires to accomplish such a
goal. And be able to call them up even if the original worksheet where I
created them is not currently open. Can it be done now? Yes. Is it easy?
No. Please . . . make it easy.

Keep up the good work. Make it better.

Mark
 
Mark Schreiber said:
Or I could
(and did) write my own UDF to do a simple linear interpolation between two
data points, something like =MYINTERPOLATE(Y-Data,X-Data,X-Point). But by
far the easiest way for me would be if it already existed as a standard
function available with Excel.
Click to expand...

It does. It's called FORECAST.
 
Hi Yes, FAO the Excel development team:

Excel would greatly benefit from an interpolate function with inputs similar
to the trend function. Often I have known X and Y values, and a new set of X
values for which I would like to interpolate corresponding Y values. The
original Y values are rarely linear and form all sorts of complex lines when
graphed. Trend therefore isn't suitable for this. Currently, I have to use
a substantial and impractical formula to manually linearly interpolate new Y
values, and when this is applied to many y-value sets, it quickly becomes
impractically cumbersome to use Excel. An in-built interpolate function
would fix this. Other software, I am told, does support this feature and its
ommission in Excel is all too apparent.

Thanks.
 
Back
Top