Results 1 to 10 of 10
Thread: syntax for logical tests

20091203, 16:55 #1
 Join Date
 Jan 2004
 Location
 Brighton, Michigan
 Posts
 184
 Thanks
 10
 Thanked 0 Times in 0 Posts
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,
MartyRegards,
Marty
"Aerodynamics Is For Those Who Cannot Build Engines"  Enzo Ferrari

20091203, 17:08 #2
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
You can use the OR statement
=IF(OR(E1<2.5,E1>3.5)=TRUE,1,0)Jerry

20091203, 17:27 #3
 Join Date
 Jan 2004
 Location
 Brighton, Michigan
 Posts
 184
 Thanks
 10
 Thanked 0 Times in 0 Posts
Hi Jezza,
thank you for the quick response. I'll give it a try.
Regards,
MartyRegards,
Marty
"Aerodynamics Is For Those Who Cannot Build Engines"  Enzo Ferrari

20091203, 17:47 #4
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
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///HTHJerry

20091215, 10:24 #5
 Join Date
 Jan 2004
 Location
 Brighton, Michigan
 Posts
 184
 Thanks
 10
 Thanked 0 Times in 0 Posts
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,
MartyRegards,
Marty
"Aerodynamics Is For Those Who Cannot Build Engines"  Enzo Ferrari

20091215, 20:04 #6
 Join Date
 Aug 2004
 Posts
 123
 Thanks
 0
 Thanked 1 Time in 1 Post
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

20091215, 20:22 #7
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,240
 Thanks
 2
 Thanked 470 Times in 387 Posts
Another way:
=(ABS(A13)>0.5)^1
or:
=(ABS(A13)>=0.5)^1
Depending on whether the 2.5 and 3.5 are to be included in or excluded from the rangeCheers,
Paul Edstein
[MS MVP  Word]

20091216, 11:14 #8
 Join Date
 Jan 2004
 Location
 Brighton, Michigan
 Posts
 184
 Thanks
 10
 Thanked 0 Times in 0 Posts
Good Morning or Afternoon Paul,
A little help...ABS returns the absolute value, but what does the (A13)>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......
MartyRegards,
Marty
"Aerodynamics Is For Those Who Cannot Build Engines"  Enzo Ferrari

20091216, 11:22 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
ABS(A13) 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(A13)>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.

20091216, 14:47 #10
 Join Date
 Jan 2004
 Location
 Brighton, Michigan
 Posts
 184
 Thanks
 10
 Thanked 0 Times in 0 Posts
Hi Hans,
Thank you for the explanation....the more I learn about excel formulas etc, the more I like it.....thanks again.
Regards,
MartyRegards,
Marty
"Aerodynamics Is For Those Who Cannot Build Engines"  Enzo Ferrari