Index/ Match enhancement needed

[Guest]

have 4000 rows of employees and 81 columns of months - Jan 00 to Sep 06
range - in Columns D:CE. In the columns are records of changes in salary,
these are random as employees start in different month and receive increases
in different months. I need to calculate the percentage increase for each
entry. If there is an increase in the last column (Sep 06) I can use this
formula successfully:
=(+CF3-INDEX(D3:CE3,1,(MATCH(CF3,D3:CE3,1))))/INDEX(D3:CE3,1,(MATCH(CF3,D3:CE3,1))).

I need help in making this a dynamic calculation to look back along the row,
find the latest salary store that and then find the previous salary to
calculate the increase. The next formula must take the previous salary and
make it "latest" to repeat the process back down the row.

The results can be in successive columns after Column CE or I can insert a
new column after each month.

 

[Dave Peterson]

I'd use a couple of helper cells so that I wouldn't have to do the same
calculation in lots of formulas.

Put this in CG3 (say):
=match(max(d3:ce3),d3:ce3,0)
to show the column that has the first largest salary.

Then you can use:
=index(d3:c3,CG3)
to see the first largest salary.

And
=if(cg3=1,"No increase in salary",index(d3:c3,cg3-1))
to show the previous column/month salary

(if the largest salary is in the first column of the range (D3 in this row),
then I showed text. Else just subtract one from column tha contained the first
largest salary.)

If you wanted, you could create larger formulas by using the formula in CG3 in
place of any reference to CG3:

=index(d3:c3,match(max(d3:ce3),d3:ce3,0))
to get the max salary.

And so forth....

 

[Guest]

I'd use a couple of helper cells so that I wouldn't have to do the same
calculation in lots of formulas.

Put this in CG3 (say):
=match(max(d3:ce3),d3:ce3,0)
to show the column that has the first largest salary.

Then you can use:
=index(d3:c3,CG3)
to see the first largest salary.

And
=if(cg3=1,"No increase in salary",index(d3:c3,cg3-1))
to show the previous column/month salary

(if the largest salary is in the first column of the range (D3 in this row),
then I showed text. Else just subtract one from column tha contained the first
largest salary.)

If you wanted, you could create larger formulas by using the formula in CG3 in
place of any reference to CG3:

=index(d3:c3,match(max(d3:ce3),d3:ce3,0))
to get the max salary.

And so forth....

 

[Guest]

Thanks Dave

I will try this at the office today and will let you know. Unfortunately, we
have limited web access at work so it will be some time.

Regards

Charles