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-98 and 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(C ELL=86,"7",IF(CELL=90,"6",IF(CELL=93,"5",IF(CELL=9 6,"4",IF(CELL=98,"3",IF(CELL=99,"2"))))))))))))))) ))))

If you haven't already, you may want to check out the VLOOKUP function in the
help files.
If the choices you listed are the only ones you need, this should do what
you want:
=VLOOKUP(B12,{2,2;4,3;7,4;10,5;14,6;19,7;25,8;31,9 ;41,10;59,11;69,10;75,9;81,8;86,7;90,6;93,5;96,4;9 8,3;99,2},2,0)


--
tj


"Palencia1978" wrote:


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-98 and 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(C ELL=86,"7",IF(CELL=90,"6",IF(CELL=93,"5",IF(CELL=9 6,"4",IF(CELL=98,"3",IF(CELL=99,"2"))))))))))))))) ))))


--
Palencia1978

In this particular case, the return values for the formula are nicely
arranged in numerical sequence, so you can use the MATCH function:

=MATCH(CELL,{4,7,10,14,19,25,31,41,50},0)+1

"Palencia1978" wrote:


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-98 and 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(C ELL=86,"7",IF(CELL=90,"6",IF(CELL=93,"5",IF(CELL=9 6,"4",IF(CELL=98,"3",IF(CELL=99,"2"))))))))))))))) ))))


--
Palencia1978