Vlookup or interpolation, not quite sure

[ptc-2701309]

I'm converting an excel spreadsheet to mathcad, right now I'm in the process of adding a table of interferance values, the excel sheet that I'm going off of just uses Vlookup. but the value that the user enters most likely won't be the exact value.

the entered value will be the Bore diameter, the return values will be the maximum and minimum tolerances.

I've tried searching on here but haven't really found anything similar, also, I'm not the best programmer, so I'm hoping that the solution is fairly simple 🙂

oh, and I'm using MathCAD 14.0

[ptc-1368288]

You seem be looking for intermediate values of the min/max tolerances. It may not apply directly from imagining that the tol would fit in between. A machinery specialist should opinion on that ! Whatever, if you would want a linear tol in between and as fine as desired, you could do for every use. You can generalise the work and tabulate based on the proportional parts 0,0.1, 0.2 ...1 . I have posted an entire procedure and work sheet yesterday, for a complete tabulation of another 3 vectors application, you would simply need modify the lspline by a linterp.

jmG

[ptc-2701309]

I should probably clarify what I'm looking for, the 2nd and 3rd column are fixed values, those don't need to be interpolated. The user input isn't going to be exactly what is on the table. so say the user enters a value of 1.6, and the column values around it are 1.5 and 2.5, it will use the value corresponding to the 1.5 values for max and minimum interferances since 1.6 is >= 1.5

[ptc-1368288]

� you are asking for Mathcad doing what a year 3 kid would do w/o Mathcad. Certainly feasible, but why spend brain for brainless task ? In a more advanced application, this tool does exist in Mathcad for the up/down spline search coefficients that goes with interp(,,,,)

jmG

[TomGutman]

You're looking for the largest value not exceeding the target value. Mathcad does not have a built in function to do that. But it's fairly easy to program a linear search to do that. A binary search is more complicated and a bit faster. With 40 odd entries the binary search will be faster, but probably not enough to matter.

Given the nature of your data those decimal places in the bore are not really meaningful. Why not just use integers (or, at the very low end, simple fractions)?

You could also do an interpolation. That is easier than a search. The results will not be exactly the same as the Excel, and the resulting tolerance values will not be neat numbers. Depending on what you are doing with them, and what standards you are following, that may or may not matter.
__________________
� � � � Tom Gutman

[ptc-1368288]

>You could also do an interpolation<<br> __________________________

He said NO interpolation, just round off then up/down for the corresponding tolerances. Probably as the attached ?

jmG

[StuartBruff]

On 8/6/2009 11:17:58 AM, Theheadtwo wrote:
== I've tried searching on here but haven't really found anything similar, also, I'm not the best programmer, so I'm hoping that the solution is fairly simple 🙂

Is the attached something like what you want?

Stuart

[ptc-1368288]

>Is the attached something like what you want? <<br> _______________________

Maybe not, try 18.4 ...18.6 as per the project intent.

jmG

[StuartBruff]

On 8/6/2009 3:13:36 PM, jmG wrote:
>Is the attached something like what you want? <<br> == Maybe not, try 18.4 ...18.6 as per the project intent.

I've tried that range, in steps of 0.01, and the function consistently returns the values corresponding to 18.001, which I believe is what is required.

In what way do you think it is incorrect, Jean?

Stuart

[ptc-1368288]

On 8/6/2009 4:49:55 PM, stuartafbruff wrote:
>On 8/6/2009 3:13:36 PM, jmG wrote:
>>Is the attached something like what you want? <<br> >== Maybe not, try 18.4 ...18.6 as per
>the project intent.
>
>I've tried that range, in steps of 0.01,
>and the function consistently returns
>the values corresponding to 18.001,
>which I believe is what is required.
>
>In what way do you think it is
>incorrect, Jean?
>
>Stuart
_______________________________

My understanding was that for the user fractional part > XX.5 the collab wanted the upper tolerances ... vice versa < XX.5 the lower tolerances. I'm sure the collab will come back more specifically.

Thanks for your collaboration, Stuart.

Jean

[ptc-2701309]

cool, thank you guys for all your help, I'm looking through it now to see if it is what I'm looking for, I opened the Unk tolerance file and I'm getting null pointer error messages at the moment.

Also, I do understand it is a brainless task I'm asking MathCAD to do, :p. But it is one that my boss is asking me to do to demonstrate a little bit of MathCAD to him to replace some of our Excel files. The more painstaking part of this project is deciphering the excel file and all the built in unit checks they did.

[ptc-2701309]

Thanks Jean! it works perfectly :), I just had to save it as a MCD 11 file and it works, yay for the search function on the forum.

Hmm, I do have one problem though, the user will be entering the value with units (the bore is calculated from another user entered value), if there is some way to have it remove the units before it tries to find the value on the table that would work, or if I could just add values to the table.

Oh, and I just have one more question, if I wanted it to round the up rather than down for the values on the table, what would I have to change, I imagine I'll be using that as well in the near future 🙂

[ptc-1368288]

On 8/11/2009 10:28:46 AM, Theheadtwo wrote:
>Thanks Jean! it works
>perfectly :), I just had to
>save it as a MCD 11 file and
>it works, yay for the search
>function on the forum.

Hmm, I
>do have one problem though,
>the user will be entering the
>value with units (the bore is
>calculated from another user
>entered value), if there is
>some way to have it remove the
>units before it tries to find
>the value on the table that
>would work, or if I could just
>add values to the table.

Oh,
>and I just have one more
>question, if I wanted it to
>round the up rather than down
>for the values on the table,
>what would I have to change, I
>imagine I'll be using that as
>well in the near future 🙂
_______________________________

Plenty of collabs deal and work with units, I don't. Repost the work sheet with your unit example. For the round up/down, you may have to use floor/ceiling.

Jean