Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    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,
    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  2. #2
    Platinum Lounger
    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

  3. #3
    2 Star Lounger
    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,
    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  4. #4
    Platinum Lounger
    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///HTH
    Jerry

  5. #5
    2 Star Lounger
    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,
    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by mojave1 View Post
    .......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. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    2 Star Lounger
    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 (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
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #10
    2 Star Lounger
    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,
    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

Posting Permissions

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