|
That doesn't ring true. The range of data consists of two columns, with a known value that lies within one column. The function should determine the interpolated value within the other column. Therefore I wouldn't expect that any function would be trying to grab data out of the "EXCEL" range you specified. ...and yes, interpolation is simple until you're doing it 5 million times in a spreadsheet... therefore the ".xll".
|
|
|
|
|
M.Slipper wrote: The range of data consists of two columns, with a known value that lies within one column. The function should determine the interpolated value within the other column.
That's extrapolation. Interpolation fills in missing values and you thus need a scenario similar to the following:
x1(known) | x(unknown) | x2(known)
So you need values on either side of the unknown value which is what I already explained. If in earlier versions of Excel there was a column limitation, your .xll would be coded to be aware that IX was the ultimate column possible and thus would not consider columns beyond IX - most likely the reason for the #VALUE error and the reason you continue to get the error even in spreadsheets (e.g. Excel 2007) that no longer have this limitation.
If excel can't handle it (and really, why are you trying to do 5,000,000 calculations using Excel??) write a c++ function. Then dump the data back into excel by calling the COM server. The other thing you might want to think about is how sparse is your data that you have to interpolate 5,000,000 times?
...that mortally intolerable truth; that all deep, earnest thinking is but the intrepid effort of the soul to keep the open independence of her sea; while the wildest winds of heaven and earth conspire to cast her on the treacherous, slavish shore.
|
|
|
|
|
Evidently I didn't make my explanation clear enough. I have two columns representing two separate data arrays. What I'm trying to do is clearly interpolation, not extrapolation. I know a value that lies within two adjacent data points within one of the arrays. I'm trying to interpolate between the two corresponding points in the other array...again interpolation, not extrapolation. Clearly simple stuff, but not if it can't be done in one Excel cell per calculation, and not fast enough if it can't be done by a function in an ".xll". the 5 million was an exaggeration to make you aware that doing these multiple calculations in VBA would require too much computational time. The InterpX function in the Interp32.xll add worked just fine in Excel 2003. The issue is having the arrays in Excel columns beyond column IV.
Example:
The following works fine in either Excel 2003 or Excel 2007:
=InterpX(C$670,Report!$IU$734:$IU$765,Report!$IV$734:$IV$765,TRUE)
The issue comes into play when one /or both of the arrays are moved beyond column IV.
Example:
=InterpX(C$670,Report!$IW$734:$IW$765,Report!$IX$734:$IX$765,TRUE)
|
|
|
|
|
To work around the column limitation can you link to a column in another sheet? Send row IX data to Sheet2 column A1, for example and use that in the interpX call?
...that mortally intolerable truth; that all deep, earnest thinking is but the intrepid effort of the soul to keep the open independence of her sea; while the wildest winds of heaven and earth conspire to cast her on the treacherous, slavish shore.
|
|
|
|
|
I've thought of that, but my Excel workbook already has well over 20 sheets. It's getting way too hard to manage, that's why I upgraded to Excel 2007. I had already run out of fonts in Excel 2003, and I thought the additional fonts & columns in 2007 would be a godsend. There has to be a way to upgrade the Interp32.xll addin with an updated InterpX that will allow the use of the additional columns available in Excel 2007. Hopefully the Gentleman (JChampion) that created Interp32.xll will read my frustration and apply his expertise to upgrade the addin. I'm sure the "X" number of scientists and engineers using Excel beyond its limited capabilities will be appreciative.
|
|
|
|
|
I haven't had a chance to download and look at the source, but the limitation could very well be hard-coded. You can email the author directly (look for one of his posts and then click the Email link) or you can also post a reply under the article (it should notify him). Maybe he can shed some more light on the limitation problem. I seem to be out of solutions/suggestions at the moment.
...that mortally intolerable truth; that all deep, earnest thinking is but the intrepid effort of the soul to keep the open independence of her sea; while the wildest winds of heaven and earth conspire to cast her on the treacherous, slavish shore.
|
|
|
|
|
Thanks....
The link to the article & source is provided below
http://www.codeproject.com/KB/macros/InterpolationAddin.aspx?df=100&forumid=25034&exp=0&select=1094573
|
|
|
|
|
Had a quick look at the source. There's quite a few places in there where the limits are defined as 255. I think this line from Xlcall.h is the important one though:
#define xlUDF 255
From MSDN:
xlUDF
Calls a user-defined function (UDF). This function allows a DLL to call Visual Basic for Applications (VBA) user-defined functions, XLM macro language functions, and registered functions contained in other add-ins.
[...]
Zero or more arguments to the user-defined function. When you are calling this function in versions earlier than Excel 2007, the maximum number of additional arguments that can be passed is 29, which is 30 including pxFnRef. In Excel Microsoft Office 2007, this limit is raised to 254, which is 255 including pxFnRef.
Looks like it could be hard-coded into Excel itself. Now, I'm not an expert on this but if you are trying to pass cell references beyond the 255 column limit this could be what is causing the problem.
...that mortally intolerable truth; that all deep, earnest thinking is but the intrepid effort of the soul to keep the open independence of her sea; while the wildest winds of heaven and earth conspire to cast her on the treacherous, slavish shore.
|
|
|
|
|
This Call seems to have corrected the max number of arguments issue but not necessarily a column limitation... Excel 2003 and versions before have 256 columns (0:255). Excel 2007 has 16384 columns and I expect that it is the limitation keeping InterpX from working. So I guess you're on the right track. Maybe there is another 255 imbeded in the code elsewhere.
Also, there is a microsoft site
http://msdn.microsoft.com/en-us/library/aa730920.aspx[^]
that addresses some issues with "Developing Add-ins (XLLs) in Excel 2007".
If I had the time and inclination I would tackle this issue, but with my current workload that isn't possible. If you know how to contact the original author of the InterpX function & Interp32.xll article (JChampion), maybe he would again tackle the mods needed to enable his Interp32.xll to work in Excel 2007.
Again, thanks for any and all your help.
-Michael Slipper
michael.slipper@navy.mil
modified on Tuesday, September 23, 2008 5:32 PM
|
|
|
|
|
"Math-making seems the opposite of automatic, which is why scientists long thought it had nothing to do with our ancient, pre-verbal size-em-up ways. Yet a host of new studies suggests that the two number systems, the bestial and celestial, may be profoundly related, an insight with potentially broad implications for math education. "
NY Times article[^].
An interesting article from the New York Times about the human capacity for mathematics and its implications for math education.
...that mortally intolerable truth; that all deep, earnest thinking is but the intrepid effort of the soul to keep the open independence of her sea; while the wildest winds of heaven and earth conspire to cast her on the treacherous, slavish shore.
|
|
|
|
|
That was pretty damn interesting. I like the 'bestial' & 'celestial' thing they had going there, kinda reflects how I feel about it. I also took their coloured dots test and I got 100%. 
|
|
|
|
|
It's hard to find articles like that. I usually get them from the NYTimes or the CBC.
Speaking of the CBC, you may enjoy their science program, Quirks and Quarks[^].
Glad you enjoyed that.
...that mortally intolerable truth; that all deep, earnest thinking is but the intrepid effort of the soul to keep the open independence of her sea; while the wildest winds of heaven and earth conspire to cast her on the treacherous, slavish shore.
|
|
|
|
|
73Zeppelin wrote: Speaking of the CBC, you may enjoy their science program, Quirks and Quarks[^].
Glad you enjoyed that.
Thanks!  ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
|
|
|
|
|
73Zeppelin wrote: Speaking of the CBC, you may enjoy their science program, Quirks and Quarks
I'm going to have to check that out. Thanks for the link
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Paul Conrad wrote: I'm going to have to check that out. Thanks for the link
No problem - it's a very good program, award winning even!
...that mortally intolerable truth; that all deep, earnest thinking is but the intrepid effort of the soul to keep the open independence of her sea; while the wildest winds of heaven and earth conspire to cast her on the treacherous, slavish shore.
|
|
|
|
|
That's very interesting indeed 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
can anyone tell me the algorithm for the pseudo random number generator? and how it works, i tried searching the net but to no avail..
thanks in advance,
nico
|
|
|
|
|
|
niconicx wrote: i tried searching the net but to no avail..
Or this[^] one. 
If you don't have the data, you're just another a**hole with an opinion.
|
|
|
|
|
See also here [^].
If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.
This is going on my arrogant assumptions. You may have a superb reason why I'm completely wrong.
-- Iain Clarke
[My articles]
|
|
|
|
|
Hey, I don't think that six you rolled is completly random. Since there are more pips on that face than the opposing face (one), it likely be up more often than not.
"Love people and use things, not love things and use people." - Unknown
"The brick walls are there for a reason...to stop the people who don't want it badly enough." - Randy Pausch
|
|
|
|
|
Well I'm working on the anti-gravity vacuum glass box... 
If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.
This is going on my arrogant assumptions. You may have a superb reason why I'm completely wrong.
-- Iain Clarke
[My articles]
|
|
|
|
|
DavidCrow wrote: there are more pips on that face than the opposing face (one), it likely be up more often than not.
Not really; they're only one-sixth as deep as the one on the opposite side.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I know, but the mass distribution is different. 
If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.
This is going on my arrogant assumptions. You may have a superb reason why I'm completely wrong.
-- Iain Clarke
[My articles]
|
|
|
|
|
Roger Wright wrote: Not really;
How so?
Roger Wright wrote: ...they're only one-sixth as deep...
All pips are drilled to the same depth on a die.
"Love people and use things, not love things and use people." - Unknown
"The brick walls are there for a reason...to stop the people who don't want it badly enough." - Randy Pausch
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
