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

"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" wrote in message
...
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

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
"Ron Coderre" wrote in message
...
"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" wrote in message
...
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

"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

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" wrote in message
...
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
"Ron Coderre" wrote in message
...
"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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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
"Ron Coderre" wrote in message
...
"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" wrote in message
...
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

Hi, Steve

1)
Hi Ron I really didn't think this would be such a problem.
Well, I just got back from vacation, so maybe my mind still back on the
beach. <G

2)
Largest 8 scores per person?.-yes
Do you want them listed somewhere on the worksheet? - don't understand

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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
"Ron Coderre" wrote in message
...
"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" wrote in message
...
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

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

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

1)
Hi Ron I really didn't think this would be such a problem.
Well, I just got back from vacation, so maybe my mind still back on the
beach. <G

2)
Largest 8 scores per person?.-yes
Do you want them listed somewhere on the worksheet? - don't understand

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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
"Ron Coderre" wrote in message
...
"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" wrote in message
...
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

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

Sorry it looked OK on my P/C when I sent it.
"Steve Smyth" wrote in message
...
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

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

1)
Hi Ron I really didn't think this would be such a problem.
Well, I just got back from vacation, so maybe my mind still back on the
beach. <G

2)
Largest 8 scores per person?.-yes
Do you want them listed somewhere on the worksheet? - don't understand

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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
"Ron Coderre" wrote in message
...
"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" wrote in message
...
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

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)" wrote in
message ...
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

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" wrote in message
...
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)" wrote in
message ...
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

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

"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in message
...
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)" wrote in
message ...
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