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.

 

[ccleland]

maybe test is the wrong word...you are calcualting from the cell above.

 

[learnmore]

or just try this

[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]40
[/TD]
[TD]60000
[/TD]
[/TR]
[TR]
[TD]41
[/TD]
[TD]=B2*1.03
[/TD]
[/TR]
[TR]
[TD]42
[/TD]
[TD]=B3*1.03
[/TD]
[/TR]
[TR]
[TD]43
[/TD]
[TD]=B4*1.03
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ccleland]

utlizing the previous code get me there in the 1st place....right?

 

[learnmore]

utlizing the previous code get me there in the 1st place....right?

what do you mean "get me there in the 1st place"?

 

[ccleland]

meaning you have to you the first part of your solution to place the 60,000 at the right age....then I will try the rest.

 

[ccleland]

sorry, you're a different person. Actually you need to go to the beginning of the THREAD and get the whole skinny. Then this should make sense.

 

[Jeff Meyers]

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.

You are correct - I wanted the formula to be consistent down the entire column. To do so there must be a blank / empty / 0 cell preceding your data table. The other alternative is to modify the formula for Age 25 (i.e. Cell B6 in my example) as follows:

=IF(OR(A6 < $B$3,A6 > $B$4),0,IF(A6=$B$3,$B$1,0))

As the only two options for Age 25 are: it's outside the age range or it's the initial earning year.

 

[ccleland]

You need to put this in a sheet and test it. It does not work for me. Please keep in mind that I am using contstants $B$2 , etc for all my static data. At least this is what I'm getting....I would love for this to work. I'm still playing around with it.
[TABLE="width: 800"]
<tbody>[TR]
[TD]Salary[/TD]
[TD]$60,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Inc %[/TD]
[TD]3%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Begin Age[/TD]
[TD]46[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]End Age[/TD]
[TD]51[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Age[/TD]
[TD]Salary[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]=IF(A6>$B$2,IF(A6<$B$4,B1*(1+$B$2),""),A6)[/TD]
[TD]This will produce the value of 44[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jeff Meyers]

You need to fix your formulas. Here it is with your data working correctly.

Note that this is the same formula as I posted earlier with two exceptions:

1. The ROUND function has been removed
2. The formula in Cell B5 is not identical to the rest of the formulas

Sheet6[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]Inc %[/TD]
[TD="align: right"]3.00%[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Begin Age[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]End Age[/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="align: right"]44[/TD]
[TD="align: right"] 0 [/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD="align: right"]45[/TD]
[TD="align: right"] 0 [/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD="align: right"]46[/TD]
[TD="align: right"] 60,000 [/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="align: right"]47[/TD]
[TD="align: right"] 61,800 [/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD="align: right"]48[/TD]
[TD="align: right"] 63,654 [/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD="align: right"]49[/TD]
[TD="align: right"] 65,564 [/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD="align: right"]50[/TD]
[TD="align: right"] 67,531 [/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD="align: right"]51[/TD]
[TD="align: right"] 69,556 [/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD="align: right"]52[/TD]
[TD="align: right"] 0 [/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD]--------------[/TD]
[TD]--------------[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
B5	=IF(OR(A5<$B$3,A5>$B$4),0,IF(A5=$B$3,$B$1,0))
B6	=IF(OR(A6<$B$3,A6>$B$4),0,IF(A6=$B$3,$B$1,B5*(1+$B$2)))
B7	=IF(OR(A7<$B$3,A7>$B$4),0,IF(A7=$B$3,$B$1,B6*(1+$B$2)))
B8	=IF(OR(A8<$B$3,A8>$B$4),0,IF(A8=$B$3,$B$1,B7*(1+$B$2)))
B9	=IF(OR(A9<$B$3,A9>$B$4),0,IF(A9=$B$3,$B$1,B8*(1+$B$2)))
B10	=IF(OR(A10<$B$3,A10>$B$4),0,IF(A10=$B$3,$B$1,B9*(1+$B$2)))
B11	=IF(OR(A11<$B$3,A11>$B$4),0,IF(A11=$B$3,$B$1,B10*(1+$B$2)))
B12	=IF(OR(A12<$B$3,A12>$B$4),0,IF(A12=$B$3,$B$1,B11*(1+$B$2)))
B13	=IF(OR(A13<$B$3,A13>$B$4),0,IF(A13=$B$3,$B$1,B12*(1+$B$2)))

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

</thead><tbody>
</tbody>

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

 

[ccleland]

Roger that...the post I submitted was a response from "learnmore" earlier up the thread....I agree, you got it right.