Best 8 from 11 results Formula Please

[Steve Smyth]

Hello I've been trying to find the formula to calculate the best 8 scores
from 11 results.
Scores are numbers from 0 to 130.
Can you help
Thanks

Steve

 

[Ron Coderre]

"Calculate" and "Best" are a little vague.

Do you want to sum? average? the eight largest? smallest? smallest non-zero?
If there are less than 8 values, return error? return "insufficient data"?
use available data?
Is the data in rows or columns?

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

Regards,

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

 

[Steve Smyth]

Thanks for your answer Ron.
They are Kart racing scores but the highest 8 only count towards the
championship.
So it's the eight largest and a non appearance at a round would be a zero.
Thanks

Steve

 

[Rick Rothstein \(MVP - VB\)]

"Calculate" and "Best" are a little vague.

Do you want to sum? average? the eight largest? smallest? smallest
non-zero?
If there are less than 8 values, return error? return "insufficient data"?
use available data?
Is the data in rows or columns?

One additional question to those Ron asked you... what if there are
duplicate values within the "best" eight... do they each count individually
or are you after 8 *unique* "best" scores?

Rick

 

[Ron Coderre]

Still wanting more information....

Largest 8 scores per person? Overall for all competitors?
Do you want them listed somewhere on the worksheet?
Do you need names associated with the top 8 scores?
How do you want to handle tie scores?

Meanwhile, see the LARGE function in Excel Help.

Is that something you can work with?

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

Regards,

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

 

[Steve Smyth]

Hi Ron I really didn't think this would be such a problem.
Largest 8 scores per person?.-yes
Overall for all competitors?-don't care
Do you want them listed somewhere on the worksheet? - don't understand
Do you need names associated with the top 8 scores?- they have names
How do you want to handle tie scores?- if there is a tie so-be-it
Meanwhile, see the LARGE function in Excel Help- I don't understand it.
I'm sorry to be so vague but I'm a grumpy old man who can't type to save his
life and finds Misrosoft help files a foreign language.
Thanks

Steve

 

[Ron Coderre]

Hi, Steve

1)Well, I just got back from vacation, so maybe my mind still back on the
beach. <G>

2)
Ok....You have a row of 11 scores (eg cells B1:L1)
Eight of them are larger than the remaining scores in that range.

What needs to happen to those scores?

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

Regards,

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

 

[Steve Smyth]

Hi Ron I hope it was nice on the beach
Something like this
Round 1 Round 2 Round 3 Round 4 Round 5 Round 6 Round 7 Round 8 Round 9
Round 10 Round 11 Total Best 8
Name 130 130 127 111 104 101 130 121 124 130 1208 #NAME?
name 124 0 119 115 127 120 127 127 130 127 1116
Wolfy 116 114 130 130 119 119 120 127 130 118 1223

My attempt at the formula for Best 8 was no good
hope that helps

Steve

 

[Rick Rothstein \(MVP - VB\)]

Hi Ron I really didn't think this would be such a problem.

There are multiple ways to read your request. While you know what you want,
we don't... so all we have to go on is the completeness of the description
you give us.

Largest 8 scores per person?.-yes
Overall for all competitors?-don't care
Do you want them listed somewhere on the worksheet? - don't understand
Do you need names associated with the top 8 scores?- they have names
How do you want to handle tie scores?- if there is a tie so-be-it
Meanwhile, see the LARGE function in Excel Help- I don't understand it.
I'm sorry to be so vague but I'm a grumpy old man who can't type to save
his life and finds Misrosoft help files a foreign language.

All right, assuming your names are listed in column A and that your scores
are in column B through L and that the first name is on row 2.... I am going
to assume you want the 8 highest scores listed in column N through U within
the same row. Put this formula in N2...

=LARGE(($B2:$L2<>"")*$B2:$L2,COLUMNS($A:A))

and commit it by pressing Ctrl+Shift+Enter (do **not** just press Enter by
itself). Now, copy N2 across through to U2. That will give you the 8 highest
scores (each duplicate score counts individually) for the first name. Next,
select N2:U2 and then copy it down through all your names (which will give
you the 8 highest scores for the rest of the names).

Rick

 

[Steve Smyth]

Sorry it looked OK on my P/C when I sent it.

Hi Ron I hope it was nice on the beach
Something like this
Round 1 Round 2 Round 3 Round 4 Round 5 Round 6 Round 7 Round 8 Round
9 Round 10 Round 11 Total Best 8
Name 130 130 127 111 104 101 130 121 124 130 1208 #NAME?
name 124 0 119 115 127 120 127 127 130 127 1116
Wolfy 116 114 130 130 119 119 120 127 130 118 1223

My attempt at the formula for Best 8 was no good
hope that helps

Steve

 

[Steve Smyth]

Thanks Rick that was perfect.
I was hoping I could do it in one formula but that is fine. Just hide the
columns N to U and have =Sum(N2:U2) in column V.
Thanks to both of you, you've been a great help.
PS Typing is a real problem for me and if I didn't go over what I've done
and correct it you would probabley not understand a word.
So sorry again to be so vague.

Steve

 

[Rick Rothstein \(MVP - VB\)]

Ahh! You wanted the **sum** of the eight highest scores... I did not
understand that to be what you were looking for from your other postings. I
think this single formula (Entered normally) will give you that...

=SUMPRODUCT(LARGE(($B2:$L2<>"")*$B2:$L2,{1,2,3,4,5,6,7,8}))

You will not need the other formulas I posted... the above should give you
the sum directly.

Rick

 

[Steve Smyth]

Thanks again Rick that's perfect
Now I can work out what my son has to do in the last round to win the
championship. (Kill the first two drivers )
Steve