[Solved] Negative Value of a number reads as lower than zero

[Mr.Nobody]

Hi guys,

I am new here at using Open Office. But then i have a problem on my sheet. As you see in this formula, =IF(C3=0;"ACCURATE";IF(AND((C3)OR(F3))<=-1;"NEGATIVE VARIANCE";IF(AND((C3)OR(F3))>=1;"POSITIVE VARIANCE")))

Both C3 and F3 have result from my vlookup, so the result ive been talking is negative and positive numbers.
"ACCURATE" "POSITIVE VARIANCE" "NEGATIVE VARIANCE" are my result if the number I expected based on my formula will set.
I want to make comparison on c3 and f3 cell for which/whomever on c3 or f3 is <= -1, the selected cell will appear as negative variance.

Based on my formula, it worked but my problem is when i encounter likewise on my expected result, the comment appeared is positive variance.

I dont really know if I can make the negative number based on my formula reads the value of c3 or f3 as lower than zero.

Is that even posible or should I revise my formula?

What should I do? Please help me guys i am new and start to explore thank you!

[RusselB]

Welcome to the Forums.
Your understanding of the usage of the AND and OR functions is incorrect.
I'm going to suggest using the following formula, which is a re-write of yours, and based on my knowledge of these functions.

Code: Select all

=if(C3=0;"ACCURATE";IF(OR(c3<=-1;F3<=-1);"NEGATIVE VARIANCE";"POSITIVE VARIANCE"))

The problem with your formula is the incorrect usage of the AND and OR functions. The AND function compares the various parameters that have to all match in order for a TRUE response to be returned.
The OR function also compares the various parameters, but only requires 1 to be true for a TRUE response.

Please read through the Calc help system, as these functions are decently documented.

[Mr.Nobody]

Hi sir russel

I think I really should read first before asking I will read the calc help system to learn and improve using calc and other open office features. Thank you so much and god bless you!

Regards,
Mr.Nobody

[RusselB]

I'd also recommend you consult the various Tutorials, especially Ten concepts that every Calc user should know

[Mr.Nobody]

Thank you so much sir russel, actually I already tried the formula that you gave to me, it helps me a lot but there are still errors based on my expectations. But dont worry i think i can fix it. Thank a lot sir russel for your recommendation.

[RusselB]

If you can give examples of data that is working correctly and data that isn't giving the expected results, along with what results you were expecting, we might be able to determine what (if anything) in the formula needs to be changed or explain why you aren't seeing the results you're expecting.