Terminal Benefits - Nested If's

  • Thread starter Thread starter mday36
  • Start date Start date
M

mday36

I am trying to calculate our liabilty of current Terminal Benefits.
Using the YEARFRAC and ROUNDDOWN functions (in separate columns & I'
sure it can easily be calculated in one column) I have assessed th
number of completed years service.
The criteria for Terminal Benefits is:
1. Minimum 3 years service.
2. 3 weeks pay for each completed years service up to 5 years.
3. 1 months pay for each completed year in excess of 5 years.

Criteria 1 should read something like IF [completed years]<3 =0
Criteria 2 is causing the greatest headache - especially those tha
have completed less than 5 years service. (3 weeks expressed i
decimal is 0.057692.
Criteria 3 should read something like IF [completed years]>5
(([completed years]-5)*[monthly rate])+[calculation for criteria 2]

I suspect that this is a two-cell calculation rather than a nested I
in one cell, but would be grateful for advice.

I attach the spreadsheet - Terminal Benefits.xl

Attachment filename: term benefits.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=51714
 
'=ROUNDDOWN(((+$E$3-B13)/365),0), E3 is hire date & B13 is evaluation date, should get years. Hope this will help.
 
'=IF(C15>=20,200,IF(C15>=15,160,IF(C15>=7,120,IF(C15>=3,80,IF(C15>=2,40,IF(C15>=1,40,IF(C15<=1,0,IF(C15<1,"err","err")))))))) - This evaluates the formula below, which was suppossed to find years, with the cutoffs below the formula. It was really for accruing vacation

'=ROUNDDOWN(((+$E$3-B15)/365),0) - E3 had the evaluation date, B15 was hire date, this yields year

<1 = 0 <15=120 - These were the cutoff
<3=40 <20=16
<7=80 >20=20
(I think you cn addapt this, but it might be hard to combine into a single formula, cell. Sorry for the previous post, I just woke up)
 
Back
Top