Highest 3 in 10 Consecutive Numbers

[Dan]

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

 

[Mike H]

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(B22),AVERAGE(C2:E2),AVERAGE(D2:F2),AVERAGE(F2:H2),AVERAGE(G2:I2),AVERAGE(H2:J2))

Mike

 

[Mike H]

i did of course mean

=MAX(AVERAGE(B22),AVERAGE(C2:E2),AVERAGE(D2:F2),AVERAGE(E2:G2),AVERAGE(F2:H2),AVERAGE(G2:I2),AVERAGE(H2:J2))

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Lars-Åke Aspelin]

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

 

[Mike H]

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(B22)
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

 

[Rick Rothstein \(MVP - VB\)]

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\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Coderre]

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\)]

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]

Rick
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)) returns 7 (huh? wrong!)

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

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

Best Regards,

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

 

[Rick Rothstein \(MVP - VB\)]

See inline...

there are gaps in my knowledge base... <<

Don't feel like the Lone Ranger. We're ALL self-taught.

Yeah, but I'm guessing you and the other regulars here have been self-taught

It's exposure to great resources, like this group, that helps us
expand our skill set.

No question about that... I have learned a lot over this past year of my
volunteering in this newsgroup by just reading the various posted answers
here.

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)) returns 7 (huh? wrong!)

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

That second formula really is a "huh", isn't it? If you highlight
COLUMN(A) and press F9, it shows the expansion as {1,2,3,4} as one would
expect. If you highlight {2,4,6,5}+COLUMN(A) which is the argument to the
MAX function, and press F9, it shows the expansion as {3,6,9,9}, again, as
would be expected. But then, instead of MAX choosing one of the values from
{3,6,9,9}, it goes and makes up a value of 7?!!? While the F9 expansions
show the array values correctly, the MAX function appears to be ignoring the
array evaluations of COLUMN(A) and simply using the first value in the
array list, 1, and adding it to the first array's values before selecting
the maximum value of 7 (6+1). If you change your formula to use COLUMN(B:E)
instead, the formula will return 8 which appears to be the 6 from the first
array grouping plus the 2 (B's equivalent as a column number) from the array
expansion of COLUMN(B:E). The mechanism at work here seems to be akin to the
one at work in a formula like this...

=FIND({"a","b"},<<SomeText>>)

When <<SomeText>> is the word "about", the formula works and returns 1; but,
when <<SomeText>> is the word "bout", the formula errors out... and it does
that whether you array-enter it or not. Some functions seem able to tolerate
an array without actually being able to use anything past its first
argument... this appears to be the case with your second formula.

Rick

 

[Harlan Grove]

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

....

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

 

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[Ron Coderre]

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

RE: 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

 

[Lars-Åke Aspelin]

...

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.

 

[Harlan Grove]

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.

An opportunity for me to shorten the formula too.

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