[GL #93] Formulas #VALUE error

Tags: #<Tag:0x00007f0b03ba3910> #<Tag:0x00007f0b03ba3758>

Does the pro formulas plugin calculate recursively like a spreadsheet?

http://jsfiddle.net/ceturc/q2szo45r/4/

When I try to use the pro formulas plugin (v1.10.1) and refer to another cell (that refers to a later cell in my data table), I receive #VALUE error or the literal formula (rather than the computed value). If I refer to an earlier field, it works fine.

The follow row:
[1,’=C1+1’,’=A1+1’,’=C1+1’]

Renders as:
1 #VALUE! 2 3
Expected output:
1 3 2 3

Thanks for any help you can offer.
Chuck

Thanks for sharing Chuck,

Formulas is still an alpha plugin so it’s buggy. I will mark this issue as a bug.

Thank you very much

any update on this issue?

We are working on a new formula engine that should be public next month. Here’s how Formulas work now, in the Handsontable 7.3.0 version https://handsontable.com/docs/7.3.0/demo-formula-support.html

@edmrepost33 can you share an example that doesn’t work in your project? Sometimes it’s OK to use API methods instead.

https://jsfiddle.net/edmrepost33_gmailcom/kj5wa1bL/15/ please check the code also can i use iferror or somthing like that

Thank you for the example. You get this error cause you are trying to divine by zero. It also produces an error in Excel https://support.office.com/en-us/article/how-to-correct-a-div-0-error-3a5a18a9-8d80-4ebb-a908-39e759a009a5

Each time the formula parser finds out that the operation may occur it throws an error.

hey i have checked the link but i am still not sure what to do… i have tried this please check and let me know if its right or wrong https://jsfiddle.net/qxmLoz70/

image

This image explains it better. In your code the (C1 - B1)/ B1 return 0 / 0 and (as mentioned above)

you are trying to divine by zero. It also produces an error in Excel https://support.office.com/en-us/article/how-to-correct-a-div-0-error-3a5a18a9-8d80-4ebb-a908-39e759a009a5

yes i do understand that but how to prevent it with error handler of some kind who will return 0 instead of #value!

You can use beforeChange / afterChange to check if the value is zero and then remove the formula with anything else you’d like to show.