[GL #93] Formulas #VALUE error

Tags: #<Tag:0x00007efc6b6277d0> #<Tag:0x00007efc6b627488>
#1

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

#2

Thanks for sharing Chuck,

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

#3

Thank you very much

#4

any update on this issue?

#5

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.

#6

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

#7

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.

#8

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/

#9

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

#10

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

#11

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.