IF statement with a range

R

ricky

Hi

Is there anyway I can write the following:

IF(D2>0 and <5000000, "5m","0m")

To populate another cell for reporting purposes.

Kind Regards

Ricky
 
R

Ron Coderre

Try this:

=IF(AND(D2>0,D2<5000000), "5m","0m")

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
R

ricky

Thanks guys for the solutions. Worked just fine.

I took it a step further by writing nested IF statements on the FALSE
portion, to cater for differing values of D2.

This is what I came up with :

=IF(AND(D2>=0 said:
=1001,D2<=5000),"1001-5000",IF(AND(D2>=5001,D2<=10000),"5001-10000",IF(AND(
Click to expand...
D2>=10001,D2<=25000),"10001-25000",IF((D2>=25001),"25001","0"))))))

Kind Regards

Ricky
 
R

Ron Coderre

Here's an option that won't be limited to the 7-nesting level limitation.

Put this table in A1:B6
0 0-500
501 501-1000
1001 1001-5000
5001 5001-10000
10001 10001-25000
25001 >25000

With a value in D2

This formula returns the category for the value in D2
E2: =VLOOKUP(D2,A1:B6,2,1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
D

David Biddulph

If your first test included D2<= 500, do you really want to test for D2>=501
in the next condition?
At present you are returning the answer "0" for cases where D2 is <0, or if
it is >500 and <501, or >1000 and <1001, or >5000 and <5001, or >10000 and
<10001, or >25000 and <25001. Did you really intend that? Can't you get
rid of the ANDs?
 
R

ricky

Hi

Thank you for the suggestion, incidentally, how could I use this formula for
a long list of values, which are from D2 - D100?

Since when I paste the formula, it seems to increment the values of the
VLOOKUP?

Kind Regards

Ricky
 
R

Ron Coderre

Dollar signs ($) lock in parts of the formula references.

This formula:
E2: =VLOOKUP(D2,$A$1:$B$6,2,1)
will always reference $A$1:$B$6 when copied.

See Excel Help and search for "range reference"
and read this section: "About cell and range references"

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Top