IF Statment

[Momo]

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.

 

[Pete_UK]

Is the worker's Years of service capped at 12 also?

And will C1 be filled automatically? With something like this

=IF(B1="Director",2,IF(B1="Manager",1.5,IF(B1="Worker",1,0)))

or through a lookup formula?

Will B1 only contain those 3 values ?

If so, then you can use this in D1:

=IF(A1="Y",MIN(12,D1)*C1,0)

Hope this helps.

Pete

 

[Ron Rosenfeld]

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