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

✔ 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.

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,""))

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

This should do what you want. =if((A1>2),0,(if(A1>0),1,(if(A1=0),2,"")))

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.

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.

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_falseis 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_falsewith another IF function:=IF(A1>1, 1, IF(A1<1, 0, ""))

Just like the 0 as the

value_if_falsedid not require parentheses around it, the

IF(A1<1, 0, "")) as thevalue_if_falsedoes not require parentheses around it.

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

Ask Your Question

Weekly Poll