On Sat, 6 Dec 2008 03:45:00 -0800, Momo wrote:

I am stuck with the IF statement under 3 different senarios. Please HELP!

A1=Y
B1=Director or Manager or Worker
C1=2 or 1.5 or 1
D1=year of service

If A1=Y and he is Director, he will get 2 weeks pay (C1) times D1 and cap at
12 years (24 weeks). But if A1=Y and he is a Manager, he will get 1.5 weeks
pay (C1) times D1 and cap at 12 years (18 weeks). But if A1=Y and he is a
Worker, he will get 1 week pay (C1) times (D1). How do I put all 3 IF
together?

It should be something like =IF(AND(A1="Y",B1="Director" and
D1<12),C1*D1,24; and so on. But don't know how to link all 3 together.

Using your format:

A1: Y (or not Y)
B1: Position
C1: =VLOOKUP(Position,$G$1:$H$3,2,FALSE)
where G1:H3 contains a table:
Worker 1
Manager 1.5
Director 2

D1: Years

Now, assuming that Worker is also capped at 12 years, then:

=SUMPRODUCT(--(A1="Y"),C1,MIN(D1,12))

If worker is not capped at 12 years, then:

=SUMPRODUCT(--(A1="Y"),C1,MAX((C1=1)*D1,MIN(D1,12)))
--ron