Ask Your Question

If statement with arithmatic [closed]

asked 2015-12-12 23:35:52 +0200

johnlai2004 gravatar image

I'm using Libre Calc but for some reason the following always produces the string TRUE


What I want is if Cell K3 is empty, then print the value of Cell D3, otherwise print the value of D3 multiplied with K3.

What am I doing wrong?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-28 00:41:30.074103

2 Answers

Sort by » oldest newest most voted

answered 2015-12-14 21:54:50 +0200

Lupp gravatar image

What is called "the string TRUE" by the OP is not a string (text) but the way Calc displays a numerical value in a cell set to the number format 'BOOLEAN' (listed as 'Boolean value'). It may be heavily misleading but is a fact that Calc displays TRUE for any value not equal to 0 an FALSE for the value 0 only in this case. [Let K3 be 0 to experience this.]

The second misleading behaviour of Calc in this context is that it automatically applies the number format BOOLEAN to a cell if it has the number format 'General' in advance and is recalculated to the result of a BOOLEAN function.

The third craziness I see in the fact that a cell once formatted this way will display every numeric result as 'BOOLEAN' even if the formula was edited to something without any relation to logic, "=PI()/8", eg.

And the fourth incredibility (or was it the zeroth) is that an IF function is classified logical if the third or both the second and the third parameters are missing. It will produce the logical result of the condition as its own result for the missing parameter. Very bad.

@johnlai2004 , for some reason, most likely because of a typo, had first entered an incomplete IF expression. Then he rectified that. This did not heal the consequences of silly smart automatisms.

You see?

@Edward is right in principle. We get rid of the insistent wrong format by choosing another numeric format but 'BOOLEAN'. 'BOOLEAN', however, is also a number format. I cannot but accept that.

By the way: Entering a numeric constant into the cell will also switch off the BOOLENA hysteria.

edit flag offensive delete link more

answered 2015-12-14 21:15:44 +0200

Edward gravatar image

Formula looks correct. If cell is formatted as Boolean Value formula will return "TRUE", formatting cell as number should fix the issue.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2015-12-12 23:35:52 +0200

Seen: 373 times

Last updated: Dec 14 '15