# Thread: Multiple calculations = Multiple IFs? (Office 97)

1. ## Multiple calculations = Multiple IFs? (Office 97)

I want to create a spreadsheet for calculating Terminal Benefits.
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.

Using the YEARFRAC and ROUNDDOWN functions (in separate columns & I'm sure it can easily be calculated in one column) I have assessed the number of completed years service.

Criteria 1 should read something like IF [completed years]<3 =0
Criteria 2 is causing the greatest headache - especially those that have completed between 3 and 5 years service. (3 weeks expressed in 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 multiple IF in one cell, but would be grateful for advice.

2. ## Re: Multiple calculations = Multiple IFs? (Office

Hi Mark

Try something like this
A1 = Completed Years
A2 = 3 weeks pay
A3 = 1 months pay

=IF(A1<3,0,IF(A1<=5,A1*A2,A2*5+(A1-5)*A3))

3. ## Re: Multiple calculations = Multiple IFs? (Office 97)

If A1 has the startdate, the monthlyrate is named "MonthlyRate" the 3 wks rate is named "ThreeWeeks", then this megaformula, should calc what you want:

=(MAX(0,DATEDIF(A1,TODAY(),"y")-5)*MonthlyRate+(DATEDIF(A1,TODAY(),"y")-MAX(DATEDIF(A1,TODAY(),"y")-5,0))*ThreeWeeks)*((DATEDIF(A1,TODAY(),"y")-3)>=0)

Steve

4. ## Re: Multiple calculations = Multiple IFs? (Office

Tony,

Brilliant! Yet so simple - should have thought to provide a column for 3 weeks pay!

Many thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•