nested IF AND too cumbersome

[barnabas]

I've studied other nested IF posts and can't figure out how to use Vlookup or
a named range on sheet2 to solve my problem. I've accomplished the task but
very clumsily. There are 8 nested IFs so have to use two cells because of the
7 limit.

The spreadsheet is to determine the vacation factor for each employee based
on full time or part time status and years of service.

A3= "N" or "Y" (Full time? yes or no)
E3= years of service
F3: IF(L3,L3,M3) (this is the factor column)
L3:
=IF(AND(A3="N",E3>0,E3<2),0.0196,
IF(AND(A3="N",E3>1.9999,E3<10),0.04,
IF(AND(A3="N",E3>9.9999,E3<20),0.0612,
IF(AND(A3="N",E3>20),0.0833,M3))))
M3:
=IF(AND(A3="Y",E3>0,E3<1),0.0196,
IF(AND(A3="Y",E3>0.9999,E3<2),0.0273,
IF(AND(A3="Y",E3>1.9999,E3<10),0.0554,
IF(AND(A3="Y",E3>9.9999,E3<20),0.0766,0.0987))))

 

[bpeltzer]

I'd still prefer a vlookup (or maybe two, one for full-time and one for
part-time). But if you like IFs, it can still be simpler... First test for
Y/N, and only then for the years of service. I'm hoping we can assume the
service is always >0..
=IF(A3="Y",IF(E3<1,0.0196,IF(E3<2,0.0273,IF(E3<10,0.05544,IF(E3<20,0.0766,0.0987)))),IF(A3="N",IF(E3<2,0.0196,IF(E3<10,0.04,IF(E3<20,0.0612,0.0833))),"invalid status"))
(Note that I'm also eliminating the ANDs by testing for the years of service
in increasing order; if the <2 test fails, I don't have to test for >=2 in
the next clause.)

 

[JMB]

A lookup approach.

=IF(A3="N",VLOOKUP(E3,{0,0.0196;2,0.04;10,0.0612;20,0.0833},2),IF(A3="Y",VLOOKUP(E3,{0,0.0196;1,0.0273;2,0.0554;10,0.0766;20,0.0987},2),"Invalid Data"))

The lookup tables (data inside of the braces { }) could be put elsewhere,
then you could just reference the tables - it would be easier to update.

For example, H1:I4
0 0.0196
2 0.04
10 0.0612
20 0.0833

Then the first part of the formula changes to:
=IF(A3="N",VLOOKUP(E3,H1:I4,2)...........

 

[T. Valko]

Create this 3 column table:

...........H.............I................J
1.......................Y..............N
2........0........0.0196......0.0196
3........1........0.0273......0.0196
4........2........0.0554......0.04
5......10........0.0766......0.0612
6......20........0.0987......0.0833

Then:

=IF(OR(A3="",E3=""),"",VLOOKUP(E3,H2:J6,MATCH(A3,H1:J1,0)))

Biff

 

[barnabas]

Thanks to bpeltzer, JMB and T.Valko! I like IFs but think I'll learn the
vlookup.