Calculate average pay from highest five consecutive years

[Chris]

I have a workbook with two worksheets

Worksheet #1 has the following:

Employee# Year Amount
12345 2002 40000
12345 2001 26390
12345 2000 33184
12345 1999 38861
12345 1998 25198
12345 1997 32015
98765 2002 67923
<etc etc etc etc>

Worksheet #2 has a cell that I need to put the average of the five
highest years of pay for each employee record.

Any ideas how to write this function?

Thanks in advance.

Chris

 

[Guest]

Perhaps over simple, but can you sort the data on Amount
within Employee value. Then the top 5 (or less ?) would be
at the top.
A new column could identify a change in employee number
(i.e. if not the same as the one above) and average the
next 5 columns of Amount
=if(A5<>A6,(C1+c2+c3+c4+c5)/5,"") in column D

Would need adjusting if the Employee had less that 5 years
data though. Alternatively build it into a macro/VBA
script.

 

[Don Guillett]

Assuming a sort 1st by employee number ascending and then amount
descending,use

=SUM(INDIRECT("c"&MATCH(a2,$A$1:$A$200,0)&":c"&MATCH(a2,$A$1:A$200,0)+4))
assumes that the employee number you want is in a2

if another sheet employee number 12345 in cell a2. Correct to one line
=SUM(INDIRECT("sheet8!c"&MATCH(A2,Sheet8!$A$1:$A$200,0)&":c"&MATCH(A2,Sheet8
!$A$1:$A$200,0)+4))

BTW, you may use this formula to convert formulas that result in more than
one line to the line above

Sub FixLongFormulas() 'goto a remote area of ws & select 1st line
x = ActiveCell.Row
y = ActiveCell.Column
z = ActiveCell.End(xlDown).Row
For Each C In Range(Cells(x, y), Cells(z, y))
Cells(x - 1, y) = Cells(x - 1, y) & C
'mstr = mstr & C
Next
'Cells(x - 1, y) = mstr
End Sub

 

[Chris]

Thanks for the answers so far, but you both are missing a crucial
point, which I stated in the subject of my original post: The average
must be the highest 5 ***CONSECUTIVE*** years, so I can't sort the
list any further beyond employee number.

 

[Don Guillett]

Then sort key 1 should be the employee number ascending and sort key 2
should be the year in descending