[Solved] Operator for value lower than negative value?

withnail · Post by **withnail** » Sat Jun 22, 2019 8:04 pm

Hi,

I have a column with negative numbers that gradually increase, like so....

-1
-14
-56
-190
-334
-576
-798

When a cell reaches a negative value of more than -600 (so from -601 onwards) I want the formula to then perform a different function.
I can't seem to find an operator which allows me to do this.
All I can get to work is under zero ("<0"), if I try <-600 or <=-600 it won't work....what am I doing wrong?!

Post by **FJCC** » Sat Jun 22, 2019 8:07 pm

Using < -600 should work. Without seeing your actual formula, it is hard to say what is going on. Please show your formula and explain what result you are getting and what result you expect.

withnail · Post by **withnail** » Sat Jun 22, 2019 8:08 pm

Oh and these are my formula-

=IF(I4<0,1.053*( (-I4+$E$3) / (A5-1) ),IF(I4<=-600,$E$3))

=IF(I5<0,1.0526*(-I4+$E$3),IF(I4<=-600,1.053*$E$3))

Post by **RoryOF** » Sat Jun 22, 2019 8:09 pm

-600 is greater than -601; you need to rethink - your list of negative number gradually decreases.

Lupp · Post by **Lupp** » Sat Jun 22, 2019 8:15 pm

@whitenail:
Did you notice that you formulas contain the comma as the list separator between parameters?
Your signature claims you are using AOO. This would never work.

withnail · Post by **withnail** » Sat Jun 22, 2019 8:19 pm

Sorry, I'm using LibreOffice, thought it was the same...

Post by **FJCC** » Sat Jun 22, 2019 8:29 pm

Code: Select all

=IF(I4<0,1.053*( (-I4+$E$3) / (A5-1) ),IF(I4<=-600,$E$3))

Let's say that I4 = -605. The formula will calculate

Code: Select all

1.053*( (-I4+$E$3) / (A5-1) )

because I4 is less than zero. It will never evaluate the I4 <= -600 condition because I4 <0 is TRUE. If you reorder the formula so that I4 <= -600 comes first, then you may get what you want.

Code: Select all

=IF(I4 <= -600, $E$3, 1.053*( (-I4+$E$3) / (A5-1)))

withnail · Post by **withnail** » Sat Jun 22, 2019 8:35 pm

FJCC wrote:
Code: Select all
=IF(I4<0,1.053*( (-I4+$E$3) / (A5-1) ),IF(I4<=-600,$E$3))
Let's say that I4 = -605. The formula will calculate
Code: Select all
1.053*( (-I4+$E$3) / (A5-1) )
because I4 is less than zero. It will never evaluate the I4 <= -600 condition because I4 <0 is TRUE. If you reorder the formula so that I4 <= -600 comes first, then you may get what you want.
Code: Select all
=IF(I4 <= -600, $E$3, 1.053*( (-I4+$E$3) / (A5-1)))

Thank you, it works perfectly now!

Post by **robleyd** » Sun Jun 23, 2019 2:33 am

withnail wrote:Sorry, I'm using LibreOffice, thought it was the same...

Not exactly; there are differences which matter when providing advice, such as the situation Lupp mentions about separators. LO Calc also has some functions not (yet) available in AOO.

Please consider updating your signature to reflect what you are actually using so we can better advise you.

withnail · Post by **withnail** » Sun Jun 23, 2019 11:38 am

robleyd wrote:
withnail wrote:Sorry, I'm using LibreOffice, thought it was the same...
Not exactly; there are differences which matter when providing advice, such as the situation Lupp mentions about separators. LO Calc also has some functions not (yet) available in AOO.

Please consider updating your signature to reflect what you are actually using so we can better advise you.

Done

[Solved] Operator for value lower than negative value?

[Solved] Operator for value lower than negative value?

Re: Which operator for value lower than another negative val

Re: Which operator for value lower than another negative val

Re: Which operator for value lower than another negative val

Re: Which operator for value lower than another negative val

Re: Which operator for value lower than another negative val

Re: Which operator for value lower than another negative val

Re: Which operator for value lower than another negative val

Re: Which operator for value lower than another negative val

Re: Which operator for value lower than another negative val