Calculate average pay from highest five consecutive years
 

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

Calculate average pay from highest five consecutive years
 

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.
-----Original Message-----
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
.

Calculate average pay from highest five consecutive years
 

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

=SUM(INDIRECT("c"&MATCH(a2,$A$1:$A$200,0)&":c"&MAT CH(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$2 00,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

--
Don Guillett
SalesAid Software

"Chris" wrote in message
om...
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

Calculate average pay from highest five consecutive years
 

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.

Calculate average pay from highest five consecutive years
 

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

--
Don Guillett
SalesAid Software

"Chris" wrote in message
om...
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.