Error Message because too many if statement

[lahmes]

Hi, I need to use the following formula below but I am getting an error message stating I have too many levels of nesting that are allowed. I know I am only allowed to have 7 levels, is there another way to revise this formula? =IF(AND(Y77>=400000,Y77<450000),"$400-$449K",IF(AND(Y77>=450000,Y77<500000),"$450-$499K",IF(AND(Y77>=500000,Y77<550000),"$500-$549K",IF(AND(Y77>=550000,Y77<600000),"$550-$599K",IF(AND(Y77>=600000,Y77<650000),"$600-$649K",IF(AND(Y77>=650000,Y77<700000),"$650-$699K",IF(AND(Y77>=700000,Y77<750000),"$700-$749K",IF(AND(Y77>=750000,Y77<800000),"$750-$799K",IF(AND(Y77>=800000,Y77<850000),"$800-$849K",IF(AND(Y77>=850000,Y77<900000),"$850-$899K",IF(AND(Y77>=900000,Y77<950000),"$900-$949K",IF(AND(Y77>=950000,Y77<1000000),"$950-$999K",IF(AND(Y77>=1000000,Y77<1090000),"$1-$1.09M",IF(AND(Y77>=1100000,Y77<1190000),"$1.1-$1.19M",IF(AND(Y77>=1200000,Y77<1290000),"$1.2-$1.29M",IF(AND(Y77>=1300000,Y77<1390000),"$1.3-$1.39M",IF(AND(Y77>=1400000,Y77<1490000),"$1.4-$1.49M",IF(AND(Y77>=1500000,Y77<1590000),"$1.5-$1.59M",IF(AND(Y77>=1600000,Y77<1690000),"$1.6-$1.69M",IF(AND(Y77>=1700000,Y77<1790000),"$1.7-$1.79M",IF(AND(Y77>=1800000,Y77<1890000),"$1.8-$1.89M",IF(AND(Y77>=1900000,Y77<1990000),"$1.9-$1.99M",IF(Y77>=2000000,"$2M+",0

 

[paulsolar]

Hi Lahmes

I'm not brilliant at this and only joined this forum recently myself. I am learning to use macros at the moment which I'm struggling with at the moment. In excel 2010 as far as I'm aware you can have a lot more than 7 nested if statement if required.

another thought is to do it via a look up table, would that work for you?

cheers

Paul

 

[markmzz]

Try this:

Layout:

[TABLE="width: 262"]
<tbody>[TR]
[TD="class: xl65, width: 68, bgcolor: transparent"]Data
[/TD]
[TD="class: xl66, width: 72, bgcolor: yellow"]LOOKUP
[/TD]
[TD="class: xl72, width: 72, bgcolor: #8DB4E2"]VLOOKUP
[/TD]
[TD="class: xl70, width: 65, bgcolor: #92D050"]Table
[/TD]
[TD="class: xl70, width: 72, bgcolor: #92D050"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 911.107
[/TD]
[TD="class: xl66, bgcolor: yellow"]$900-$949K
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$900-$949K
[/TD]
[TD="class: xl70, bgcolor: #92D050, align: right"]0
[/TD]
[TD="class: xl70, bgcolor: #92D050, align: right"]0
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 1.926.315
[/TD]
[TD="class: xl66, bgcolor: yellow"]$1,9-$1,99M
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$1,9-$1,99M
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 400.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$400-$449K
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 1.076.100
[/TD]
[TD="class: xl66, bgcolor: yellow"]$1-$1,09M
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$1-$1,09M
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 450.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$450-$499K
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 1.730.019
[/TD]
[TD="class: xl66, bgcolor: yellow"]$1,7-$1,79M
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$1,7-$1,79M
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 500.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$500-$549K
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 1.596.996
[/TD]
[TD="class: xl66, bgcolor: yellow"]$1,5-$1,59M
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$1,5-$1,59M
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 550.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$550-$599K
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 937.492
[/TD]
[TD="class: xl66, bgcolor: yellow"]$900-$949K
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$900-$949K
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 600.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$600-$649K
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 1.615.957
[/TD]
[TD="class: xl66, bgcolor: yellow"]$1,6-$1,69M
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$1,6-$1,69M
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 650.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$650-$699K
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 508.046
[/TD]
[TD="class: xl66, bgcolor: yellow"]$500-$549K
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$500-$549K
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 700.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$700-$749K
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 1.569.608
[/TD]
[TD="class: xl66, bgcolor: yellow"]$1,5-$1,59M
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$1,5-$1,59M
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 750.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$750-$799K
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 827.684
[/TD]
[TD="class: xl66, bgcolor: yellow"]$800-$849K
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$800-$849K
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 800.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$800-$849K
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 1.060.673
[/TD]
[TD="class: xl66, bgcolor: yellow"]$1-$1,09M
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$1-$1,09M
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 850.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$850-$899K
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 1.188.115
[/TD]
[TD="class: xl66, bgcolor: yellow"]$1,1-$1,19M
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$1,1-$1,19M
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 900.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$900-$949K
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 1.633.539
[/TD]
[TD="class: xl66, bgcolor: yellow"]$1,6-$1,69M
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$1,6-$1,69M
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 950.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$950-$999K
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 1.294.419
[/TD]
[TD="class: xl66, bgcolor: yellow"]$1,2-$1,29M
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$1,2-$1,29M
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 1.000.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$1-$1,09M
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 1.099.939
[/TD]
[TD="class: xl66, bgcolor: yellow"]$1-$1,09M
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$1-$1,09M
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 1.100.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$1,1-$1,19M
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 567.226
[/TD]
[TD="class: xl66, bgcolor: yellow"]$550-$599K
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$550-$599K
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 1.200.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$1,2-$1,29M
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 499.941
[/TD]
[TD="class: xl66, bgcolor: yellow"]$450-$499K
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$450-$499K
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 1.300.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$1,3-$1,39M
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 257.431
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2, align: right"]0
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 1.400.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$1,4-$1,49M
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 1.632.813
[/TD]
[TD="class: xl66, bgcolor: yellow"]$1,6-$1,69M
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$1,6-$1,69M
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 1.500.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$1,5-$1,59M
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 334.629
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2, align: right"]0
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 1.600.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$1,6-$1,69M
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 453.902
[/TD]
[TD="class: xl66, bgcolor: yellow"]$450-$499K
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$450-$499K
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 1.700.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$1,7-$1,79M
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 1.492.397
[/TD]
[TD="class: xl66, bgcolor: yellow"]$1,4-$1,49M
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$1,4-$1,49M
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 1.800.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$1,8-$1,89M
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 717.193
[/TD]
[TD="class: xl66, bgcolor: yellow"]$700-$749K
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$700-$749K
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 1.900.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$1,9-$1,99M
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] 889.905
[/TD]
[TD="class: xl66, bgcolor: yellow"]$850-$899K
[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"]$850-$899K
[/TD]
[TD="class: xl71, bgcolor: #92D050"] 2.000.000
[/TD]
[TD="class: xl70, bgcolor: #92D050"]$2M+
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"] ************
[/TD]
[TD="class: xl69, bgcolor: transparent"]*************
[/TD]
[TD="class: xl69, bgcolor: transparent"]*************
[/TD]
[TD="class: xl68, bgcolor: transparent"] ************
[/TD]
[TD="class: xl69, bgcolor: transparent"]*************
[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:

B2-> =LOOKUP(A2,{0,0;400000,"$400-$449K";450000,"$450-$499K";500000,"$500-$549K";550000,"$550-$599K";600000,"$600-$649K";
650000,"$650-$699K";700000,"$700-$749K";750000,"$750-$799K";800000,"$800-$849K";850000,"$850-$899K";900000,"$900-$949K";
950000,"$950-$999K";1000000,"$1-$1,09M";1100000,"$1,1-$1,19M";1200000,"$1,2-$1,29M";1300000,"$1,3-$1,39M";1400000,"$1,4-$1,49M";
1500000,"$1,5-$1,59M";1600000,"$1,6-$1,69M";1700000,"$1,7-$1,79M";1800000,"$1,8-$1,89M";1900000,"$1,9-$1,99M";2000000,"$2M+"})

Or

C2-> =VLOOKUP(A2,$D$2:$E$25,2)

In this case you must create the table $D$2:$E$25.

Markmzz

 

[lahmes]

The problem mark is that the values are changing consistently. for example, in the data field, those will change which will ultimately change the lookup values in the following column. Any thoughts on that additional level of issue? If the numbers were stagnant and not going to change I would do the vlookup but unfortunately that isn't the case. Thanks and any thoughts would be really helpful

 

[markmzz]

The problem mark is that the values are changing consistently. for example, in the data field, those will change which will ultimately change the lookup values in the following column. Any thoughts on that additional level of issue? If the numbers were stagnant and not going to change I would do the vlookup but unfortunately that isn't the case. Thanks and any thoughts would be really helpful

Hi Lahmes,

Sorry, but I didn't understand.

The table with VLOOKUP isn't ok?


Markmzz