Need help with IF statement

[Barry Katcher]

In cell A1, I have a 4 digit number. If that number is between 5000 and 5999, I want YES to appear in cell B1. If the number is less than or more than that range, I want NO to appear in B1. In Lotus, I would have used this formula in B1:

@IF(A1>4999#AND#A1<6000,"YES","NO")

with #AND# being the operative to give me the range. I can't find the equivalent operative in Excel. Excel's AND function doesn't provide for a range and it only returns true or false. Can anyone help me, I'm sure it's possible and easy, just can't seem to locate it.

 

[DonkeyOte]

b1 =if(and(a1>=5000,a1<=5999),"yes","no")

 

[Aladin Akyurek]

On 2002-10-03 08:33, Barry Katcher wrote:
In cell A1, I have a 4 digit number. If that number is between 5000 and 5999, I want YES to appear in cell B1. If the number is less than or more than that range, I want NO to appear in B1. In Lotus, I would have used this formula in B1:

@IF(A1>4999#AND#A1<6000,"YES","NO")

with #AND# being the operative to give me the range. I can't find the equivalent operative in Excel. Excel's AND function doesn't provide for a range and it only returns true or false. Can anyone help me, I'm sure it's possible and easy, just can't seem to locate it.

=(A1>=5000)*(A1<=5999)

Custom format the formula cell as:

[=0]"NO";[=1]"YES"

 

[Barry Katcher]

Thanks, LAS, worked great. Curious, though. I entered 5500 in A1 and changed the formula in B1 to:

=IF(AND(A1=5000,A1=5500),"YES","NO")

and it return NO. Any idea why?

 

[bolo]

it is not possible for a1 to be both 5000 and 5500 so it will always say NO.

 

[Bisleycat2002]

It returned NO because natutrally enough it couldn't satisfy both criteria i.e. it couldn't equal both 5000 and 5500

 

[Barry Katcher]

Aladin - doesn't work. 5500 returns a zero, not a one. What is the * symbol. Is it a multiplier or the symbol for "and"?

 

[Barry Katcher]

O.K, thanks, Bisley & Bolo. If I understand correctly, it satisfied the less than/more than criteria but not both the equal to criteria.

 

[IML]

On 2002-10-03 09:07, Barry Katcher wrote:
O.K, thanks, Bisley & Bolo. If I understand correctly, it satisfied the less than/more than criteria but not both the equal to criteria.

If you did want either case, simply substitute
OR for ANDin the "traditional" formula
or
+ for * in Aladin's boolean formula.