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!


--
hmz
Wash DC
--
hmz
Wash DC

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"hmz" wrote:

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!


--
hmz
Wash DC
--
hmz
Wash DC

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" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"hmz" wrote:

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!


--
hmz
Wash DC
--
hmz
Wash DC

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,ROUND DOWN($E2*18,0),IF(DATEDIF($B2,TODAY(),"y")9,ROUND DOWN($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


"hmz" wrote in message
...
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!


--
hmz
Wash DC
--
hmz
Wash DC

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(A116,18,IF(AND(A19,A1<17),ROUNDD OWN(12+A1-10,0))))
A5 SEV PAY 11,442.36 "=A3*A4



"hmz" wrote:

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!


--
hmz
Wash DC
--
hmz
Wash DC