Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statment
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statment
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="Wor ker",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 On Dec 6, 11:45*am, 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. * |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statment
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with If statment | Excel Discussion (Misc queries) | |||
If statment | Excel Discussion (Misc queries) | |||
if statment | Excel Worksheet Functions | |||
If statment if its possible. | Excel Discussion (Misc queries) | |||
If statment | Excel Worksheet Functions |