Severance Formulas: number of severance weeks based on salary grade level and years of service

[TMalaska]

I am looking for an IF formula for...number of severance weeks based on salary grade level...for example:

IF grade level 21-25 receives 10 weeks of severance @ [amount] (based on individual salary)
IF grade level 26-27 receives 12 weeksof severance @ [amount] (based on individual salary)
IF grade level 28-29 receives 16 weeks of severance @ [amount] (based on individual salary)

**Also**

I need a simple lump sum formula for severance pay (lump sum amount) based on years of service...for example:

IF 5 yr = 1 wk @$1000 (this number will be based on a weekly salary amount)
IF 6 yr = 2 wks @$2000
IF 7 yr = 3 wks
and so on...

I would appreciate any help!

 

[Logit]

.


[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]

Grade Level​

[/td][td]

Wks Pay​

[/td][td]

Current Wkly Salary​

[/td][td]

Total Payout​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

21-25​

[/td][td]

10​

[/td][td]

$1,252.37​

[/td][td]

$12,523.70​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

26-27​

[/td][td]

12​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

28-29​

[/td][td]

16​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]

Formula in D2 and copied down column: =IF(C2="","",B2*C2)​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]


I don't understand the specifics of your second request.

I need a simple lump sum formula for severance pay (lump sum amount) based on years of service...for example:

IF 5 yr = 1 wk @$1000 (this number will be based on a weekly salary amount)
IF 6 yr = 2 wks @$2000
IF 7 yr = 3 wks
and so on...

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E
1	Grade	Years of service	Weekly Salary	Payment	Lump Sum
2	21	5	1,234.56	12,345.60	1,234.56
3	22	6	1,234.56	12,345.60	2,469.12
4	23	7	1,234.56	12,345.60	3,703.68
5	24	8	1,234.56	12,345.60	4,938.24
6	25	9	1,234.56	12,345.60	6,172.80
7	26	10	1,234.56	14,814.72	7,407.36
8	27	11	1,234.56	14,814.72	8,641.92
9	28	12	1,234.56	19,752.96	9,876.48
10	29	13	1,234.56	19,752.96	11,111.04
Quote

Cell Formulas
Range	Formula
D2	=IF(A2<26,C2*10,IF(A2<28,C2*12,IF(A2<30,C2*16)))
E2	=C2*(B2-4)