What is Excel equivalent of Lotus #AND# function?
What is Excel equivalent of Lotus #AND# function?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: What is Excel equivalent of Lotus #AND# function?

  1. #1
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I would like cell B1 to return the value in cell A1 if that value is either 1 or greater (the number of coffins sold to customers) or if it is minus 1 or less (the number of coffins returned by dissatisfied customers). If the value is between those two, i.e. .5, 0 or NO ENTRY AT ALL, I would like B1 to return "Incorrect Input, Fool!"

    The Lotus formula was:
    @IF(A1>1#AND#A1<-1,"Incorrect Input, Fool",A1)

    The AND function in Excel seems to only return TRUE or FALSE.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You need a nested IF for that...

    =IF(A1>=1,A1,IF(A1<=-1,A1,"Incorrect input fool!"))

    Rgds
    AJ

  3. #3
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, duh! Now I really feel stupid. Thanks for the fast response, AJ.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,827
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-04 13:28, Barry Katcher wrote:
    I would like cell B1 to return the value in cell A1 if that value is either 1 or greater (the number of coffins sold to customers) or if it is minus 1 or less (the number of coffins returned by dissatisfied customers). If the value is between those two, i.e. .5, 0 or NO ENTRY AT ALL, I would like B1 to return "Incorrect Input, Fool!"

    The Lotus formula was:
    @IF(A1>1#AND#A1<-1,"Incorrect Input, Fool",A1)

    The AND function in Excel seems to only return TRUE or FALSE.
    =IF(OR(A1>1,A1<-1),A1,"Incorrect Input, Fool")

    or, better:

    =IF(ABS(A1)>=1,A1,"Incorrect Input, Fool")

    or, even better:

    =IF((ABS(A1)>=1)*(A1-INT(A1)=0),A1,"Incorrect Input, Fool")


  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    why not use the AND function (also works on OR)

    =if(or(a1>1,a1<-1),"vaild input","wrong input")

  6. #6
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks, people. They all work. Now, how do I choose?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com