| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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") |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF statement (non nested) that compares cell value against range | Excel Worksheet Functions | |||
| Duplicate Range Names by worksheet | Excel Discussion (Misc queries) | |||
| Using a worksheet name from a range | Excel Worksheet Functions | |||
| How do I use a range of numbers in an if statement? | Excel Worksheet Functions | |||
| If statement where the logical test is a range that equals a word | Excel Worksheet Functions | |||
Linear Mode
