Page 1 of 1
[Solved] Operator for value lower than negative value?
Posted: Sat Jun 22, 2019 8:04 pm
by withnail
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?!
Re: Which operator for value lower than another negative val
Posted: Sat Jun 22, 2019 8:07 pm
by FJCC
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.
Re: Which operator for value lower than another negative val
Posted: Sat Jun 22, 2019 8:08 pm
by withnail
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))
Re: Which operator for value lower than another negative val
Posted: Sat Jun 22, 2019 8:09 pm
by RoryOF
-600 is greater than -601; you need to rethink - your list of negative number gradually decreases.
Re: Which operator for value lower than another negative val
Posted: Sat Jun 22, 2019 8:15 pm
by Lupp
@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.
Re: Which operator for value lower than another negative val
Posted: Sat Jun 22, 2019 8:19 pm
by withnail
Sorry, I'm using LibreOffice, thought it was the same...
Re: Which operator for value lower than another negative val
Posted: Sat Jun 22, 2019 8:29 pm
by FJCC
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
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)))
Re: Which operator for value lower than another negative val
Posted: Sat Jun 22, 2019 8:35 pm
by withnail
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
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!
Re: Which operator for value lower than another negative val
Posted: Sun Jun 23, 2019 2:33 am
by robleyd
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.
Re: Which operator for value lower than another negative val
Posted: Sun Jun 23, 2019 11:38 am
by withnail
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