ExcelBanter - IF statement with a range

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - IF statement with a range (https://www.excelbanter.com/excel-worksheet-functions/116251-if-statement-range.html)

ricky

IF statement with a range

Hi

Is there anyway I can write the following:

IF(D20 and <5000000, "5m","0m")

To populate another cell for reporting purposes.

Kind Regards

Ricky

[email protected]

IF statement with a range

ricky wrote:
Is there anyway I can write the following:
IF(D20 and <5000000, "5m","0m")

=if(and(D20, D2<5000000), "5m", "0m")

Ron Coderre

IF statement with a range

Try this:

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

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

XL2002, WinXP

"ricky" wrote:

Hi

Is there anyway I can write the following:

IF(D20 and <5000000, "5m","0m")

To populate another cell for reporting purposes.

Kind Regards

Ricky

ricky

IF statement with a range

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,D2<=500),"0-500",IF(AND(D2=501,D2<=1000),"501-1000",IF(AND(D2
=1001,D2<=5000),"1001-5000",IF(AND(D2=5001,D2<=10000),"5001-10000",IF(AND(
D2=10001,D2<=25000),"10001-25000",IF((D2=25001),"25001","0"))))))

Kind Regards

Ricky

"Ron Coderre" wrote in message
...
Try this:

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

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

XL2002, WinXP

"ricky" wrote:

Hi

Is there anyway I can write the following:

IF(D20 and <5000000, "5m","0m")

To populate another cell for reporting purposes.

Kind Regards

Ricky

Ron Coderre

IF statement with a range

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

"ricky" wrote:

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,D2<=500),"0-500",IF(AND(D2=501,D2<=1000),"501-1000",IF(AND(D2
=1001,D2<=5000),"1001-5000",IF(AND(D2=5001,D2<=10000),"5001-10000",IF(AND(
D2=10001,D2<=25000),"10001-25000",IF((D2=25001),"25001","0"))))))

Kind Regards

Ricky

"Ron Coderre" wrote in message
...
Try this:

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

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

XL2002, WinXP

"ricky" wrote:

Hi

Is there anyway I can write the following:

IF(D20 and <5000000, "5m","0m")

To populate another cell for reporting purposes.

Kind Regards

Ricky

David Biddulph

IF statement with a range

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?
--
David Biddulph

"ricky" wrote in message
...
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,D2<=500),"0-500",IF(AND(D2=501,D2<=1000),"501-1000",IF(AND(D2
=1001,D2<=5000),"1001-5000",IF(AND(D2=5001,D2<=10000),"5001-10000",IF(AND(
D2=10001,D2<=25000),"10001-25000",IF((D2=25001),"25001","0"))))))

Kind Regards

Ricky

"Ron Coderre" wrote in message
...
Try this:

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

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

XL2002, WinXP

"ricky" wrote:

Hi

Is there anyway I can write the following:

IF(D20 and <5000000, "5m","0m")

To populate another cell for reporting purposes.

Kind Regards

Ricky

ricky

IF statement with a range

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

"Ron Coderre" wrote in message
...
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

"ricky" wrote:

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,D2<=500),"0-500",IF(AND(D2=501,D2<=1000),"501-1000",IF(AND(D2

=1001,D2<=5000),"1001-5000",IF(AND(D2=5001,D2<=10000),"5001-10000",IF(AND(
D2=10001,D2<=25000),"10001-25000",IF((D2=25001),"25001","0"))))))

Kind Regards

Ricky

"Ron Coderre" wrote in message
...
Try this:

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

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

XL2002, WinXP

"ricky" wrote:

Hi

Is there anyway I can write the following:

IF(D20 and <5000000, "5m","0m")

To populate another cell for reporting purposes.

Kind Regards

Ricky

Ron Coderre

IF statement with a range

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

"ricky" wrote:

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

"Ron Coderre" wrote in message
...
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

"ricky" wrote:

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,D2<=500),"0-500",IF(AND(D2=501,D2<=1000),"501-1000",IF(AND(D2

=1001,D2<=5000),"1001-5000",IF(AND(D2=5001,D2<=10000),"5001-10000",IF(AND(
D2=10001,D2<=25000),"10001-25000",IF((D2=25001),"25001","0"))))))

Kind Regards

Ricky

"Ron Coderre" wrote in message
...
Try this:

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

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

XL2002, WinXP

"ricky" wrote:

Hi

Is there anyway I can write the following:

IF(D20 and <5000000, "5m","0m")

To populate another cell for reporting purposes.

Kind Regards

Ricky

ricky

IF statement with a range

Thank you Ron, that works very well.

Kind Regards

Ricky

"Ron Coderre" wrote in message
...
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

"ricky" wrote:

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

"Ron Coderre" wrote in message
...
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

"ricky" wrote:

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,D2<=500),"0-500",IF(AND(D2=501,D2<=1000),"501-1000",IF(AND(D2

=1001,D2<=5000),"1001-5000",IF(AND(D2=5001,D2<=10000),"5001-10000",IF(AND(
D2=10001,D2<=25000),"10001-25000",IF((D2=25001),"25001","0"))))))

Kind Regards

Ricky

"Ron Coderre" wrote in message
...
Try this:

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

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

XL2002, WinXP

"ricky" wrote:

Hi

Is there anyway I can write the following:

IF(D20 and <5000000, "5m","0m")

To populate another cell for reporting purposes.

Kind Regards

Ricky

All times are GMT +1. The time now is 05:41 PM.