Solved Excel - Greater Than And Less Than

April 21, 2013 at 02:11:32
Specs: Windows 7
Hello I am trying to create a formula with the following Rules
1)If value is grater 0 result should be 1
2)If value is grater 2 result should be 0

See More: Excel - Greater Than And Less Than

Report •


✔ Best Answer
April 21, 2013 at 14:38:26
There's a few things I feel I should bring up...

1 - Newbie10: Did you try your formulas? I get errors for both of them.

2 - Newbie10: Even if you suggestions did work, both of them have more parenthesis than are necessary. A Nested IF does not require parenthesis around each IF clause nor are parenthesis required around the logical_test. For example, a solution to the OP original request can be written:

=IF(A1>2,0,IF(A1>0,1,""))

For the OP's second request, I would try:

=IF(A1>2,0,IF(AND(A1>0,A1<=1),1,IF(A1=0,2,"")))

3 - Sayed: It does not appear that you have given us all of your requirements. You haven't told us what you want the formula to return if the value is "greater than 1 but less than or equal to 2" or if it's a negative number. Are any of those values possible?

e.g. -2, 1.5, 1.75, 2

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
April 21, 2013 at 06:36:43
Try something like this: (you will need to change the A1 to whatever cell you want to analyze.)

=if((A1>2),0,(if(A1>0),1,""))


Report •

#2
April 21, 2013 at 07:12:30
Thanks For Your help
I have similar problem now
1)If value is grater then 0 to 1 result should be 1
2)If value is grater than 2 result should be 0
3)if value is 0 result should be 2

Report •

#3
April 21, 2013 at 07:26:43
This should do what you want.

=if((A1>2),0,(if(A1>0),1,(if(A1=0),2,"")))


Report •

Related Solutions

#4
April 21, 2013 at 14:38:26
✔ Best Answer
There's a few things I feel I should bring up...

1 - Newbie10: Did you try your formulas? I get errors for both of them.

2 - Newbie10: Even if you suggestions did work, both of them have more parenthesis than are necessary. A Nested IF does not require parenthesis around each IF clause nor are parenthesis required around the logical_test. For example, a solution to the OP original request can be written:

=IF(A1>2,0,IF(A1>0,1,""))

For the OP's second request, I would try:

=IF(A1>2,0,IF(AND(A1>0,A1<=1),1,IF(A1=0,2,"")))

3 - Sayed: It does not appear that you have given us all of your requirements. You haven't told us what you want the formula to return if the value is "greater than 1 but less than or equal to 2" or if it's a negative number. Are any of those values possible?

e.g. -2, 1.5, 1.75, 2

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
April 22, 2013 at 06:56:24
I did these off of the top of my head; It appears I forgot to complete a few pairs of parenthesis. The following worked perfect for me.

=IF((A1>2),0,(IF((A1>0),1,(IF((A1=0),2,"")))))

Thanks again DerbyDad03 for the useful information.


Report •

#6
April 22, 2013 at 07:22:19
As I said in my previous post, you are using more parentheses than necessary.

Extra parentheses make the formula harder to read, and as you have seen, harder to write.

These two formulas are equivalent:

=IF((A1>2),0,(IF((A1>0),1,(IF((A1=0),2,"")))))

=IF(A1>2,0,IF(A1>0,1,IF(A1=0,2,"")))

Excel does not require parentheses around each IF clause nor around the logical_test.

In other words, there is no need to use parentheses around A1>2, A1>0, etc. nor do you need parentheses around each IF.

Let's look at a simple IF function:

=IF(logical_test, value_if_true, value_if_false)

=IF(A1>1, 1, 0)

The value_if_false is 0. As I'm sure you know, there is no need for parentheses around the 0.

Now, instead of using 0, let's replace the value_if_false with another IF function:

=IF(A1>1, 1, IF(A1<1, 0, ""))

Just like the 0 as the value_if_false did not require parentheses around it, the
IF(A1<1, 0, "")) as the value_if_false does not require parentheses around it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question