# Need some help with nested if

January 28, 2013 at 00:35:58
Specs: Windows Vista

 I am trying to get a warning system that let's me know if an employee needs to be sanctionedThis is the range I want to test0.01 - 1.0 points = First Warning1.01 - 3.0 = Second Warning3.01 - 5,0 Third Warning5.01 - 6.0 Final Warning6.01 - above TerminableSo far I am using this formula but getting errors and cannot proceed I don't even know where I went wrong[code]=IF(AND(A3>0.00,A3<1.01),"FIRST WARNING",IF(AND(A3>1.00,A3<3.01),"SECOND WARNING",IF(AND(A3>3.00,A3<5.01),"THIRD WARNING",IF(AND(A3>5.00,A3<6.01),"FINAL WARNING",IF(A3>6.01),"TERMINABLE"))))))[/code/]

See More: Need some help with nested if

#1
January 28, 2013 at 01:39:06

 HiI find the best way to do this is by setting up a table and then using vlookups. The benefit of this is that if the ranges change it's a simple matter to alter the table rather than a long equation.The table would look like this...```From To Action 0.01 1.00 First Warning 1.01 3.00 Second Warning 3.01 5.00 Third Warning 5.01 6.00 Final Warning 6.01 Terminable ```The vlookup would be ...=VLOOKUP(B2,\$F\$2:\$H\$6,3,TRUE)The result would look like this```0.02 First Warning 0.09 First Warning 1.00 First Warning 1.10 Second Warning 2.90 Second Warning 3.00 Second Warning 3.01 Third Warning 3.80 Third Warning 5.00 Third Warning 5.01 Final Warning 5.02 Final Warning 6.00 Final Warning 6.01 Terminable 7.00 Terminable ```The cell references would need to be changed to suit your sheet of course. Give this a try and come back if you have any problems.

#2
January 28, 2013 at 03:27:06

 Sorry but I can't seem to understand your table.. can you clarify as to where I position these?

#3
January 28, 2013 at 03:47:57

 HiYou can position it on any free space in the worksheet or even another worksheet. I would usually place it where it can easily be referred to so the reader understands how determinations are madeLet's say your main table is from A1 to F200 I might put the reference table in H1 to J6 and that would make the second part of the lookup \$H\$1:\$J\$6.If you can describe your worksheet setup I can advise better how you might add this in and how to write the lookup.