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

Community Tip - You can subscribe to a forum, label or individual post and receive email notifications when someone posts a new topic or reply. Learn more! X

4-Participant

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!

1 ACCEPTED SOLUTION

Accepted Solutions
24-Ruby V
(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.

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:

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.

Prime 6 worksheet attached

3 REPLIES 3
21-Topaz I
(To:OL_10351728)

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.

24-Ruby V
(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.

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:

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.

Prime 6 worksheet attached

24-Ruby V
(To:Werner_E)

Here is another, tricky one

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:

Announcements
Top Tags