Excel's puzzling behavior when seeing redundant parentheses or addition of 0

Thanks so much! I shall post my message there. Thanks for your kind and quick responses.

Hi,

Thanks - but there are two issues:

1. If we do what you suggest, then without the extra ( ... ) , one will get the -2.22e-16 . With the extra (..) one must get 0. But the excel behavior is OPPOSITE. Without the (...) , it obtains 0, but with (...) extra, it obtains -2.22e-16. Please see how I compiled the evidence (I include the full C sources for your reproduction. The file is attached. So that is more puzzling.
2. There is another example I supplied. To repeat, try (4/3-1)*3 -1 and (4/3-1)*3 - 1 + 0. Strangely, the addition of the "+ 0" at the end has the exact same impact as putting an extra (... ) . Why?

Since my attachment may not come thru, I'll add it below.

V1: 4/3 in FP128, round to FP64, then (-1)*3-1 in FP64

================================================================================

4/3 [FP128] = 1.33333333333333333333333333333333327e+00

round to FP64 = 1.33333333333333325932e+00

- 1 [FP64] = 3.33333333333333259318e-01

* 3 [FP64] = 9.99999999999999777955e-01

- 1 [FP64] FINAL = -2.22044604925031308085e-16

================================================================================

V2: (4/3-1) in FP128, round to FP64, then *3-1 in FP64

================================================================================

4/3 [FP128] = 1.33333333333333333333333333333333327e+00

- 1 [FP128] = 3.33333333333333333333333333333333269e-01

round to FP64 = 3.33333333333333314830e-01

* 3 [FP64] = 1.00000000000000000000e+00

- 1 [FP64] FINAL = 0.00000000000000000000e+00

================================================================================

V3: (4/3-1)*3 in FP128, round to FP64, then -1 in FP64

================================================================================

4/3 [FP128] = 1.33333333333333333333333333333333327e+00

- 1 [FP128] = 3.33333333333333333333333333333333269e-01

* 3 [FP128] = 9.99999999999999999999999999999999807e-01

round to FP64 = 1.00000000000000000000e+00

- 1 [FP64] FINAL = 0.00000000000000000000e+00

================================================================================

V4: (4/3-1)*3-1 entirely in FP128, then round to FP64

================================================================================

4/3 [FP128] = 1.33333333333333333333333333333333327e+00

- 1 [FP128] = 3.33333333333333333333333333333333269e-01

* 3 [FP128] = 9.99999999999999999999999999999999807e-01

- 1 [FP128] = -1.92592994438723585305597794258492732e-34

round to FP64 FINAL = -1.92592994438723585306e-34

================================================================================

BONUS: Pure FP64 (all operations)

================================================================================

4/3 [FP64] = 1.33333333333333325932e+00

- 1 [FP64] = 3.33333333333333259318e-01

* 3 [FP64] = 9.99999999999999777955e-01

- 1 [FP64] FINAL = -2.22044604925031308085e-16

================================================================================

BONUS: Pure FP128 (all operations)

================================================================================

4/3 [FP128] = 1.33333333333333333333333333333333327e+00

- 1 [FP128] = 3.33333333333333333333333333333333269e-01

* 3 [FP128] = 9.99999999999999999999999999999999807e-01

- 1 [FP128] FINAL = -1.92592994438723585305597794258492732e-34

===

#include <stdio.h>

#include <quadmath.h> // For __float128 (FP128)

void print_q128(const char *label, __float128 q) {

char buf[64];

quadmath_snprintf(buf, sizeof(buf), "%.35Qe", q);

printf("    %s = %s\n", label, buf);

}

void print_f64(const char *label, double d) {

printf("    %s = %.20e\n", label, d);

}

int main() {

// Mathematically: (4/3 - 1) * 3 - 1 = (1/3) * 3 - 1 = 1 - 1 = 0

printf("================================================================================\n");

printf("V1: 4/3 in FP128, round to FP64, then (-1)*3-1 in FP64\n");

printf("================================================================================\n");

{

    __float128 q = 4.0Q / 3.0Q;

    print_q128("4/3 [FP128]", q);

    double d = (double)q;

    print_f64("round to FP64", d);

    double step1 = d - 1.0;

    print_f64("- 1 [FP64]", step1);

    double step2 = step1 * 3.0;

    print_f64("* 3 [FP64]", step2);

    double result = step2 - 1.0;

    print_f64("- 1 [FP64] FINAL", result);

}

printf("\n");

printf("================================================================================\n");

printf("V2: (4/3-1) in FP128, round to FP64, then *3-1 in FP64\n");

printf("================================================================================\n");

{

    __float128 q1 = 4.0Q / 3.0Q;

    print_q128("4/3 [FP128]", q1);

    __float128 q2 = q1 - 1.0Q;

    print_q128("- 1 [FP128]", q2);

    double d = (double)q2;

    print_f64("round to FP64", d);

    double step1 = d * 3.0;

    print_f64("* 3 [FP64]", step1);

    double result = step1 - 1.0;

    print_f64("- 1 [FP64] FINAL", result);

}

printf("\n");

printf("================================================================================\n");

printf("V3: (4/3-1)*3 in FP128, round to FP64, then -1 in FP64\n");

printf("================================================================================\n");

{

    __float128 q1 = 4.0Q / 3.0Q;

    print_q128("4/3 [FP128]", q1);

    __float128 q2 = q1 - 1.0Q;

    print_q128("- 1 [FP128]", q2);

    __float128 q3 = q2 * 3.0Q;

    print_q128("* 3 [FP128]", q3);

    double d = (double)q3;

    print_f64("round to FP64", d);

    double result = d - 1.0;

    print_f64("- 1 [FP64] FINAL", result);

}

printf("\n");

printf("================================================================================\n");

printf("================================================================================\n");

printf("V4: (4/3-1)*3-1 entirely in FP128, then round to FP64\n");

printf("================================================================================\n");

{

    __float128 q1 = 4.0Q / 3.0Q;

    print_q128("4/3 [FP128]", q1);

    __float128 q2 = q1 - 1.0Q;

    print_q128("- 1 [FP128]", q2);

    __float128 q3 = q2 * 3.0Q;

    print_q128("* 3 [FP128]", q3);

    __float128 q4 = q3 - 1.0Q;

    print_q128("- 1 [FP128]", q4);

    double result = (double)q4;

    print_f64("round to FP64 FINAL", result);

}

printf("\n");

printf("================================================================================\n");

printf("BONUS: Pure FP64 (all operations)\n");

printf("================================================================================\n");

{

    double d1 = 4.0 / 3.0;

    print_f64("4/3 [FP64]", d1);

    double d2 = d1 - 1.0;

    print_f64("- 1 [FP64]", d2);

    double d3 = d2 * 3.0;

    print_f64("* 3 [FP64]", d3);

    double result = d3 - 1.0;

    print_f64("- 1 [FP64] FINAL", result);

}

printf("\n");

printf("================================================================================\n");

printf("BONUS: Pure FP128 (all operations)\n");

printf("================================================================================\n");

{

    __float128 q1 = 4.0Q / 3.0Q;

    print_q128("4/3 [FP128]", q1);

    __float128 q2 = q1 - 1.0Q;

    print_q128("- 1 [FP128]", q2);

    __float128 q3 = q2 * 3.0Q;

    print_q128("* 3 [FP128]", q3);

    __float128 result = q3 - 1.0Q;

    print_q128("- 1 [FP128] FINAL", result);

}

return 0;

}

Please understand that our forum is a public platform, and we will modify your question to cover the personal information in the description. Kindly ensure that you hide these personal or organization information next time you post error or some information to protect personal data. 

Dear @Ganesh Gopalakrishnan,

Welcome to Microsoft Q&A Forum!

Thank you for sharing the details. The behavior you observed in Excel is due to floating-point precision, not a functional error. Excel uses IEEE 754 floating-point arithmetic, so adding redundant parentheses or a trailing +0 can slightly change the internal computation order, resulting in very small rounding differences (e.g., -2.22045E-16 instead of 0). Direct equality checks (=0) fail because the value is not exactly zero, so this causes direct equality checks like IF(A5=0, "flower", "missile") to fail. You can see more details in here: Floating-point arithmetic may give inaccurate results in Excel

Also, I have personally run a few tests on my Mac version 16.103 to clarify your concern, and I confirm that I have the same results as you.

Therefore, I have run more tests and found these formulas that may fix this issue for you:

At cell A5, I use =((4/3-1)*3-1) or =(4/3-1)*3-1+0

However, in cell C1, to avoid direct equality checks with floating-point results, I use this formula instead: =IF(ABS(A5)<1E-9,"flower","missile"). This checks if the value is “close enough” to zero and the result is flower, not missile

Alternatively, I also use this formula in cell C5: =IF(ROUND(A5,9)=0,"flower","missile"). This normalizes small floating-point errors, and the result is still flower, not missile

I hope this information can give you more insights of it and please kindly give me any feedback in comment section. Wish you a pleasant day!

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

The AI answer I obtained said "order of evaluation may change" is the reason. But what conceivable different order exists here??