[Solved] cell comparison inside IF() test

[gramb0t]

Hello Everyone!
Long time lurker first time caller.
Hows everybody feeling today? Alllllllrighty then.

I have a questions, goes a little like this:

If a_cell minus 180 is less than 0, show a 0, if not, show what the difference is.

currently my formula looks like this:
=IF((J34-180) < 0; "0"; (J34-180));

For fun, lets assume the following:
J34 = 190

In a perfect world, the result would be 10,
=IF((190-180) < 0; "0"; (190-180));
=IF((10) < 0; "0"; (10))
=10

alas, she left,
the dogs gone,
and all i have is an Err:509 in that cell.

Anyone have an idea on whats causing that?

[Lupp]

-1- I would see it as a bad idea to return the result in case of a negative difference as the TEXT "0". It should be the number 0.
-2- The simplest formula for the purpose is

Code: Select all

=MAX(0; Minuend - Subtrahend)

or, for one of your examples

Code: Select all

=MAX(0; J34-180)

-3- Error code 509 is explained as "Missing operator". This may be slightly misleading here. In fact the error is caused by the trailing semicolon.
-4- Many of the pairs of parentheses don't do anything.

[Zizi64]

509
Missing operator
Operator is missing, for example, "=2(3+4) * ", where the operator between "2" and "(" is missing.

Please upload your .ods type sample file here.

[gramb0t]

@Lupp - Thanks for that, that formula worked great.
I've spent a little too much time complicating things in life, it seems like that was one of them too.