Sverweis Not working properly

[sm�ller-2]

Hallo,

i have a special problem. My sverweis function is not working properly. Sverweis gives more results than realy exists it should only be 2 results. 152 is no valid result. I added a Paint Picture of my special problem.

I need to know the loadcase of the maximum damage (<1 for each)

Thank you,

BR Stefan

[Werner_E]

Maybe we should translate for other readers here:

erweitern -> augment

sverweis -> vlookup

You experience a simple effect of bad precision. Match and the various lookup command only look within a certain precision and nor for an exact match. You may try if setting TOL to a lower value than the default 10^-3 would help but otherwise I guess there is no solution other than to write your own lookup routines.

One additional idea: You may try to replace sverweis(a,b,2) by something like sverweis(10^8*a, 10^8*b, 2) and see if it helps.

Here are examples of those workarounds:

[sm�ller-2]

I dotn understand why is there a workaround required im searching the same value out of a matrix which contains that same value? Why are there coming wrong values? Im still confused. So does sverweis only consider the first 3 digits after the comma for comparison? What is the TOL value? Where do i change that value?

why are there different values for that TOL numbers?

Hmm ill set the TOL value to the lowest possible number. What is the lowest TOL Number.

Theres also a CTOL. Hmm will use the lowest available numbers and see if that does help.

Thats wired:

TOL and CTOL

TOL—Worksheet variable that controls the convergence precision of some functions such as integrals, derivatives, odesolve and the root functions.

CTOL—Worksheet variable that controls how closely a constraint in a solve block must be met for a solution to be acceptable when using find, minerr, minimize, ormaximize. For example, if a constraint such as x < 2 must be satisfied to within CTOL before a solution is returned, and if CTOL = 0.001 (the default), then this constraint is satisfied when x < 2.001.

I want to be x<2 so 2 is Not OK  x should be maximum 1,9999999 and not above 2 can i set CTOL also to 0?

I really do want to fulfill my boundary conditions 100 percent. I dont want to calculate wrong stuff. Fulfillment of boundary conditions are crucial to me.

Thank you for that fast help!

BR Stefan

[sm�ller-2]

Ok thank you ill set the TOL and CTOL value to the lowest possible number. Seems to be a Mathcad problem itselg.

Thats wired:

TOL and CTOL

TOL—Worksheet variable that controls the convergence precision of some functions such as integrals, derivatives, odesolve and the root functions.

CTOL—Worksheet variable that controls how closely a constraint in a solve block must be met for a solution to be acceptable when using find, minerr, minimize, ormaximize. For example, if a constraint such as x < 2 must be satisfied to within CTOL before a solution is returned, and if CTOL = 0.001 (the default), then this constraint is satisfied when x < 2.001.

Fulfillment of boundary conditions are crucial to me. Is there a way to avoid this? If x<2 than 2,001 is not ok and 2 is also not ok and 2*10-5 is also not OK to me.

Il try your suggests and will see if that works.

What are the lowest TOL and CTOL values?

Thank you br Stefan

[Werner_E · Accepted solution]

> Il try your suggests and will see if that works.

It should - see the screenshot of an example in my reply above.

> What are the lowest TOL and CTOL values?

Mathcad uses IEEE format for storing it numbers which usually is good for a precision up to 15 or 16 decimals. So obviously it does not make much sense to set values lower than 10^-15.

Setting TOL (CTOL does not affect the precision in your case) may influence other calculations in your sheet (a numerical integral or a solve block may fail to converge if TOL or CTOL is too low). So you will have to balance the value of TOL in a way your sheet is still working OK.

The variant with the faktor may be a better alternative even though it means more typing and worse readability.

I'd prefer to write my own match or vlookup routines like this:

[sm�ller-2]

You are my man 😃

Thank you!