|
IF Formula Problem
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.9 9,"3.5",IF(C5<=29.99,"3.375",IF(C5<=39.99,"3.25",I F(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<=1 69.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<=4 9999.00,"1.33")) |
IF Formula Problem
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 -- Brevity is the soul of wit. "kris" wrote: 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.9 9,"3.5",IF(C5<=29.99,"3.375",IF(C5<=39.99,"3.25",I F(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<=1 69.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<=4 9999.00,"1.33")) |
IF Formula Problem
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 "kris" wrote: 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.9 9,"3.5",IF(C5<=29.99,"3.375",IF(C5<=39.99,"3.25",I F(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<=1 69.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<=4 9999.00,"1.33")) |
IF Formula Problem
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 -- Brevity is the soul of wit. "Dave F" wrote: 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 -- Brevity is the soul of wit. "kris" wrote: 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.9 9,"3.5",IF(C5<=29.99,"3.375",IF(C5<=39.99,"3.25",I F(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<=1 69.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<=4 9999.00,"1.33")) |
IF Formula Problem
On Mon, 2 Oct 2006 11:13:02 -0700, kris wrote:
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(C 5<=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;7 9.99,2.625;89.99,2.5;99.99,2.3332;139.99,2.2499;16 9.99,2.1666;199.99,2;239.99,1.855;269.99,1.8225;29 9.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;2 999.99,1.4;4999.99,1.35;9999.99,1.3375;24999.99,1. 3333;49999,1.33},2) --ron |
| All times are GMT +1. The time now is 03:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com