help with an if formula

  • Thread starter Thread starter hmz
  • Start date Start date
H

hmz

Hi,

I am having trouble writing a formula for the following:

Severance Plan:

12 Weeks of base pay, plus an additional 1 week of base pay for every year
worked service more than 10 years of service, to maximum of 18 weeks in
total.

So, in other words, if someone has worked over 10 years, say it is 22 years,
then they would be entitled to at least a total of 18 weeks (12+6 weeks).

My problem is writing a formula that captures all three "or" outcomes of (1)
under 10 years (2) (or) over 10 years but under Max amount of 18 (3) over the
18 weeks.

So, I am looking for something to say,

Salary: 25,000 weekly $480

Years worked: 20, 5, and 17

please help! Thanks!
 
Hi,

Try something like this

=IF(A1<10,0,MIN(8,A1-10)*480)

or

=IF(A1<10,0,MIN(8,A1-10)*D1)

where column A contains the number of year. Where D1 would contain the
weekly pay.
 
Well, I sort have figured out the formula now, but I do not know how to write
it correctly. I think it is an OR statement: like this:

=if(x<10,y,if(x<10and<16,16*#,if(x<26,#*#,#))

I am saying something like, If x is going to be this, then this, if x is
less than this and less than this, then this, if less is greater than this,
then that.

does this make sense? thank you,
--
hmz
Wash DC


Shane Devenshire said:
Hi,

Try something like this

=IF(A1<10,0,MIN(8,A1-10)*480)

or

=IF(A1<10,0,MIN(8,A1-10)*D1)

where column A contains the number of year. Where D1 would contain the
weekly pay.
Click to expand...
 
hmz,
Try the following in an Excel Sheet:

Header Row:
A1: Name
B1: Hire Date (format mm/dd/yyyy)
C1: YearsWkd
D1: Salary Yr
E1: Salary Wk
F1: Sev Pay

Formulas:
C2: =if($A2="","",datedif($B2,today(),"y"))
This will give the full years completed, so that some one who worked for
15.5 yrs, will have his/her weekly salary multiplied by 15, not 15.5 or 16.

E2: =if($A2="","",ROUNDDOWN($D2/52,0))

F2:
=IF($A2="","",IF(DATEDIF($B2,TODAY(),"y")>17,ROUNDDOWN($E2*18,0),IF(DATEDIF($B2,TODAY(),"y")>9,ROUNDDOWN($C2*$E2,0),"No
Severence")))

Drag the formulas down the column, one at a time.

--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


Hi,

I am having trouble writing a formula for the following:

Severance Plan:

12 Weeks of base pay, plus an additional 1 week of base pay for every year
worked service more than 10 years of service, to maximum of 18 weeks in
total.

So, in other words, if someone has worked over 10 years, say it is 22 years,
then they would be entitled to at least a total of 18 weeks (12+6 weeks).

My problem is writing a formula that captures all three "or" outcomes of (1)
under 10 years (2) (or) over 10 years but under Max amount of 18 (3) over
the
18 weeks.

So, I am looking for something to say,

Salary: 25,000 weekly $480

Years worked: 20, 5, and 17

please help! Thanks!
 
Try the fololwing in Excel
CELL DESCRIPTION
A1 YEARS WKD 15.00 FILL OUT
A2 SALARY/YR 35,000.00 FILL OUT
A3 SALARY/WK 673.08 "=ROUND(A2/52,2)"
A4 SEV WKS TO
PAY 17 "=IF(A1<10,0,IF(A1>16,18,IF(AND(A1>9,A1<17),ROUNDDOWN(12+A1-10,0))))
A5 SEV PAY 11,442.36 "=A3*A4
 
Back
Top