cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - You can Bookmark boards, posts or articles that you'd like to access again easily! X

MathCAD function question

OL_10351728
4-Participant

MathCAD function question

Hi! I'm trying to make a template for engineering calculation for hydraulic cylinder sizes and was wondering if there was a way to do the following: I have a function that spits out a number. I'd like it to be able to find the closest match to this from an array/matrix that I've already made. For example, lets say the calculation spits out a minimum necessary size of 3.054. The next available hydraulic cylinder sizes that would work is 3.25, then 3.5 then 3.75 then 4. I'd like it to spit out 3.25 as the answer. Thank you!

ACCEPTED SOLUTION

Accepted Solutions
Werner_E
25-Diamond I
(To:OL_10351728)

Here is a similar approach using "vlookup" instead of "match".
Its one step less and the vector must not necessarily be sorted (but I guess it would be in your case.

Werner_E_0-1656507164212.png

But no matter if you use "match" or "vlookup" - there is a huge drawback in using those functions because they depend on the system variable TOL. The default value of TOL is 10^-3 and using for example 3.001 you get a wrong result:

Werner_E_1-1656507291685.png

Obviously the correct result should be 3.25. You would have to set TOL to a lower value (in the worksheet or via the menu) to get the correct result.

 

So I would rather suggest not using "match" or "vlookup" and use a small, self written user-defined function to do the job.

Werner_E_0-1656508238877.png

Prime 6 worksheet attached

View solution in original post

3 REPLIES 3

Hi,

What you are looking for is the match function with the "geq" greater than or equal modifier.

There is no testing to ensure you at least get one match.

Capture.JPG

Werner_E
25-Diamond I
(To:OL_10351728)

Here is a similar approach using "vlookup" instead of "match".
Its one step less and the vector must not necessarily be sorted (but I guess it would be in your case.

Werner_E_0-1656507164212.png

But no matter if you use "match" or "vlookup" - there is a huge drawback in using those functions because they depend on the system variable TOL. The default value of TOL is 10^-3 and using for example 3.001 you get a wrong result:

Werner_E_1-1656507291685.png

Obviously the correct result should be 3.25. You would have to set TOL to a lower value (in the worksheet or via the menu) to get the correct result.

 

So I would rather suggest not using "match" or "vlookup" and use a small, self written user-defined function to do the job.

Werner_E_0-1656508238877.png

Prime 6 worksheet attached

Werner_E
25-Diamond I
(To:Werner_E)

Here is another, tricky one

Werner_E_0-1656510830870.png

OK, it doesn't work if minCyl is smaller than the lowest value in he vector.

But I would turn it into a utility function anyway and while doing so we could also apply some error checking:

Werner_E_1-1656510939103.png

 

Announcements

Top Tags