Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Location
    Belmopan
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #4
    New Lounger
    Join Date
    Apr 2004
    Location
    Belmopan
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •