Using IF in a formula

[kris]

I am trying to enter multiple IF values into one cell. I enter all the values
and when I try to close out of the formula an error comes up and will not let
me. Do I have too many values? here is what I am trying to enter:


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"))


....any suggestions? THANKS!!!!

 

[Gord Dibben]

You are limited to 7 nested "IF's"

For your purposes a lookup table would be more suitable.

Two column table and a VLOOKUP formula.

See Debra Dalgleish's site for excellent instructions on VLOOKUP formulas and a
downloadable sample workbook to assist.

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben MS Excel MVP

 

[Rodrigo Ferreira]

Did you try to use vlookup?

F G H
min max result
0 0,49 6
0,5 0,99 5,75
1 1,49 5,5
....

=VLOOKUP(C2, F2:H5, 3, 1)

Rodrigo Ferreira

 

[Ron Coderre]

Try something like this:

Enter this table in a range (I'll assume E1:F40)
0 6.000
0.50 5.750
1.00 5.500
1.50 5.000
2.00 4.750
2.50 4.750
3.00 4.500
4.00 4.375
5.00 4.250
6.00 4.125
7.00 4.000
8.00 3.750
9.00 3.625
10.00 3.500
20.00 3.375
30.00 3.250
40.00 3.125
50.00 3.000
60.00 2.750
70.00 2.625
80.00 2.500
90.00 2.333
100.00 2.250
140.00 2.167
170.00 2.000
200.00 1.855
240.00 1.823
270.00 1.786
300.00 1.750
350.00 1.725
400.00 1.688
500.00 1.600
750.00 1.550
1000.00 1.500
1500.00 1.450
2000.00 1.400
3000.00 1.350
5000.00 1.338
10000.00 1.333
25000.00 1.330

Then
For a value in C5

F5: =VLOOKUP(C5,$E$1:$F$40,2,1)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Avi]

Hi Kris

I think there are too many conditions - Please see this information from Ms
Excel Help:

Up to seven IF functions can be nested as value_if_true and value_if_false
arguments to construct more elaborate tests. If you want to test more than
seven conditions, consider using the LOOKUP, VLOOKUP, or HLOOKUP function.

I suggest that you create a list of values and then use Vlookup to pick up
this values.

Hope this helps