Calculation issue when IF is used on FALSE data

Question

Calculation issue when IF is used on FALSE data

Closed this issue 3 years ago · 17 comments

LoveOfLava commented 6 years ago

Hello

I don't know why the formula on B3 cell display "#VALUE!" when the value on A3 is "nd"

Thanks
Regards

Answer 1 · 2018-10-11T12:34:06.000Z

Hi @LoveOfLava

B3 shows #REF!

The formula is a bit complicated

=IF(OR(A3<E3,A3="nd"),"nd",IF(A3*(A1-A2)/A1<3*E3,"<"&ROUND(3*E3,3),A3*(A1-A2)/A1))

You are refering to a cell E3 that does not exist. If I add another column to create E3 cell the B3 becomes #VALUE!.

Can you tell me what are the requirements for this cell?

Answer 2 · 2018-10-11T12:38:47.000Z

Hi,
The formula is located on the B3 cell

When I enter the value "nd" for A3 cell the formula display "#VALUE!"

the correct link is :
https://jsfiddle.net/Hun7er/h4bsqwtr/48/

Answer 3 · 2018-10-11T12:43:18.000Z

It looks correct in the demo

even if I put nd manually in cell A3 I do not get the error.

Answer 4 · 2018-10-11T12:45:19.000Z

Strange it doesn't work on my fiddle

Answer 5 · 2018-10-11T12:51:07.000Z

What device and browser do you use?

Answer 6 · 2018-10-11T12:53:52.000Z

Hello,

I think the link to demo is incorrect

Here is the right link

I use the latest chrome browser

https://jsfiddle.net/Hun7er/h4bsqwtr/48/

Answer 7 · 2018-10-11T13:13:48.000Z

OK, now I get the #VALUE!.
Let's check what is going on in the formula:
'=IF(OR(A3<E3,A3="nd"),"nd",IF(A3*(A1-A2)/A1<3*E3,"<"&ROUND(3*E3,3),A3*(A1-A2)/A1))'

the typical IF checks
if OR(A3<E3,A3="nd") is true
and if it gets true it will return nd
otherwise you will get IF(A3*(A1-A2)/A1<3*E3,"<"&ROUND(3*E3,3),A3*(A1-A2)/A1))'

The first part return true, so we should get nd but as the second part produces and error #VALUE! whole formula gets corrupted. Especially the A3*(A1-A2) produces the #VALUE! error as you try to multiple text by a number nd * (4-3)

Answer 8 · 2018-10-11T13:19:58.000Z

I think the behavior is not correct.
It must displaying "nd"

I tried to implement the formula on a Excel file and it displays "nd"

Answer 9 · 2018-10-11T13:27:37.000Z

Yes, you are right. The formula should abadon the fact that the rest of the equasion is invalid as the IF statement turns our true. The result should be nd.

Answer 10 · 2018-10-12T07:50:50.000Z

Hello,

You will solve the bug ?

Answer 11 · 2018-10-12T07:51:53.000Z

Yes, we should. However, I do not know if we won't just wait for the new engine to come.

Answer 12 · 2021-01-18T10:17:32.000Z

We have the same result in v 8.2.0 https://jsfiddle.net/0hkw1fvb/

Answer 13 · 2021-02-12T08:49:14.000Z

This issue is closed temporarily. It will be reopened for development as it became a part of New formula plugin task reported at #6466

I will make sure to inform everyone interested in this topic after the official fix.

Please feel welcome to makes any comments on this issue. And if you experience similar behavior feel free to contact me at support@handsontable.com

Answer 14 · 2021-05-21T09:39:36.000Z

This issue is not replicable using the pre v9.0.0 https://jsfiddle.net/handsoncode/dms82o5k/

Answer 15 · 2021-05-26T12:40:52.000Z

This issue is no longer replicable using the latest build of v9 https://jsfiddle.net/4gm1oqbs/
The issue will be closed after the official release of v9

Answer 16 · 2021-06-01T07:21:26.000Z

@aninde it works well in v9 https://jsfiddle.net/vx7zc2po/1/ (today's build, - freeze)

Answer 17 · 2021-06-01T12:06:08.000Z

Hi @LoveOfLava

I'm happy to announce that I'm closing this issue as fixed for v9.0.0. Thank you for your patience and constant feedback.

For everyone interested, here is a list of changes for the following version https://handsontable.com/docs/9.0.0/tutorial-release-notes.html and here https://handsontable.com/docs/9.0.0/tutorial-migration-guide.html is a migration guide (from v8 to v9)