Get Past the 7 Nested IF limit

[Palencia1978]

I need to implement a more than 7 IF nested Statements in a Master Excel sheet I am creating. It is a Tolerance Table that I need to put several times across the sheet and across the book. I know it can be done in VBA but I am not an expert in VBA.

Of course I tried to write the whole formula in EXCEL although I know it was not going to be accepted.

Average% intervals__Tolerance
99 and 2_______________2
97-98and 3-4___________3
94-96 and 5-7__________4
91-93 and 8-10_________5
87-90 and 11-14________6
82-86 and 15-19________7
76-81 and 20-25________8
70-75 and 26-31________9
60-69 and 32-41________10
51-59 and 42-50________11


=IF(CELL=2,"2",IF(CELL<=4,"3",IF(CELL<=7,"4",IF(CELL<=10,"5",IF(CELL<=14,"6",IF(CELL<=19,"7",IF(CELL<=25,"8",IF(CELL<=31,"9",IF(CELL<=41,"10",IF(CELL<=59,"11",IF(CELL<=69,"10",IF(CELL<=75,"9",IF(CELL<=81,"8",IF(CELL<=86,"7",IF(CELL<=90,"6",IF(CELL<=93,"5",IF(CELL<=96,"4",IF(CELL<=98,"3",IF(CELL=99,"2")))))))))))))))))))

[DaveBo]

Can you make a small "lookup" worksheet with the references of value=>tolerance
e.g.
2 2
3 3
4 3
5 4 etc up to 99 2
and then in your calculation use the Lookup function
e.g. Lookup(cell? , LookupSheet!A1:A98, LookupSheet!B1:B98 )

[DKenny]

Or use a table with only the values where you tolerance changes and use a range lookup function. That way you only need 19 values in your lookup table.

Copy the following in Cells (A1:B20)

Value Tolerance
0 2
3 3
5 4
8 5
11 6
15 7
20 8
26 9
32 10
42 11
60 10
70 9
76 8
82 7
87 6
91 5
94 4
97 3
99 2

The use this formula:
=VLOOKUP(YourCellRef,$A$2:$B$20,2,1)

[Palencia1978]

Thank you guys, I finally managed to make my own formula with VBA and It worked!!!