What is Excel equivalent of Lotus #AND# function?

[Barry Katcher]

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.

 

[AJ]

You need a nested IF for that...

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

Rgds
AJ

 

[Barry Katcher]

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

 

[Aladin Akyurek]

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")

 

[Spock's Brain]

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

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

 

[Barry Katcher]

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