I want to calculate the average of best 5 out of 7 numbers?

  • Thread starter Attempt at solving a Matrix Problem?
  • Start date
A

Attempt at solving a Matrix Problem?

How can I calculate the average of 5 best out of 7.. or best 10 out of 15..
For example if I have 10 different numbers. 10,7,9,8,5,6,7,5,6,7 but I only
want it to take the average of the best 7.. I really appreciate your help..
Thank you!

Eyad
 
B

Biff

Hi!
if I have 10 different numbers. 10,7,9,8,5,6,7,5,6,7 but I only
want it to take the average of the best 7..
Click to expand...

What do you consider to be the best 7?

Biff

"Attempt at solving a Matrix Problem?"
 
T

TheRobsterUK

You could use the LARGE worksheet function and program in as many
formulas as numbers that you want to assess, then take the average of
the numbers that this gives you.

This wouldn't be very efficient though, you really need an array
formula and I aren't all that good at making them!
 
A

Attempt at solving a Matrix Problem?

Hello,

Oh Sorry, The best out of 7 could be the highest 7 numbers.. or even the
lowest 7 numbers.. either way..
Thank you

Eyad
 
X

xlmaven

Here is a solution based upon a suggestion in John Walkenbach's Excel
2003 Formulas. Enter the following array formula in a single cell:
=AVERAGE(LARGE(ColumnRange,ROW(INDIRECT("1:3"))))

Here ColumnRange is the given range of numbers. In this example, the
average of the largest 3 is computed.

George Monahan
[email protected]
 
A

Attempt at solving a Matrix Problem?

I attempted it, but this only takes the largest out of them all.. I put the
numbers in Rows also..

Eyad
 
S

Sloth

Average of top 7 out of 10 (located in A1:A10)
=AVERAGE(LARGE(A1:A10,{1,2,3,4,5,6,7}))

Average of top 10 out of 15 (located in A1:A15)
=AVERAGE(LARGE(A1:A15,{1,2,3,4,5,6,7,8,9,10}))

Average of bottom 7 out of 15 (located in A1:A10)
=AVERAGE(SMALL(A1:A10,{1,2,3,4,5,6,7}))

Average of bottom 10 out of 15 (located in A1:A15)
=AVERAGE(SMALL(A1:A15,{1,2,3,4,5,6,7,8,9,10}))
 
Top