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

Discuss the spreadsheet application

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

Postby Mr.Nobody » Sat Jan 04, 2020 6:22 pm

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!
Last edited by Hagar Delest on Sun Jan 05, 2020 10:53 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 3.1 on Windows 7
Mr.Nobody
 
Posts: 3
Joined: Sat Jan 04, 2020 5:52 pm

Re: Negative Value of a number reads as lower than zero

Postby RusselB » Sat Jan 04, 2020 7:08 pm

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   Expand viewCollapse view
=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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Negative Value of a number reads as lower than zero

Postby Mr.Nobody » Sun Jan 05, 2020 4:39 am

Hi sir russel

I think I really should read first before asking :lol: :crazy: 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
OpenOffice 3.1 on Windows 7
Mr.Nobody
 
Posts: 3
Joined: Sat Jan 04, 2020 5:52 pm

Re: Negative Value of a number reads as lower than zero

Postby RusselB » Sun Jan 05, 2020 4:50 am

I'd also recommend you consult the various Tutorials, especially Ten concepts that every Calc user should know
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby Mr.Nobody » Mon Jan 06, 2020 6:03 pm

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.
OpenOffice 3.1 on Windows 7
Mr.Nobody
 
Posts: 3
Joined: Sat Jan 04, 2020 5:52 pm

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

Postby RusselB » Mon Jan 06, 2020 6:30 pm

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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests