Highest 3 in 10 Based on Consecutive Cells
 

I have a series of columns which hold salary data for a few hundred employees
which is recorded by date. I need to work out what the average salary is for
the highest three consecutive (i.e. cells immediatly adjacent) years in 10
e.g.

06/04/05 06/04/06 06/04/07
06/04/08 Answer
Employee 1 15,000 9,000 10,000 10,000
11,333
Employee 2 14,000 15,000 16,000 17,000
16,000

I am unable to sort the rows because of the need to keep the salaries in
order and most other formulae only pick the highest out of the list.

This is a pretty exacting requirement and I am not confident this is
possible at all through Excel. Any help would be grately appreciated!

Dan

Highest 3 in 10 Based on Consecutive Cells
 

It is not difficult. Here is an example for just one person. In A1 thru K1:

Boris Badinov 154 141 285 209 388 292 411 143 919 769

In an un-used area, say B2, enter:
=AVERAGE(B1:D1) and copy across thru I2

Finally in A2:
=MAX(B2:I2)

Boris Badinov 154 141 285 209 388 292 411
143 919 769
610.33 193.33 211.67 294.00 296.33 363.67 282.00 491.00 610.33

The trick is we calculate ALL the consecutive three years averages and pick
the hjighest one.



--
Gary''s Student - gsnu200775


"Dan" wrote:

I have a series of columns which hold salary data for a few hundred employees
which is recorded by date. I need to work out what the average salary is for
the highest three consecutive (i.e. cells immediatly adjacent) years in 10
e.g.

06/04/05 06/04/06 06/04/07
06/04/08 Answer
Employee 1 15,000 9,000 10,000 10,000
11,333
Employee 2 14,000 15,000 16,000 17,000
16,000

I am unable to sort the rows because of the need to keep the salaries in
order and most other formulae only pick the highest out of the list.

This is a pretty exacting requirement and I am not confident this is
possible at all through Excel. Any help would be grately appreciated!

Dan

Highest 3 in 10 Based on Consecutive Cells
 

If you have only got those four columns for years, then you can do it
this way, assuming year data is in columns B to E:

=MAX(AVERAGE(B2:D2),AVERAGE(C2:E2))

Put this in F2 (for example) and copy down.

Hope this helps.

Pete


On Mar 25, 7:07*pm, Dan wrote:
I have a series of columns which hold salary data for a few hundred employees
which is recorded by date. *I need to work out what the average salary is for
the highest three consecutive (i.e. cells immediatly adjacent) years in 10
e.g.

* * * * * * * * * * * *06/04/05 * * * * *06/04/06 * * * 06/04/07 * * *
06/04/08 * * * * *Answer
Employee 1 * * * 15,000 * * * * * * 9,000 * * * * * *10,000 * * * * *10,000 *
* * * * * *11,333
Employee 2 * * * 14,000 * * * * * * 15,000 * * * * *16,000 * * * * *17,000 *
* * * * * 16,000

I am unable to sort the rows because of the need to keep the salaries in
order and most other formulae only pick the highest out of the list.

This is a pretty exacting requirement and I am not confident this is
possible at all through Excel. *Any help would be grately appreciated!

Dan