30 levels of nesting excel

  • Thread starter Thread starter prokopof
  • Start date Start date
P

prokopof

Hello, I am using Excel 2007 and it doesn't allow me to enter more levels for
IF condition. I am trying to create mark up for every price interval for
every item. The following is what I have. Is there anything I could do about
it?
=IF(AND(G10>=0,G10<=9.99),6,IF(AND(G10>=10,G10<=12.5),1.55,IF(AND(G11>=12.51,G11<=15.63),1.531,IF(AND(G11>=15.64,G11<=19.53),1.508,
IF(AND(G11>=19.54,G11<=24.41),1.478,IF(AND(G11>=24.42,G11<=30.52),1.442,IF(AND(G11>=30.53,G11<=38.15),1.396,IF(AND(G11>=38.16,G11<=47.68),1.338,IF(AND(G11>=47.69,G11<=59.6),1.267,IF(AND(G11>=59.61,G11<=74.51),1.177,IF(AND(G11>=74.52,G11<=93.13),1.065,IF(AND(G11>=93.14,G11<=116.42),0.925,IF(AND(G11>=116.43,G11<=145.52),0.75,IF(AND(G11>=145.53,G11<=181.90),0.739,IF(AND(G11>=181.91,G11<=227.37),0.726,IF(AND(G11>=227.38,G11<=284.22),0.71,IF(AND(G11>=284.23,G11<=355.27),0.689,IF(AND(G11>=355.28,G11<=444.09),0.664,IF(AND(G11>=444.10,G11<=555.11),0.631,IF(AND(G11>=555.12,G11<=693.89),0.591,IF(AND(G11>=693.90,G11<=867.36),0.542,IF(AND(G11>=867.37,G11<=1084.2),0.478,IF(AND(G11>=1084.21,G11<=1355.25),0.4,IF(AND(G11>=1355.26,G11<=1694.07),0.397,IF(AND(G11>=1694.08,G11<=2117.58),0.394,IF(AND(G11>=2117.59,G11<=2646.98),0.39,IF(AND(G11>=2646.98,G11<=3308.72),0.39,IF(AND(G11>=3308.73,G11<=4135.90),0.379,IF(AND(G11>=4135.91,G11<=516988),0.372,IF(AND(G11>=5169.89,G11<=6462.35),0.362,IF(AND(G11>=6462.36,0.362))
 
Use the VLOOKUP() function instead.

Here's a tutorial:

http://www.contextures.com/xlFunctions02.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hello, I am using Excel 2007 and it doesn't allow me to enter more levels for
| IF condition. I am trying to create mark up for every price interval for
| every item. The following is what I have. Is there anything I could do about
| it?
|
=IF(AND(G10>=0,G10<=9.99),6,IF(AND(G10>=10,G10<=12.5),1.55,IF(AND(G11>=12.51,G11<=15.63),1.531,IF(AND(G11>=15.64,G11<=19.53),1.508,
|
IF(AND(G11>=19.54,G11<=24.41),1.478,IF(AND(G11>=24.42,G11<=30.52),1.442,IF(AND(G11>=30.53,G11<=38.15),1.396,IF(AND(G11>=38.16,G11<=47.68),1.338,IF(AND(G11>=47.69,G11<=59.6),1.267,IF(AND(G11>=59.61,G11<=74.51),1.177,IF(AND(G11>=74.52,G11<=93.13),1.065,IF(AND(G11>=93.14,G11<=116.42),0.925,IF(AND(G11>=116.43,G11<=145.52),0.75,IF(AND(G11>=145.53,G11<=181.90),0.739,IF(AND(G11>=181.91,G11<=227.37),0.726,IF(AND(G11>=227.38,G11<=284.22),0.71,IF(AND(G11>=284.23,G11<=355.27),0.689,IF(AND(G11>=355.28,G11<=444.09),0.664,IF(AND(G11>=444.10,G11<=555.11),0.631,IF(AND(G11>=555.12,G11<=693.89),0.591,IF(AND(G11>=693.90,G11<=867.36),0.542,IF(AND(G11>=867.37,G11<=1084.2),0.478,IF(AND(G11>=1084.21,G11<=1355.25),0.4,IF(AND(G11>=1355.26,G11<=1694.07),0.397,IF(AND(G11>=1694.08,G11<=2117.58),0.394,IF(AND(G11>=2117.59,G11<=2646.98),0.39,IF(AND(G11>=2646.98,G11<=3308.72),0.39,IF(AND(G11>=3308.73,G11<=4135.90),0.379,IF(AND(G11>=4135.91,G11<=516988),0.372,IF(AND(G11>=5169.89,G11<=6462.35),0.362,IF(AND(G11>=6462.36,0.362))
 
Back
Top