if less than

[JosieJosie02]

I am trying to figure out how to mark a cell that either has a negative number, a positive number or no remaining

IF(OR(A1<0, A1>0), "negative", "positive") sees zero as positive.

How do I add a third condition to check, A1 can be 1, or -1, or 0?

This will be used to create a slice in a pivot, to show the values of Negative Amounts, Positive Amounts or No Remaining Amount.

 

[SunnyAlv]

Like this ?

=IF(A1<0,"Negative","Positive")

 

[JosieJosie02]

Like this ?

=IF(A1<0,"Negative","Positive")

No that marks 0 as positive as well. Which is basically the same as the formula I already have.

 

[JosieJosie02]

This seems to work.

=IF(A1<0, "Negative Amounts", IF(OR(A1>0), "Positive Amounts", "No Remaining Amounts"))

 

[MARK858]

What is the purpose of the OR?

Book1
	A	B	C
1	-5	Negative Amounts	Negative Amounts
2	0	No Remaining Amounts	No Remaining Amounts
3	5	Positive Amounts	Positive Amounts
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=IF(A1<0, "Negative Amounts", IF(OR(A1>0), "Positive Amounts", "No Remaining Amounts"))
C1:C3	C1	=IF(A1<0, "Negative Amounts", IF(A1>0, "Positive Amounts", "No Remaining Amounts"))

 

[JosieJosie02]

What is the purpose of the OR?

Book1
	A	B	C
1	-5	Negative Amounts	Negative Amounts
2	0	No Remaining Amounts	No Remaining Amounts
3	5	Positive Amounts	Positive Amounts
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=IF(A1<0, "Negative Amounts", IF(OR(A1>0), "Positive Amounts", "No Remaining Amounts"))
C1:C3	C1	=IF(A1<0, "Negative Amounts", IF(A1>0, "Positive Amounts", "No Remaining Amounts"))

How to structure AND, OR and NOT functions individually. When you combine each one of them with an IF statement, they read like this:

• AND – =IF(AND(Something is True, Something else is True), Value if True, Value if False)
• OR – =IF(OR(Something is True, Something else is True), Value if True, Value if False)
• NOT – =IF(NOT(Something is True), Value if True, Value if False)

 

[MARK858]

I meant in your formula, see my last post.
Column B has the OR, Column C does not.

I am fully aware of the AND/OR/NOT methods and syntax

 

[JosieJosie02]

I meant in your formula, see my last post.
Column B has the OR, Column C does not.

I am fully aware of the AND/OR/NOT methods and syntax

it it better to fully qualify the two if statements with OR

 

[Joe4]

it it better to fully qualify the two if statements with OR

But you only have ONE condition in that OR statement you written:

=IF(A1<0, "Negative Amounts", IF(OR(A1>0), "Positive Amounts", "No Remaining Amounts"))

The OR serves no purpose when there is only one condition being checked there.
You can replace the part in Red just with:
A1>0
A1<0

Don't confuse the NESTED IF statements with your OR clause. Though they are both used in Combination, they are two totally different things here!

 

[Fluff]

The OR function here OR(A1>0) is completely unnecessary as it does not do anything as there is only one value, in fact you could just as easily use AND (although still pointless)