I have a workbook which shows salaries for a number of employees over the
last 10 years. I need to find the average of the three highest consecutive
salaries over that period. The columns are ordered by date so it is not
possible to re-order each row.

2005 2006 2007 2008 Ave
Emp 1 10,000 11,000 12,000 13,000 12,000
Emp 2 13,000 13,000 14,000 10,000 13,333
Emp 3 13,000 12,000 11,000 10,000 12,000

Any help would be grately appreciated!
Dan

Dan,

It's a bit messy but you could do this. Assume your data layout are
2000 2001 2002 2003 2004
Emp 10,000 11,000 12,000 13,000 11,500
Emp 13000 13000 14000 10000 12,500
Emp 13000 12000 11000 10000 11,500

Ive left out 2005 - 2008 because I didn't want it to wrap but the above
table continues to column J for 2008.
Enter this formula in K2 and copy down
=MAX(AVERAGE(B2:D2),AVERAGE(C2:E2),AVERAGE(D2:F2), AVERAGE(F2:H2),AVERAGE(G2:I2),AVERAGE(H2:J2))

Mike

"Dan" wrote:

I have a workbook which shows salaries for a number of employees over the
last 10 years. I need to find the average of the three highest consecutive
salaries over that period. The columns are ordered by date so it is not
possible to re-order each row.

2005 2006 2007 2008 Ave
Emp 1 10,000 11,000 12,000 13,000 12,000
Emp 2 13,000 13,000 14,000 10,000 13,333
Emp 3 13,000 12,000 11,000 10,000 12,000

Any help would be grately appreciated!
Dan

i did of course mean

=MAX(AVERAGE(B2:D2),AVERAGE(C2:E2),AVERAGE(D2:F2), AVERAGE(E2:G2),AVERAGE(F2:H2),AVERAGE(G2:I2),AVERA GE(H2:J2))

"Mike H" wrote:

Dan,

It's a bit messy but you could do this. Assume your data layout are
2000 2001 2002 2003 2004
Emp 10,000 11,000 12,000 13,000 11,500
Emp 13000 13000 14000 10000 12,500
Emp 13000 12000 11000 10000 11,500

Ive left out 2005 - 2008 because I didn't want it to wrap but the above
table continues to column J for 2008.
Enter this formula in K2 and copy down
=MAX(AVERAGE(B2:D2),AVERAGE(C2:E2),AVERAGE(D2:F2), AVERAGE(F2:H2),AVERAGE(G2:I2),AVERAGE(H2:J2))

Mike

"Dan" wrote:

I have a workbook which shows salaries for a number of employees over the
last 10 years. I need to find the average of the three highest consecutive
salaries over that period. The columns are ordered by date so it is not
possible to re-order each row.

2005 2006 2007 2008 Ave
Emp 1 10,000 11,000 12,000 13,000 12,000
Emp 2 13,000 13,000 14,000 10,000 13,333
Emp 3 13,000 12,000 11,000 10,000 12,000

Any help would be grately appreciated!
Dan

Assuming your 10-year span is from Column B to Column K and that the
employee data starts in Row 2, put this formula...

=(LARGE(B2:K2,1)+LARGE(B2:K2,2)+LARGE(B2:K2,3))/3

in L2 and copy down.

Rick


"Dan" wrote in message
...
I have a workbook which shows salaries for a number of employees over the
last 10 years. I need to find the average of the three highest
consecutive
salaries over that period. The columns are ordered by date so it is not
possible to re-order each row.

2005 2006 2007 2008 Ave
Emp 1 10,000 11,000 12,000 13,000 12,000
Emp 2 13,000 13,000 14,000 10,000 13,333
Emp 3 13,000 12,000 11,000 10,000 12,000

Any help would be grately appreciated!
Dan

Rick, your formula does not guarantee the three terms to be taken from
three consecutive cells/years.

13,000 12,000 11,000 12,000
should give an average of 12,000 (not 12,333) I guess.

Lars-Åke


On Wed, 26 Mar 2008 05:34:30 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

Assuming your 10-year span is from Column B to Column K and that the
employee data starts in Row 2, put this formula...

=(LARGE(B2:K2,1)+LARGE(B2:K2,2)+LARGE(B2:K2,3))/3

in L2 and copy down.

Rick


"Dan" wrote in message
...
I have a workbook which shows salaries for a number of employees over the
last 10 years. I need to find the average of the three highest
consecutive
salaries over that period. The columns are ordered by date so it is not
possible to re-order each row.

2005 2006 2007 2008 Ave
Emp 1 10,000 11,000 12,000 13,000 12,000
Emp 2 13,000 13,000 14,000 10,000 13,333
Emp 3 13,000 12,000 11,000 10,000 12,000

Any help would be grately appreciated!
Dan

You obvoiusly didn't like my first answer so how about this one. Same data
layout as before once gain stretcehed out for 10 years

1999 2000 2001 2002 2003 2004
Emp 1000 10,000 11,000 12,000 13,000 99,999
Emp 1026 13000 13000 14000 10000 10000
Emp 1058 13000 12000 11000 10000 10000

Somewhere out of the way enter
=average(B2:D2)
Drag right 7 cells and you have the average for each 3 consecutive years
Then the formula
=Max(the above range)

Give the maximum

Mike


"Lars-Ã…ke Aspelin" wrote:

Rick, your formula does not guarantee the three terms to be taken from
three consecutive cells/years.

13,000 12,000 11,000 12,000
should give an average of 12,000 (not 12,333) I guess.

Lars-Ã…ke


On Wed, 26 Mar 2008 05:34:30 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

Assuming your 10-year span is from Column B to Column K and that the
employee data starts in Row 2, put this formula...

=(LARGE(B2:K2,1)+LARGE(B2:K2,2)+LARGE(B2:K2,3))/3

in L2 and copy down.

Rick


"Dan" wrote in message
...
I have a workbook which shows salaries for a number of employees over the
last 10 years. I need to find the average of the three highest
consecutive
salaries over that period. The columns are ordered by date so it is not
possible to re-order each row.

2005 2006 2007 2008 Ave
Emp 1 10,000 11,000 12,000 13,000 12,000
Emp 2 13,000 13,000 14,000 10,000 13,333
Emp 3 13,000 12,000 11,000 10,000 12,000

Any help would be grately appreciated!
Dan

I missed that the "3 consecutive years" part (I went with what the Subject
said, not what the body of the message said).

Here is the revised formula for this condition that I just posted against my
previous message...

=SUMPRODUCT(MAX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2: I2,0,2))/3)

I am pretty sure this does what the OP asked. And, yes, I know this could
have been posted as an array-entered formula this way...

=MAX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2:I2,0,2))/3

but it seems many people forget about the array-entered formulas'
Ctrl+Shift+Enter commitment keystroke when doing future modifications to
their formulas, so I try to stay away from offering them when possible.

Rick


"Lars-Åke Aspelin" wrote in message
...
Rick, your formula does not guarantee the three terms to be taken from
three consecutive cells/years.

13,000 12,000 11,000 12,000
should give an average of 12,000 (not 12,333) I guess.

Lars-Åke


On Wed, 26 Mar 2008 05:34:30 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

Assuming your 10-year span is from Column B to Column K and that the
employee data starts in Row 2, put this formula...

=(LARGE(B2:K2,1)+LARGE(B2:K2,2)+LARGE(B2:K2,3) )/3

in L2 and copy down.

Rick


"Dan" wrote in message
...
I have a workbook which shows salaries for a number of employees over the
last 10 years. I need to find the average of the three highest
consecutive
salaries over that period. The columns are ordered by date so it is not
possible to re-order each row.

2005 2006 2007 2008 Ave
Emp 1 10,000 11,000 12,000 13,000 12,000
Emp 2 13,000 13,000 14,000 10,000 13,333
Emp 3 13,000 12,000 11,000 10,000 12,000

Any help would be grately appreciated!
Dan

Sorry... I forgot to add my thank you for pointing out the mistake in my
first formula... I really do appreciate your having done that as we all want
to (eventually<g) "get it right" for those asking their questions here; so,
again, thank you.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I missed that the "3 consecutive years" part (I went with what the Subject
said, not what the body of the message said).

Here is the revised formula for this condition that I just posted against
my previous message...

=SUMPRODUCT(MAX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2: I2,0,2))/3)

I am pretty sure this does what the OP asked. And, yes, I know this could
have been posted as an array-entered formula this way...

=MAX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2:I2,0,2))/3

but it seems many people forget about the array-entered formulas'
Ctrl+Shift+Enter commitment keystroke when doing future modifications to
their formulas, so I try to stay away from offering them when possible.

Rick


"Lars-Åke Aspelin" wrote in message
...
Rick, your formula does not guarantee the three terms to be taken from
three consecutive cells/years.

13,000 12,000 11,000 12,000
should give an average of 12,000 (not 12,333) I guess.

Lars-Åke


On Wed, 26 Mar 2008 05:34:30 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

Assuming your 10-year span is from Column B to Column K and that the
employee data starts in Row 2, put this formula...

=(LARGE(B2:K2,1)+LARGE(B2:K2,2)+LARGE(B2:K2,3 ))/3

in L2 and copy down.

Rick


"Dan" wrote in message
...
I have a workbook which shows salaries for a number of employees over
the
last 10 years. I need to find the average of the three highest
consecutive
salaries over that period. The columns are ordered by date so it is
not
possible to re-order each row.

2005 2006 2007 2008 Ave
Emp 1 10,000 11,000 12,000 13,000 12,000
Emp 2 13,000 13,000 14,000 10,000 13,333
Emp 3 13,000 12,000 11,000 10,000 12,000

Any help would be grately appreciated!
Dan

As has been pointed out to me, my formula does not guarantee that the 3
selected years are consecutive. Here is a different formula which I'm pretty
sure does do that...

=SUMPRODUCT(MAX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2: I2,0,2))/3)

Put this formula in L2 (using the same assumptions as to layout as I
previously posted) and copy it down.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Assuming your 10-year span is from Column B to Column K and that the
employee data starts in Row 2, put this formula...

=(LARGE(B2:K2,1)+LARGE(B2:K2,2)+LARGE(B2:K2,3))/3

in L2 and copy down.

Rick


"Dan" wrote in message
...
I have a workbook which shows salaries for a number of employees over the
last 10 years. I need to find the average of the three highest
consecutive
salaries over that period. The columns are ordered by date so it is not
possible to re-order each row.

2005 2006 2007 2008 Ave
Emp 1 10,000 11,000 12,000 13,000 12,000
Emp 2 13,000 13,000 14,000 10,000 13,333
Emp 3 13,000 12,000 11,000 10,000 12,000

Any help would be grately appreciated!
Dan

Hi, Rick

Since the SUMPRODUCT function is only being used to
convert the returned array into "usable" values, it
can be replaced with an INDEX function:

L2: =MAX(INDEX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2:I2,0, 2),0))/3

--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"Rick Rothstein (MVP - VB)" wrote in
message ...
As has been pointed out to me, my formula does not guarantee that the 3
selected years are consecutive. Here is a different formula which I'm
pretty sure does do that...

=SUMPRODUCT(MAX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2: I2,0,2))/3)

Put this formula in L2 (using the same assumptions as to layout as I
previously posted) and copy it down.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Assuming your 10-year span is from Column B to Column K and that the
employee data starts in Row 2, put this formula...

=(LARGE(B2:K2,1)+LARGE(B2:K2,2)+LARGE(B2:K2,3))/3

in L2 and copy down.

Rick


"Dan" wrote in message
...
I have a workbook which shows salaries for a number of employees over the
last 10 years. I need to find the average of the three highest
consecutive
salaries over that period. The columns are ordered by date so it is not
possible to re-order each row.

2005 2006 2007 2008 Ave
Emp 1 10,000 11,000 12,000 13,000 12,000
Emp 2 13,000 13,000 14,000 10,000 13,333
Emp 3 13,000 12,000 11,000 10,000 12,000

Any help would be grately appreciated!
Dan

First off, my original formula was this array-entered one...

=MAX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2:I2,0,2))/3

I slapped the SUMPRODUCT function around it so the OP wouldn't have to
remember to commit it with Ctrl+Shift+Enter in future editing sessions.

However, being self-taught in all of this, there are gaps in my knowledge
base... the way you are using INDEX seems to be one of them. I'm not exactly
sure why the INDEX formulation you used works. I'm assuming it knows it is
working with an array simply because the summation cannot be interpreted as
a reference. I'm guessing the 0 argument is saying look at this in "entire"
columns, of which there are only one at a time; and I'm guessing the fact
that it interpreted its argument as an array means it will march through
those "entire" columns one at a time (first B2 then C2 then D2, etc.). I
guess the thing that throws me a little is that its argument is a summation
of 3 terms and that it knows how to handle that (I've always assumed the
first argument to the INDEX function had to be a "single" simple entity).
Very interesting stuff... you have given me something new to digest and
internalize. Thanks.

Rick


"Ron Coderre" wrote in message
...
Hi, Rick

Since the SUMPRODUCT function is only being used to
convert the returned array into "usable" values, it
can be replaced with an INDEX function:

L2: =MAX(INDEX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2:I2,0, 2),0))/3

--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"Rick Rothstein (MVP - VB)" wrote in
message ...
As has been pointed out to me, my formula does not guarantee that the 3
selected years are consecutive. Here is a different formula which I'm
pretty sure does do that...

=SUMPRODUCT(MAX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2: I2,0,2))/3)

Put this formula in L2 (using the same assumptions as to layout as I
previously posted) and copy it down.

Rick


"Rick Rothstein (MVP - VB)" wrote
in
message ...
Assuming your 10-year span is from Column B to Column K and that the
employee data starts in Row 2, put this formula...

=(LARGE(B2:K2,1)+LARGE(B2:K2,2)+LARGE(B2:K2,3))/3

in L2 and copy down.

Rick


"Dan" wrote in message
...
I have a workbook which shows salaries for a number of employees over
the
last 10 years. I need to find the average of the three highest
consecutive
salaries over that period. The columns are ordered by date so it is
not
possible to re-order each row.

2005 2006 2007 2008 Ave
Emp 1 10,000 11,000 12,000 13,000 12,000
Emp 2 13,000 13,000 14,000 10,000 13,333
Emp 3 13,000 12,000 11,000 10,000 12,000

Any help would be grately appreciated!
Dan

Rick

However, being self-taught in all of this,
there are gaps in my knowledge base... <<

Don't feel like the Lone Ranger. We're ALL self-taught. It's
exposure to great resources, like this group, that helps us
expand our skill set.

Regarding the use of the INDEX function....When the 2nd argument
is zero (0), it evaluates the first arguement and returns the
array into a series of values that worksheet functions can use
(most of the time).

Quick example:
=MAX({2,4,6,5}+{1,2,3,4}) returns 9

=MAX({2,4,6,5}+COLUMN(A:D)) returns 7 (huh? wrong!)

=MAX(INDEX({2,4,6,5}+COLUMN(A:D),0)) returns 9 (correct)

--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Rick Rothstein (MVP - VB)" wrote in
message ...
First off, my original formula was this array-entered one...

=MAX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2:I2,0,2))/3

I slapped the SUMPRODUCT function around it so the OP wouldn't have to
remember to commit it with Ctrl+Shift+Enter in future editing sessions.

However, being self-taught in all of this, there are gaps in my knowledge
base... the way you are using INDEX seems to be one of them. I'm not
exactly sure why the INDEX formulation you used works. I'm assuming it
knows it is working with an array simply because the summation cannot be
interpreted as a reference. I'm guessing the 0 argument is saying look at
this in "entire" columns, of which there are only one at a time; and I'm
guessing the fact that it interpreted its argument as an array means it
will march through those "entire" columns one at a time (first B2 then C2
then D2, etc.). I guess the thing that throws me a little is that its
argument is a summation of 3 terms and that it knows how to handle that
(I've always assumed the first argument to the INDEX function had to be a
"single" simple entity). Very interesting stuff... you have given me
something new to digest and internalize. Thanks.

Rick


"Ron Coderre" wrote in message
...
Hi, Rick

Since the SUMPRODUCT function is only being used to
convert the returned array into "usable" values, it
can be replaced with an INDEX function:

L2: =MAX(INDEX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2:I2,0, 2),0))/3

--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"Rick Rothstein (MVP - VB)" wrote
in
message ...
As has been pointed out to me, my formula does not guarantee that the 3
selected years are consecutive. Here is a different formula which I'm
pretty sure does do that...

=SUMPRODUCT(MAX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2: I2,0,2))/3)

Put this formula in L2 (using the same assumptions as to layout as I
previously posted) and copy it down.

Rick


"Rick Rothstein (MVP - VB)" wrote
in
message ...
Assuming your 10-year span is from Column B to Column K and that the
employee data starts in Row 2, put this formula...

=(LARGE(B2:K2,1)+LARGE(B2:K2,2)+LARGE(B2:K2,3))/3

in L2 and copy down.

Rick


"Dan" wrote in message
...
I have a workbook which shows salaries for a number of employees over
the
last 10 years. I need to find the average of the three highest
consecutive
salaries over that period. The columns are ordered by date so it is
not
possible to re-order each row.

2005 2006 2007 2008 Ave
Emp 1 10,000 11,000 12,000 13,000 12,000
Emp 2 13,000 13,000 14,000 10,000 13,333
Emp 3 13,000 12,000 11,000 10,000 12,000

Any help would be grately appreciated!
Dan

"Ron Coderre" wrote...
Since the SUMPRODUCT function is only being used to
convert the returned array into "usable" values, it
can be replaced with an INDEX function:

L2: =MAX(INDEX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2:I2,0, 2),0))/3
....
"Rick Rothstein (MVP - VB)" wrote...
....
=SUMPRODUCT(MAX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B 2:I2,0,2))/3)
....

Or avoid using the volatile OFFSET call completely while also
simplifying:

=MAX(B2:I2+C2:J2+D2:K2)/3

which needs to be entered as an array formula, or

=MAX(INDEX(B2:I2+C2:J2+D2:K2,0))/3

which avoids array entry.

Going the opposite direction, to generalize this to the average of the
N largest consecutive values, you'd need to use something like the
following array formula

=MAX(MMULT(TRANSPOSE(COLUMN(B2:K2)-MIN(COLUMN(B2:K2))
<=COLUMNS(B2:K2)-$N$1)*(ABS(COLUMN(B2:K2)-TRANSPOSE(COLUMN(B2:K2))
-($N$1-1)/2)<$N$1/2),TRANSPOSE(B2:K2)))/$N$1

I just keep learning more and more. Thanks Harlan, much appreciated!

Rick


"Harlan Grove" wrote in message
...
"Ron Coderre" wrote...
Since the SUMPRODUCT function is only being used to
convert the returned array into "usable" values, it
can be replaced with an INDEX function:

L2: =MAX(INDEX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2:I2,0, 2),0))/3
...
"Rick Rothstein (MVP - VB)" wrote...
...
=SUMPRODUCT(MAX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET( B2:I2,0,2))/3)
...

Or avoid using the volatile OFFSET call completely while also
simplifying:

=MAX(B2:I2+C2:J2+D2:K2)/3

which needs to be entered as an array formula, or

=MAX(INDEX(B2:I2+C2:J2+D2:K2,0))/3

which avoids array entry.

Going the opposite direction, to generalize this to the average of the
N largest consecutive values, you'd need to use something like the
following array formula

=MAX(MMULT(TRANSPOSE(COLUMN(B2:K2)-MIN(COLUMN(B2:K2))
<=COLUMNS(B2:K2)-$N$1)*(ABS(COLUMN(B2:K2)-TRANSPOSE(COLUMN(B2:K2))
-($N$1-1)/2)<$N$1/2),TRANSPOSE(B2:K2)))/$N$1

Good points, Harlan.
Since there are only 3 consecutive values to average,
there's no real gain from using the OFFSET functions.

Array Formulas:
I like them, but they're too fragile
for the relatively inexperienced users I work with.

Interesting MMULT formula. I'll have to study it a bit, though.

Best Regards,

Ron



"Harlan Grove" wrote in message
...
"Ron Coderre" wrote...
Since the SUMPRODUCT function is only being used to
convert the returned array into "usable" values, it
can be replaced with an INDEX function:

L2: =MAX(INDEX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2:I2,0, 2),0))/3
...
"Rick Rothstein (MVP - VB)" wrote...
...
=SUMPRODUCT(MAX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET( B2:I2,0,2))/3)
...

Or avoid using the volatile OFFSET call completely while also
simplifying:

=MAX(B2:I2+C2:J2+D2:K2)/3

which needs to be entered as an array formula, or

=MAX(INDEX(B2:I2+C2:J2+D2:K2,0))/3

which avoids array entry.

Going the opposite direction, to generalize this to the average of the
N largest consecutive values, you'd need to use something like the
following array formula

=MAX(MMULT(TRANSPOSE(COLUMN(B2:K2)-MIN(COLUMN(B2:K2))
<=COLUMNS(B2:K2)-$N$1)*(ABS(COLUMN(B2:K2)-TRANSPOSE(COLUMN(B2:K2))
-($N$1-1)/2)<$N$1/2),TRANSPOSE(B2:K2)))/$N$1

On Wed, 26 Mar 2008 12:32:54 -0700 (PDT), Harlan Grove
wrote:

"Ron Coderre" wrote...
Since the SUMPRODUCT function is only being used to
convert the returned array into "usable" values, it
can be replaced with an INDEX function:

L2: =MAX(INDEX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET(B2:I2,0, 2),0))/3
...
"Rick Rothstein (MVP - VB)" wrote...
...
=SUMPRODUCT(MAX(B2:I2+OFFSET(B2:I2,0,1)+OFFSET( B2:I2,0,2))/3)
...

Or avoid using the volatile OFFSET call completely while also
simplifying:

=MAX(B2:I2+C2:J2+D2:K2)/3

which needs to be entered as an array formula, or

=MAX(INDEX(B2:I2+C2:J2+D2:K2,0))/3

which avoids array entry.

Going the opposite direction, to generalize this to the average of the
N largest consecutive values, you'd need to use something like the
following array formula

=MAX(MMULT(TRANSPOSE(COLUMN(B2:K2)-MIN(COLUMN(B2:K2))
<=COLUMNS(B2:K2)-$N$1)*(ABS(COLUMN(B2:K2)-TRANSPOSE(COLUMN(B2:K2))
-($N$1-1)/2)<$N$1/2),TRANSPOSE(B2:K2)))/$N$1


This was an elegant solution indeed, but it is limited to results that
are non negative. That is probably OK for the original poster as
salaries should be non negative numbers, but it does not work in the
general case where the input, the 10 numbers, could be negative as
well as positive.