Click here for important information about Computing.net.

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!

✔ 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.1as opposed to less than or equal to1, 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.

Strictly speaking, you can't satisfy all of your requirements. You can't have the answer be 1 when A1 >= 2

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

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

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!

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.1as opposed to less than or equal to1, 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.

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Yes (14) | ||

No (14) | ||

I don't know (15) |

Discuss in The Lounge

Poll History