Best average for 5 years out of 8
 

I am trying to figure out how to calculate an average of the best five years
out of 8.

My first example is 0, 3, 3, 5, 5, 3, 5, 5 I would expect my average to be
4.6.

Another example is 5, -2, -2, -2, -2, 0, 0, 0 I would expect my average to
be -0.6

Best average for 5 years out of 8
 

=AVERAGE(LARGE(A1:H1,{1,2,3,4,5}))
This is an array formula so commit it with CTRL+SHIFT+ENTER not just ENTER
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Wendy" wrote in message
...
I am trying to figure out how to calculate an average of the best five
years
out of 8.

My first example is 0, 3, 3, 5, 5, 3, 5, 5 I would expect my average to
be
4.6.

Another example is 5, -2, -2, -2, -2, 0, 0, 0 I would expect my average
to
be -0.6

Best average for 5 years out of 8
 

PERFECT!! Thank you so much!

"Bernard Liengme" wrote:

=AVERAGE(LARGE(A1:H1,{1,2,3,4,5}))
This is an array formula so commit it with CTRL+SHIFT+ENTER not just ENTER
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Wendy" wrote in message
...
I am trying to figure out how to calculate an average of the best five
years
out of 8.

My first example is 0, 3, 3, 5, 5, 3, 5, 5 I would expect my average to
be
4.6.

Another example is 5, -2, -2, -2, -2, 0, 0, 0 I would expect my average
to
be -0.6

Best average for 5 years out of 8
 

You are very welcome!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Wendy" wrote in message
...
PERFECT!! Thank you so much!

"Bernard Liengme" wrote:

=AVERAGE(LARGE(A1:H1,{1,2,3,4,5}))
This is an array formula so commit it with CTRL+SHIFT+ENTER not just
ENTER
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Wendy" wrote in message
...
I am trying to figure out how to calculate an average of the best five
years
out of 8.

My first example is 0, 3, 3, 5, 5, 3, 5, 5 I would expect my average
to
be
4.6.

Another example is 5, -2, -2, -2, -2, 0, 0, 0 I would expect my
average
to
be -0.6

Best average for 5 years out of 8
 

Hi,

Two points, the average of the second set is not -0.6 but +0.6?

For top 5 the {1,2,3,4,5} is fine but if one wanted the top 50 this would be
a pain, so another solution is the array formula

=AVERAGE(LARGE(A11:H11,ROW(1:5)))

To make it an array you press Shift+Ctrl+Enter instead of Enter to enter it.
Notice that to average the top 50 this formula would be virtually the same:

=AVERAGE(LARGE(A11:DD11,ROW(1:50)))

while the {1,2,3,4,5,6,7,8,9,10,11,..... would get rather tedious.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Wendy" wrote:

PERFECT!! Thank you so much!

"Bernard Liengme" wrote:

=AVERAGE(LARGE(A1:H1,{1,2,3,4,5}))
This is an array formula so commit it with CTRL+SHIFT+ENTER not just ENTER
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Wendy" wrote in message
...
I am trying to figure out how to calculate an average of the best five
years
out of 8.

My first example is 0, 3, 3, 5, 5, 3, 5, 5 I would expect my average to
be
4.6.

Another example is 5, -2, -2, -2, -2, 0, 0, 0 I would expect my average
to
be -0.6

Best average for 5 years out of 8
 

For top 5 the {1,2,3,4,5} is fine but if one wanted
the top 50 this would be a pain, so another solution
is the array formula
=AVERAGE(LARGE(A11:H11,ROW(1:5)))

And that's vulnerable to row insertions will could cause an incorrect
result. Might be better to use this array** version:

=AVERAGE(LARGE(A12:H12,ROW(INDIRECT("1:5"))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

Two points, the average of the second set is not -0.6 but +0.6?

For top 5 the {1,2,3,4,5} is fine but if one wanted the top 50 this would
be
a pain, so another solution is the array formula

=AVERAGE(LARGE(A11:H11,ROW(1:5)))

To make it an array you press Shift+Ctrl+Enter instead of Enter to enter
it.
Notice that to average the top 50 this formula would be virtually the
same:

=AVERAGE(LARGE(A11:DD11,ROW(1:50)))

while the {1,2,3,4,5,6,7,8,9,10,11,..... would get rather tedious.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Wendy" wrote:

PERFECT!! Thank you so much!

"Bernard Liengme" wrote:

=AVERAGE(LARGE(A1:H1,{1,2,3,4,5}))
This is an array formula so commit it with CTRL+SHIFT+ENTER not just
ENTER
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Wendy" wrote in message
...
I am trying to figure out how to calculate an average of the best five
years
out of 8.

My first example is 0, 3, 3, 5, 5, 3, 5, 5 I would expect my average
to
be
4.6.

Another example is 5, -2, -2, -2, -2, 0, 0, 0 I would expect my
average
to
be -0.6