Solved 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 sanctioned
This is the range I want to test
0.01 - 1.0 points = First Warning
1.01 - 3.0 = Second Warning
3.01 - 5,0 Third Warning
5.01 - 6.0 Final Warning
6.01 - above Terminable
So 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

Report •


#1
January 28, 2013 at 01:39:06
✔ Best Answer

Hi

I 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.


Report •

#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?

Report •

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

Hi

You 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 made

Let'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.


Report •

Related Solutions

#4
January 28, 2013 at 10:16:45

=IF(B2<=0.01,"",IF(B2<=1,"First Warning",IF(B2<=3,"Second Warning",IF(B2<=5,"Third Warning",IF(B2<=6,"Final Warning","Terminable")))))

Report •


Ask Question