IF Formula Problem

[Guest]

I am trying to put thge following formula into one cell (C5) and the formula
error keeps popping. Does anyone have any suggetions how to enter this
formula correctly into one cell?


IF(C5<=0.49,"6",IF(C5<=0.99,"5.75",IF(C5<=1.49,"5.5",IF(C5<=1.99,"5",IF(C5<=2.49,"4.75",IF(C5<=2.99,"4.75",IF(C5<=3.99,"4.5",IF(C5<=4.99,"4.375",IF(C5<=5.99,"4.25",IF(C5<=6.99,"4.125",IF(C5<=7.99,"4",IF(C5<=8.99,"3.75",IF(C5<=9.99,"3.625",IF(C5<=19.99,"3.5",IF(C5<=29.99,"3.375",IF(C5<=39.99,"3.25",IF(C5<=49.99,"3.125",IF(C5<=59.99,"3",IF(C5<=69.99,"2.75",IF(C5<=79.99,"2.625",IF(C5<=89.99,"2.5",IF(C5<=99.99,"2.3332",IF(C5<=139.99,"2.2499",IF(C5<=169.99,"2.1666",IF(C5<=199.99,"2",IF(C5<=239.99,"1.855",IF(C5<=269.99,"1.8225",IF(C5<=299.99,"1.7862",IF(C5<=349.99,"1.75",IF(C5=399.99,"1.725",IF(C5<=499.99,"1.6875",IF(C5<=749.99,"1.6",IF(C5<=999.99,"1.55",IF(C5<=1499.99,"1.5",IF(C5<=1999.99,"1.45",IF(C5<=2999.99,"1.4",IF(C5<=4999.99,"1.35",IF(C5<=9999.99,"1.3375",IF(C5<=24999.99,"1.3333",IF(C5<=49999.00,"1.33"))

 

[Guest]

Well, there are two problems:

1) You are limited to 7 nested IF functions
2) You have too few closing parentheses.

Since you violate number 1 above, number 2 is irrelevant. I would create a
lookup table with the values for which you want to test the value of C and
then us a VLOOKUP function to return the appropriate number.

Dave

 

[Guest]

have you tried adding all of the closing parenthesis? I had the same problem
a little bit ago, and the formula wouldn't work until I had a close
parenthesis for every open parenthesis.

Ashley

 

[Guest]

BTW, if you're really wedded to the use of IF functions, there is a sort of
workaround for the limit of 7 nested functions:
http://www.cpearson.com/excel/nested.htm

Dave

 

[Don Guillett]

Unless there is a common formula that would be the same for all conditions
use a vlookup table instead. Look in the help index for vLOOKUP

 

[Ron Rosenfeld]

I am trying to put thge following formula into one cell (C5) and the formula
error keeps popping. Does anyone have any suggetions how to enter this
formula correctly into one cell?


IF(C5<=0.49,"6",IF(C5<=0.99,"5.75",IF(C5<=1.49,"5.5",IF(C5<=1.99,"5",IF(C5<=2.49,"4.75",IF(C5<=2.99,"4.75",IF(C5<=3.99,"4.5",IF(C5<=4.99,"4.375",IF(C5<=5.99,"4.25",IF(C5<=6.99,"4.125",IF(C5<=7.99,"4",IF(C5<=8.99,"3.75",IF(C5<=9.99,"3.625",IF(C5<=19.99,"3.5",IF(C5<=29.99,"3.375",IF(C5<=39.99,"3.25",IF(C5<=49.99,"3.125",IF(C5<=59.99,"3",IF(C5<=69.99,"2.75",IF(C5<=79.99,"2.625",IF(C5<=89.99,"2.5",IF(C5<=99.99,"2.3332",IF(C5<=139.99,"2.2499",IF(C5<=169.99,"2.1666",IF(C5<=199.99,"2",IF(C5<=239.99,"1.855",IF(C5<=269.99,"1.8225",IF(C5<=299.99,"1.7862",IF(C5<=349.99,"1.75",IF(C5=399.99,"1.725",IF(C5<=499.99,"1.6875",IF(C5<=749.99,"1.6",IF(C5<=999.99,"1.55",IF(C5<=1499.99,"1.5",IF(C5<=1999.99,"1.45",IF(C5<=2999.99,"1.4",IF(C5<=4999.99,"1.35",IF(C5<=9999.99,"1.3375",IF(C5<=24999.99,"1.3333",IF(C5<=49999.00,"1.33"))

Under Excel's specifications, there is a limit to nesting functions. You may
only nest seven functions. You have many more than that.

Look at HELP for VLOOKUP for a better solution. To have it all in one cell,
you could use the formula below. But you would be better off setting up a
table as described in VLOOKUP help.


=VLOOKUP(C5,{0.49,6;0.99,5.75;1.49,5.5;1.99,5;2.49,4.75;2.99,4.75;3.99,4.5;4.99,4.375;5.99,4.25;6.99,4.125;7.99,4;8.99,3.75;9.99,3.625;19.99,3.5;29.99,3.375;39.99,3.25;49.99,3.125;59.99,3;69.99,2.75;79.99,2.625;89.99,2.5;99.99,2.3332;139.99,2.2499;169.99,2.1666;199.99,2;239.99,1.855;269.99,1.8225;299.99,1.7862;349.99,1.75;399.99,1.725;499.99,1.6875;749.99,1.6;999.99,1.55;1499.99,1.5;1999.99,1.45;2999.99,1.4;4999.99,1.35;9999.99,1.3375;24999.99,1.3333;49999,1.33},2)



--ron