Solved Need help with excel if/then

June 25, 2012 at 16:54:40
Specs: Windows 7
Need an equation that will answer this: if A1<=1 then 0 if A1>=2 then 1 if A1>7 then 0
Thanks in advance!

See More: Need help with excel if/then

Report •

✔ Best Answer
June 26, 2012 at 08:35:23
Regardless of the value displayed in a cell, buried inside Excel is the actual value that Excel will use for calculations.

Therefore, an IF statement evaluating >1.1 and <=1 is still going to have an issue with values between 1 and 1.1 if anything in that range is the result of the calculation in A1.

To prove this to yourself, enter 1.09 in A1 and format for 1 decimal place to display 1.1.

Now place this formula in B1:

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

You'll note that the answer is FALSE because you have not fullfilled any of the specified conditions.

Granted, if A1 will never (internally) be equal to a value between 1 and 1.1, then the formula will work. That is something only you can answer.

To get exactly what you asked for, this formula should work. However, it too will fail for values between 1 and 1.1.

=IF(AND(A1>1.1,A1<=7),1,IF(OR(A1<=1,A1>7),0))

If you really need an answer of 0 for values less than or equal to 1.1 as opposed to less than or equal to 1, then this should work:

=IF(AND(A1>1.1,A1<=7),1,0)

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



#1
June 25, 2012 at 18:40:37
Strictly speaking, you can't satisfy all of your requirements.

You can't have the answer be 1 when A1 >= 2 and have the answer be 0 when A1 > 7 since A1 would still be greater than 2.

What I assume you meant was the answer should be 1 when A1 >= 2 but <= 7.

However, you also seem to be missing a condition, unless of course this condition could never be true: What happens if A1 = 1.5?

Your request deals with A1 <=1 and >=2, but doesn't mention what the answer should be if A1 is between 1 and 2.

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


Report •

#2
June 25, 2012 at 21:18:08
please post it in logical terms of what you want, like dd implied. "if value is less/equal 1 do this. If value is between 2 and 7 do that. If value is gtr 7 do the other." including any "between" values if the cell is not set as integer. You get the gist...

Report •

#3
June 26, 2012 at 04:23:50
Thanks for the replies. Hope re-framing like this make more sense:

If A1 greater than 1.1 and less then or equal to 7 then 1. Anything equal to or less then 1 or greater than 7 = 0

thanks so much!


Report •

Related Solutions

#4
June 26, 2012 at 08:35:23
✔ Best Answer
Regardless of the value displayed in a cell, buried inside Excel is the actual value that Excel will use for calculations.

Therefore, an IF statement evaluating >1.1 and <=1 is still going to have an issue with values between 1 and 1.1 if anything in that range is the result of the calculation in A1.

To prove this to yourself, enter 1.09 in A1 and format for 1 decimal place to display 1.1.

Now place this formula in B1:

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

You'll note that the answer is FALSE because you have not fullfilled any of the specified conditions.

Granted, if A1 will never (internally) be equal to a value between 1 and 1.1, then the formula will work. That is something only you can answer.

To get exactly what you asked for, this formula should work. However, it too will fail for values between 1 and 1.1.

=IF(AND(A1>1.1,A1<=7),1,IF(OR(A1<=1,A1>7),0))

If you really need an answer of 0 for values less than or equal to 1.1 as opposed to less than or equal to 1, then this should work:

=IF(AND(A1>1.1,A1<=7),1,0)

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


Report •

Ask Question