Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nested Formula Help (2003)

    I have a need for a nested formula that exceedes Excels more that the seven nested formula threashold.

    I work for a fire department that uses Excel to create our annual budget. Due to a recent contract negotiaton, we now have more than seven levels for longevity pay. Can anyone help me by providing insight as to how best create a custom function or solution that will do the following?

    =IF(U5<5,0,
    IF(AND(U5>=5,U5<9),$B$262*0.01,
    IF(AND(U5>=9,U5<13),$B$262*0.02,
    IF(AND(U5>=13,U5<17),$B$262*0.03,
    IF(AND(U5>=17,U5<21),$B$262*0.04,
    IF(AND(U5>=21,U5<25),$B$262*0.05,
    IF(AND(U5>=25,U5<29),$B$262*0.06,
    IF(AND(U5>=29,U5<33),$B$262*0.07,
    IF(AND(U5>=33,U5<40),$B$262*0.08,
    )))))))))

    The formula above is an example for one employee.
    There are 150 rows, one for each employee
    "U5" in the formula refers to a the hire date of the employee
    "$B$262" in the formula refers to a a cell with a fixed dollar amount (1st Class Firefighter) that is the same for all employees

    For clarification, this is the wording in the contract that is used to create the formula.

    For the duration of this Agreement, longevity for all classifications within the bargaining unit shall be as follows:
    Upon completion of 5 years of service: 1% of 1st Class Firefighter per month;
    Upon completion of 9 years of service: 2% of 1st Class Firefighter per month;
    Upon completion of 13 years of service: 3% of 1st Class Firefighter per month;
    Upon completion of 17 years of service: 4% of 1st Class Firefighter per month;
    Upon completion of 21 years of service: 5% of 1st Class Firefighter per month.
    Upon completion of 25 years of service: 6% of 1st Class Firefighter per month.
    Upon completion of 29 years of service: 7% of 1st Class Firefighter per month.
    Upon completion of 33 years of service: 8% of 1st Class Firefighter per month.

    Thank you for your help,
    Grant

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nested Formula Help (2003)

    Welcome to Woody's Lounge!

    Try this formula:

    =MIN(TRUNC((U5-1)/4),8)/100*$B$262

    This avoids the use of IF functions entirely. I have assumed that 8% is the maximum.

  3. #3
    New Lounger
    Join Date
    Mar 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested Formula Help (2003)

    Works great!
    You are my hero... thanks

  4. #4
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested Formula Help (2003)

    Hans, would you mind explaining what exactly your formula is doing. I understand the MIn and Trunc function, but I don't understand the results that are produced. For example when I entered a start date of March 5, 2000, which results in 5 years of service and 1000 in B262, the result of the formula is 91620. I assume that this is a dollar figure because B262 is referring to a fixed $ amount. What is your division by 4 doing in the formula?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nested Formula Help (2003)

    You must have made a mistake somewhere, for an amount of 1000, the formula - if entered correctly - will never return a value greater than 80.

Posting Permissions

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