Trying to increment a salary between an Age Range with a %-increase after the 1st year.

[ccleland]

Age Range 46-65 The kicker here is the 2nd number....you have to use the number above it to get it and so on...
Salary 60,000 I am trying to do this in all one formula.....nested IF and VLOOKUPS, even my HAIR hurts...
% Increase = 3% anybody with any ideas would be greatly appreciated.

Start Age
25
26
26
..
..
...
46 - 60,000
47 - 61,800
48 - 63,654
...
...
65 - whatever
66 - BLANK
All Blanks after to age 100.

 

[Special-K99]

Might help by explaining your problem properly rather than in pieces.

 

[ccleland]

I'm a Certified Financial Planner(TM), I have a client that is currently age-25 and is planning on strarting a 2nd job at age 46. He is planning on working this 2nd job until age 65 and he is planning on living until age 92. He is planning on a 3% increase every year durning his 2nd job. Now I might get other clients who will start working a 2nd job from (any-age to any-age). SO if I create an age column then I create an 2nd salary column, I want to show his salary between the ages of 46-65 with the proper increase of 3% each year. All the rest of the cells should be blank. (How's that?)

 

[Jeff Meyers]

Give this a try:

Sheet1[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD] Salary [/TD]
[TD="align: right"] 60,000 [/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD] Increase [/TD]
[TD="align: right"]3.0%[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD] Start [/TD]
[TD="align: right"] 30 [/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD] End [/TD]
[TD="align: right"] 45 [/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD] -------------- [/TD]
[TD] -------------- [/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD="align: right"] 25 [/TD]
[TD="align: right"] 0 [/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD="align: right"] 26 [/TD]
[TD="align: right"] 0 [/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="align: right"] 27 [/TD]
[TD="align: right"] 0 [/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD="align: right"] 28 [/TD]
[TD="align: right"] 0 [/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD="align: right"] 29 [/TD]
[TD="align: right"] 0 [/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD="align: right"] 30 [/TD]
[TD="align: right"] 60,000 [/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD="align: right"] 31 [/TD]
[TD="align: right"] 61,800 [/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD="align: right"] 32 [/TD]
[TD="align: right"] 63,654 [/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD="align: right"] 33 [/TD]
[TD="align: right"] 65,564 [/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD="align: right"] 34 [/TD]
[TD="align: right"] 67,531 [/TD]
[/TR]
[TR]
[TH]16[/TH]
[TD="align: right"] 35 [/TD]
[TD="align: right"] 69,557 [/TD]
[/TR]
[TR]
[TH]17[/TH]
[TD="align: right"] 36 [/TD]
[TD="align: right"] 71,644 [/TD]
[/TR]
[TR]
[TH]18[/TH]
[TD="align: right"] 37 [/TD]
[TD="align: right"] 73,793 [/TD]
[/TR]
[TR]
[TH]19[/TH]
[TD="align: right"] 38 [/TD]
[TD="align: right"] 76,007 [/TD]
[/TR]
[TR]
[TH]20[/TH]
[TD="align: right"] 39 [/TD]
[TD="align: right"] 78,287 [/TD]
[/TR]
[TR]
[TH]21[/TH]
[TD="align: right"] 40 [/TD]
[TD="align: right"] 80,636 [/TD]
[/TR]
[TR]
[TH]22[/TH]
[TD="align: right"] 41 [/TD]
[TD="align: right"] 83,055 [/TD]
[/TR]
[TR]
[TH]23[/TH]
[TD="align: right"] 42 [/TD]
[TD="align: right"] 85,547 [/TD]
[/TR]
[TR]
[TH]24[/TH]
[TD="align: right"] 43 [/TD]
[TD="align: right"] 88,113 [/TD]
[/TR]
[TR]
[TH]25[/TH]
[TD="align: right"] 44 [/TD]
[TD="align: right"] 90,756 [/TD]
[/TR]
[TR]
[TH]26[/TH]
[TD="align: right"] 45 [/TD]
[TD="align: right"] 93,479 [/TD]
[/TR]
[TR]
[TH]27[/TH]
[TD="align: right"] 46 [/TD]
[TD="align: right"] 0 [/TD]
[/TR]
[TR]
[TH]28[/TH]
[TD="align: right"] 47 [/TD]
[TD="align: right"] 0 [/TD]
[/TR]
[TR]
[TH]29[/TH]
[TD="align: right"] 48 [/TD]
[TD="align: right"] 0 [/TD]
[/TR]
[TR]
[TH]30[/TH]
[TD="align: right"] 49 [/TD]
[TD="align: right"] 0 [/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
B6	=IF(OR(A6<$B$3,A6>$B$4),0,IF(A6=$B$3,$B$1,ROUND(B5*(1+$B$2),0)))
B7	=IF(OR(A7<$B$3,A7>$B$4),0,IF(A7=$B$3,$B$1,ROUND(B6*(1+$B$2),0)))
B8	=IF(OR(A8<$B$3,A8>$B$4),0,IF(A8=$B$3,$B$1,ROUND(B7*(1+$B$2),0)))
B9	=IF(OR(A9<$B$3,A9>$B$4),0,IF(A9=$B$3,$B$1,ROUND(B8*(1+$B$2),0)))
B10	=IF(OR(A10<$B$3,A10>$B$4),0,IF(A10=$B$3,$B$1,ROUND(B9*(1+$B$2),0)))
B11	=IF(OR(A11<$B$3,A11>$B$4),0,IF(A11=$B$3,$B$1,ROUND(B10*(1+$B$2),0)))
B12	=IF(OR(A12<$B$3,A12>$B$4),0,IF(A12=$B$3,$B$1,ROUND(B11*(1+$B$2),0)))
B13	=IF(OR(A13<$B$3,A13>$B$4),0,IF(A13=$B$3,$B$1,ROUND(B12*(1+$B$2),0)))
B14	=IF(OR(A14<$B$3,A14>$B$4),0,IF(A14=$B$3,$B$1,ROUND(B13*(1+$B$2),0)))
B15	=IF(OR(A15<$B$3,A15>$B$4),0,IF(A15=$B$3,$B$1,ROUND(B14*(1+$B$2),0)))
B16	=IF(OR(A16<$B$3,A16>$B$4),0,IF(A16=$B$3,$B$1,ROUND(B15*(1+$B$2),0)))
B17	=IF(OR(A17<$B$3,A17>$B$4),0,IF(A17=$B$3,$B$1,ROUND(B16*(1+$B$2),0)))
B18	=IF(OR(A18<$B$3,A18>$B$4),0,IF(A18=$B$3,$B$1,ROUND(B17*(1+$B$2),0)))
B19	=IF(OR(A19<$B$3,A19>$B$4),0,IF(A19=$B$3,$B$1,ROUND(B18*(1+$B$2),0)))
B20	=IF(OR(A20<$B$3,A20>$B$4),0,IF(A20=$B$3,$B$1,ROUND(B19*(1+$B$2),0)))
B21	=IF(OR(A21<$B$3,A21>$B$4),0,IF(A21=$B$3,$B$1,ROUND(B20*(1+$B$2),0)))
B22	=IF(OR(A22<$B$3,A22>$B$4),0,IF(A22=$B$3,$B$1,ROUND(B21*(1+$B$2),0)))
B23	=IF(OR(A23<$B$3,A23>$B$4),0,IF(A23=$B$3,$B$1,ROUND(B22*(1+$B$2),0)))
B24	=IF(OR(A24<$B$3,A24>$B$4),0,IF(A24=$B$3,$B$1,ROUND(B23*(1+$B$2),0)))
B25	=IF(OR(A25<$B$3,A25>$B$4),0,IF(A25=$B$3,$B$1,ROUND(B24*(1+$B$2),0)))
B26	=IF(OR(A26<$B$3,A26>$B$4),0,IF(A26=$B$3,$B$1,ROUND(B25*(1+$B$2),0)))
B27	=IF(OR(A27<$B$3,A27>$B$4),0,IF(A27=$B$3,$B$1,ROUND(B26*(1+$B$2),0)))
B28	=IF(OR(A28<$B$3,A28>$B$4),0,IF(A28=$B$3,$B$1,ROUND(B27*(1+$B$2),0)))
B29	=IF(OR(A29<$B$3,A29>$B$4),0,IF(A29=$B$3,$B$1,ROUND(B28*(1+$B$2),0)))
B30	=IF(OR(A30<$B$3,A30>$B$4),0,IF(A30=$B$3,$B$1,ROUND(B29*(1+$B$2),0)))

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

 

[CDICKENS]

Assuming that age 46 was in A46 and Salary is in B 46, try this in B2: =if(a2>45,if(a2<66,B2*103%,""),a2) and fill down.

The last a2 above assumes no raise before age 46.


Chuck

 

[ccleland]

I will try thank you. Although I am going to have to -1 from the BEG-AGE so I can get to 46....may work I'll give it a go.

 

[ccleland]

I am going to try this right now...thank you...properly.

 

[ccleland]

Let me see if I can "properly explain what you did.

=IF(OR(A6<$B$3,A6>$B$4),0,IF(A6=$B$3,$B$1,ROUND(B5*(1+$B$2),0)))

In this case: Using the BEG-AGE at 30 and the END-AGE at 45....

1) You checked to so see if the AGE column number is < 30 or > 45 if it is then put a 0
2) OR if it's = to AGE 30 then put in the BEG SALARY of $60,000 ELSE
3) ROUND whatever is in B5 (which is nothing) and multiply that (1+the Increase rate%)
4) The trick is having a dead column ahead of the salary and then making sure you do NOT hit F4 to lock the salary going down.

I was using AND and trying to create a situation where it was checking the age column "between" the 2 AGES, I'm not sure of the significance of the ROUND here. Are you trying to cut off the iterations error that I kept getting with #value, etc? I bet that was it....very clever for a limey.

 

[Jeff Meyers]

Let me see if I can "properly explain what you did.

=IF(OR(A6<$B$3,A6>$B$4),0,IF(A6=$B$3,$B$1,ROUND(B5*(1+$B$2),0)))

In this case: Using the BEG-AGE at 30 and the END-AGE at 45....

1) You checked to so see if the AGE column number is < 30 or > 45 if it is then put a 0
2) OR if it's = to AGE 30 then put in the BEG SALARY of $60,000 ELSE
3) ROUND whatever is in B5 (which is nothing) and multiply that (1+the Increase rate%)
4) The trick is having a dead column ahead of the salary and then making sure you do NOT hit F4 to lock the salary going down.

I was using AND and trying to create a situation where it was checking the age column "between" the 2 AGES, I'm not sure of the significance of the ROUND here. Are you trying to cut off the iterations error that I kept getting with #value, etc? I bet that was it....very clever for a limey.

Your explanation for the formula is mostly correct:

1) Correct - it first checks for an age outside of the specified range, and if so then it should be 0
2) Correct - it then checks for the beginning age, and if it is equal to the beginning age then the salary amount should be the beginning salary amount

At this point the only ages remaining (after the first 2 tests) are ages between the beginning age and the ending age (inclusive). Therefore use the salary amount from the preceding year and increase it by the specified rate increase (3% in this example)

3) The ROUND function actually rounds the result of the entire formula B5*(1+$B$2) to zero decimal places. The ROUND function is not necessary - I used it out of habit. You could eliminate it without any issues.

4) I'm not sure what you mean by "The trick is having a dead column ahead of the salary". The second half of your explanation is correct - you want the formula to use relative cell references for the ages (as opposed to absolute references) when you copy it down.

I didn't go with the approach to test the age column to see if it was between the 2 ages as described above. Each of the ages in your data will fall into one of 3 categories:

1. the age is outside of the working / earning ages (tested with first IF function)
2. the age equals the initial working / earning age (tested with second IF function)
3. the age is one of the working / earning ages (they are the only ones remaining after the first 2 tests)

I hope that this is helpful in clarifying it (at least somewhat).

 

[ccleland]

The reason I said the "dead Column or cell" is that you ROUND(B5*(1+$B$2),0))) u are testing the cell above B6...this will not work if you post a value in B6 and go from there... you have to test the cell above 1st, as you have done....unless the wine has finally done me in for the evening.....could be.