Thanks:  0
Likes:  0

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

1. 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. You need a nested IF for that...

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

Rgds
AJ

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

4. 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. why not use the AND function (also works on OR)

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

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

## User Tag List

#### Posting Permissions

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