[calc] how compare "similar" numbers

English
,
#1

Hi folks,
I want to compare two numbers.
X is a result of calculations in the sheet.
Y is a “pure” number, a goal.
Since the first is a real number while the second is a fixed decimal number,
IF statement fails when comparing the X and Y
e.g. X=15,010402443… and is Y=15,01
thus, X dis-equal Y in mathematics

How can I overcome this issue?

Thanks!

#6
  • The second number is named “Y” later and given as 15,01. That’s not integer.
  • Calc doesn’t know an integer type.
    All numbers are represented as IEEE 754 Double internally.
  • That numbers are “similar” or “about the same” can mean very different things depending on the situation. The discussed approaches aren’t just more ore less “elegant” but appropriate or not depending on the case and the intentions.
  • ABS(X/Y-1)<eps_rel means a relative comparison and may be based on an assertion that both numbers have the same sign, and that Y is significantly different from zero. Different units for the same measurable are allowed, but must be correctly cancelled.
    Are +10 and -10.0001 “similar” enough?
    What 10 000 000 and 10 000 010? Difference is 10. If we are talking of your income in USD you won’t worry.
  • ABS(X-Y)<eps_abs is basicallly different, and in many cases you need to know the unit to be able to decide. A dentist fitting a crown cannot tolerate a difference of more than 10 µm.

disask_ToleranceAbsoluteVsRelative.ods (28.7 KB)

#7

thank for the clarification!
I edited OP by fixing “integer” with “fixed decimal”
I wrote integer because in this case scenario, any Y number can be represented as integer since Y*100 is an integer…

anyhow, how do you overcome the issue?

#8

What issue?

#9

The “issue” seems to be that the fundamental distinction between “absolute” (better “difference-based”?) and relative (better “quotient-based”?) comparison must be understood and regarded.
The example attached to my comment above was made to help insofar. There is not ONE optimal solution but the solution (formula) must be chosen in an appropriate way depending on the specific situation.

#2

Use one of the rounding functions. ROUND(), ROUNDDOWN(), ROUNDSIG().

NB; an integer is a whole number that can be positive, negative, or zero.

#4

in the meantime I used this formula:
ABS(X/Y-1)<0,01

but I think yours is more elegant, so I will switch to it
btw I haven’t tested yours extensively…

#5

This formula can produce inaccurate results when Y is close to 0.
Common practice:

ABS(X-Y)<EPS

Where the EPS constant is chosen based on the context of the problem. For example, for monetary amounts, it might be 0.005.

#3

= ABS(X-Y) < 0,012345…

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.