# Thread: Nested Formula Help (2003)

1. ## 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.

Grant

2. ## 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. ## Re: Nested Formula Help (2003)

Works great!
You are my hero... thanks

4. ## 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. ## 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
•