# Thread: syntax for logical tests

1. Hi To All,

I have a rather basic question regarding Excel 03'; specifically, I am trying to test or apply a logical test to a column of numbers, such that if any cell value in the column is outside the numerical value range of "2.5 to 3.5" a 1 is returned, if any cell value is within this range then a 0 is returned. I have tried many options with no success. ex. =IF(E2<2.5,>3.5,1,0)...etc etc
any suggestions?

Many thanks,
Marty

2. You can use the OR statement

=IF(OR(E1<2.5,E1>3.5)=TRUE,1,0)

3. Hi Jezza,

thank you for the quick response. I'll give it a try.

Regards,
Marty

4. Hi Marty

I have known you all this time but have never used your name, sorry.

Just to give you the reasoning for this:

The Or function returns TRUE if any of the conditions are TRUE. Otherwise, it returns FALSE. So I have put the premis in to check if it is Less than 2.5 OR Greater than 3.5. If any number is less than 2.5 it will become TRUE same as if Above 3.5.

You can dispense with the IF but if you do you will see TRUE or FALSE in the cell///HTH

5. Good Morning Jerry,

Im a little slow on the response, but thank you for the background. I've been a long time and it is a indespensible site....the MS Excel help function and MS blogs fall far short of a good explanation when it comes to logic and VBA.

Thanks again

Regards,
Marty

6. Originally Posted by mojave1
.......I am trying to test or apply a logical test to a column of numbers, such that if any cell value in the column is outside the numerical value range of "2.5 to 3.5" a 1 is returned, if any cell value is within this range then a 0 is returned..........
Marty
1] In excel, OR() is equal to " + " ( plus sign of arithmetic operator )

2] Check if a number is greater than or less than another number

=(E1<2.5) will return TRUE or FALSE

3] Thus, a logical testing for numeric value outside the range of "2.5 to 3.5"

The formula : ( without IF() and OR() )

=(E1<2.5)+(E1>3.5)

Regards
Bosco

7. Another way:
=(ABS(A1-3)>0.5)^1
or:
=(ABS(A1-3)>=0.5)^1
Depending on whether the 2.5 and 3.5 are to be included in or excluded from the range

8. Good Morning or Afternoon Paul,

A little help...ABS returns the absolute value, but what does the (A1-3)>0.5)^1 do? I get the >= part, I am looking for values that are 2.5< and >3.5, in which case a 1 is returned, if not a 0.

Many Thanks......

Marty

9. ABS(A1-3) tells how how far the value of A1 is from 3. If A1 = 2.8 or A1 = 3.2, this is 0.2, and if A1 = 2.3 or A1 = 3.7, it is 0.7.
ABS(A1-3)>0.5 is TRUE if the value of A1 is more than 0.5 from 3, i.e. if is is less than 2.5 or greater than 3.5, and FALSE otherwise.
Using ^1 (raising to the power 1) convert the TRUE / FALSE values to their numeric equivalents 1 and 0. Paul could also have used *1 or +0.

10. Hi Hans,

Thank you for the explanation....the more I learn about excel formulas etc, the more I like it.....thanks again.

Regards,
Marty

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•