Formula evaluation error when testing for equality

Posted by: michael.lucks on 5 September 2021, 10:15 pm EST

  • Posted 5 September 2021, 10:15 pm EST

    In the following example, the formulas in columns D and E test for equality between 2 numbers. In Excel, the formulas evaluate to TRUE, as expected -- but in Wijmo they evaluate to FALSE.

    https://jsfiddle.net/lucksm1/tva9uz23/

    Why the discrepancy and what can be done to get the correct result?

    Thanks,
    Mike
  • Replied 7 September 2021, 5:37 am EST

    I've discovered another formula problem, similar to the one mentioned above, but in this case I'm trying to do an arithmetic comparison on operands that are strings consisting of numerical digits.

    In the following example, the formula in column D gives a different result (FALSE) in Wijmo 5-20212-812 from what we get in our older version of Wijmo from 2018 (TRUE).

    https://jsfiddle.net/lucksm1/6kxbyd17/

    The operands are strings consisting entirely of numerical digits. The older Wijmo processed the formula like Excel -- i.e. the string operands are treated as numbers, yielding the expected result TRUE.

    Apparently the new Wijmo treats the operands as strings and returns FALSE. In order to get the expected result, the VALUE function has to be applied to the operands (Column E).

    Is this a bug or a feature? Our application uses a large database of formulas that will have to be modified if we can't recover the original behavior.

    Thanks,
    Mike



  • Replied 7 September 2021, 12:25 am EST

    Hello,

    We apologize, but this is expected behavior as from the build 5.20211.792 the FlexSheet's string values are no longer implicitly cast to other types when aggregates are calculated or comparison operations are performed. For example, the result of '=1="1"' and '=10>"5"' calculation is now false. Previously, it was true. For more information you may refer to the link below:

    https://www.grapecity.com/blogs/wijmo-build-5-20211-792-is-available

    Let us know if you still have any doubts or issues.

    Regards
  • Replied 7 September 2021, 7:28 am EST

    Thanks for the explanation.

    That explains example #2, but it doesn't address the first case from the original post.

    https://jsfiddle.net/lucksm1/tva9uz23/

    Note that in Column E the operands are converted to numbers before executing the operation -- but the formula still evaluates to FALSE.

    Here is a revised version of example #1 where the operands are numerical from the outset.

    https://jsfiddle.net/lucksm1/m5czr6uj/

    Column B and Column C both evaluate to exactly 13.8, but the less-than-or-equal-to operation in column D returns FALSE.

    This might be a floating point roundoff issue because the formula will return TRUE if you lower the value of Column C slightly.

    We also have this problem in our older version of Wijmo from 2018 -- but it does work correctly in Excel.
  • Replied 7 September 2021, 9:30 pm EST

    Hello,

    Sorry for the delayed response, the difference in behavior has occurred because in FlexSheet the calculation is done automatically in JavaScript. And as you said the JavaScript supports 64-bit floating-point numbers the calculated value for Column B is 13.799999999999999 which is less than 13.80, therefore, the Column D formula value is false.

    Let us know if you still have any doubts or issues.

    Regards
  • Replied 8 September 2021, 6:31 pm EST

    Thanks again for the explanation.

    Unfortunately this is a serious problem for our application, which is expected to produce results identical to Excel. Is there any possible solution using Wijmo?
  • Replied 9 September 2021, 6:08 pm EST

    Hello,

    We have escalated the issue to our dev team with an internal tracking id WJM-20880. We will let you know as soon as we get any updates on this.

    Regards
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels