Results 1 to 5 of 5
Thread: Nested Formula Help (2003)

20050315, 02:13 #1
 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

20050315, 02:21 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Nested Formula Help (2003)
Welcome to Woody's Lounge!
Try this formula:
=MIN(TRUNC((U51)/4),8)/100*$B$262
This avoids the use of IF functions entirely. I have assumed that 8% is the maximum.

20050315, 02:30 #3
 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

20050315, 16:43 #4
 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?

20050315, 16:55 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.