View original (French)

=IF(ABS(valeur1 - valeur2) < tolérance, "Accepte", "Refuse")

Cmathieu -  
 Anonymous user -
Hello,

I would like to put an "IF" formula between cell 1 and cell 2 with an error message if the two cells are different. The problem is that the data in these cells can receive decimal numbers, so my rounding matches the visual display (as only 2 digits after the decimal point are visible on the document) but my error message shows a difference even though the visual of my two cells is identical.
How can I adjust my formula to accept a difference within the decimal range?

P.S.: I also thought about displaying an error message when my cell 1 and cell 2 differ by more than 0.1, but that blocks me too because it will only be in one direction. Sometimes my cell 1 will be over 0.10 and sometimes it will be my cell 2.

Thank you in advance for your help,

Configuration: iPhone / Safari 9.0

2 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    we can't see very well what you want to compare?
    is it the integers without considering the decimals
    is it the rounding to X decimal places, up, down, or closest?
    in the first case, comparing the integers:

    =IF(INT(C1)=INT(C2);....;...)

    for rounding:
    =IF(ROUND(C1;2)=ROUND(C2;2);...;...)
    for rounding up: ROUNDUP
    for rounding down: ROUNDDOWN
    with the number of decimal places to consider here ;2)

    and finally to allow or not a margin between the two values, which may be the best:

    =IF(ABS(C1-C2)<x;/...;...)

    which will take into account the absolute value of the difference whether it's positive or negative
    xxx being the acceptable margin (0.1 or 0.001 etc..)

    best regards

    The quality of the answer mainly depends on the clarity of the question, thank you!
    0
    1. Cmathieu
       
      Thank you for the response.
      To be more precise: case 1 = 12.5356. Case 2 = 12.5399. Since I round my numbers to two decimal places. I have a visual: case 1 = 12.53 and case 2 = 12.53, so the visual of both cases is identical.
      I would like my IF formula to only consider 12.53 (my two decimal places). Therefore, in this case, the formula should indicate that my result is correct because case 1 and case 2 are indeed equal to 12.53.
      I hope I am clearer :)
      0
      1. Anonymous user > Cmathieu
         

        Hello Cmathieu, Vaucluse is right:

        ROUND(12.5356,2) returns 12.53
        ROUND(12.5399,2) returns 12.53 as well

        So the comparison of the two values will be exact and IF() will return TRUE.

        But first read my message #2 which will surely be useful to you:
        you might not have to modify any formula!  😊
        0
      2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453 > Anonymous user
         
        Hello
        this is not quite according to your examples Albkan, because

        ___ ROUND(12.539,2) will return 12.54 (from 12.536)
        ___ ROUND(12.531 will return 12.53

        if we want to consider only the visible decimal part rather than the actual gap between the two values, we need to use:
        ROUNDDOWN(Cell;2) which will always return to the 2 lower decimals (or ROUNDUP for the upper ones, which is the same)

        But to eliminate a gap of less than 0.01 regardless of the integer, this is sufficient

        =IF(ABS(C1-C2)<0.01,"OK";"")

        cheers
        0
      3. Anonymous user > Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention  
         

        This message is also for Cmathieu.

        Hello Vaucluse,

        What you wrote is absolutely correct: I forgot to round the 2nd digit after the decimal based on the 3rd; thus, for my
        two examples, instead of 12.53, I should have written 12.54:

        ROUND(12.5356,2) returns 12.54
        ROUND(12.5399,2) also returns 12.54

        And as you indicated:

        ROUND(12.539,2) returns 12.54
        ROUND(12.531,2) returns 12.53

        ----------------------------------------------------------

        I still think it would be much
        easier for Cmathieu to use in his English Excel
        the French equivalent of this parameter:

        With Excel 2007

        Office button (= File menu), Excel Options, Advanced Options tab,
        scroll down until you see this section (blue band):
        “When calculating this workbook:”

        ☑ Set calculation with precision as displayed

        With Excel 2003

        Tools menu / Options...; Calculation tab:
        ☑ Calculate with precision as displayed

        ----------------------------------------------------------

        Calculations are always done with maximum precision of 15 digits
        after the decimal, then for a format with 2 digits after the decimal, rounding
        is done based on the 3rd, and the result is stored internally and displayed with
        only those 2 digits after the decimal. But be careful: the parameter
        above applies to the entire workbook (thus for all sheets).

        If this automatic rounding is not desired for just one of the sheets,
        the parameter should not be used and rounding functions should be used instead.

        Best regards.  😊
        0
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      So try with

      =IF(ABS(C1-C2)<0.01,"OK","")

      the "OK" because I don't know what you want to get out.
      sincerely
      0
  2. Anonymous user
     
    Hello Cmathieu,
    I believe that this solution from Patrice33740 will help you. ????
    But you will need to find the equivalent in your English Excel.
    0