I am a golfer and am organising a competition. I want to formulate a cell on
a spread sheet that will give me the best 9 weeks scores from 12 weeks.
anone any ideas?

On Sat, 13 Dec 2008 08:48:01 -0800, papius
wrote:

I am a golfer and am organising a competition. I want to formulate a cell on
a spread sheet that will give me the best 9 weeks scores from 12 weeks.
anone any ideas?

Do you really want 9 scores in one cell?
Or do you want the SUM of the best 9 scores in a cell?
Is a score a number, like 5 or 8, or is it text like "Pretty good" or
"Very bad"?

You may get the result you ask for quicker if you give some
information about your problem that is not obvious for everyone.
Having said that, if I may assume that the twelve scores are positive
numbers, one score in each of the cells A1,A2,...,A12 and that a
higher score is better than a lower score, you may try this formula:

=SUMPRODUCT(A1:A12,0+((100*(A1:A12)+ROW(A1:A12))= LARGE(100*(A1:A12)+ROW(A1:A12),9)))

This will give the sum of the nine highest numbers in cells A1:A12.

If any of my assumtions are not correct, please give more information.

Hope this helps / Lars-Åke

Assumes your 12 weeks in L2:L13

Sub bestnineoftwelve()
mc = "L"
lr = Cells(Rows.Count, mc).End(xlUp).Row
Set rng = Range(Cells(2, mc), Cells(lr, mc))
For i = 1 To 9
ms = ms & "," & Application.Large(rng, i)
Next i
'MsgBox Right(ms, Len(ms) - 1)
Cells(1, mc) = Right(ms, Len(ms) - 1)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"papius" wrote in message
...
I am a golfer and am organising a competition. I want to formulate a cell
on
a spread sheet that will give me the best 9 weeks scores from 12 weeks.
anone any ideas?

try this formula to give you the total of top 10 scores
assuming that your scores are in column B2, place this in C2 and copy down
you need to change the cell's references to yours.

=SUM(LARGE($B$2:$B$20,{1,2,3,4,5,6,7,8,9,10}))

or this formula which will give you the ranking of your top 10 scores

=RANK(B2,$B$2:$B$20,0)+COUNTIF($B$2:B2,B2)-1

Alternatively, you can Sort Ascending or use Auto Filter in Excel to
identify the top 10 scorers and scores.
To use Auto Filter,
click on cell A1
Select Data on the menu Filter
Click Auto Filter. your spreadsheet
click the arrow down small button
choose Top 10

Excel will populate the top 10 scores for you

Pls post back if you encounter any problem

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"papius" wrote:

I am a golfer and am organising a competition. I want to formulate a cell on
a spread sheet that will give me the best 9 weeks scores from 12 weeks.
anone any ideas?

On Sat, 13 Dec 2008 17:10:37 GMT, Lars-Åke Aspelin
wrote:

On Sat, 13 Dec 2008 08:48:01 -0800, papius
wrote:

I am a golfer and am organising a competition. I want to formulate a cell on
a spread sheet that will give me the best 9 weeks scores from 12 weeks.
anone any ideas?

Do you really want 9 scores in one cell?
Or do you want the SUM of the best 9 scores in a cell?
Is a score a number, like 5 or 8, or is it text like "Pretty good" or
"Very bad"?

You may get the result you ask for quicker if you give some
information about your problem that is not obvious for everyone.
Having said that, if I may assume that the twelve scores are positive
numbers, one score in each of the cells A1,A2,...,A12 and that a
higher score is better than a lower score, you may try this formula:

=SUMPRODUCT(A1:A12,0+((100*(A1:A12)+ROW(A1:A12)) =LARGE(100*(A1:A12)+ROW(A1:A12),9)))

This will give the sum of the nine highest numbers in cells A1:A12.

If any of my assumtions are not correct, please give more information.

Hope this helps / Lars-Åke

The following formula is shorter, but please note that it is an array
formula that has to be entered with CTRL+SHIFT+ENTER rather than just
ENTER.

=SUM(LARGE(A1:A12,ROW(1:9)))

Hope this helps / Lars-Åke

Lars thank you for your help.. I am a relative beginner at this so please
accept my apologies for the lack of information. in the particular
spreadsheet the cells that I have chosen to input the scores start at G3 and
actually go on for 13 entries up to S3. a numerical score for 13 weeks. after
that I have a cell totalling ( sum) all those weeks. the best 9 from that 13
is what I need. is it a simple case of changing the cell ids in your formula?

"Lars-Ã…ke Aspelin" wrote:

On Sat, 13 Dec 2008 08:48:01 -0800, papius
wrote:

I am a golfer and am organising a competition. I want to formulate a cell on
a spread sheet that will give me the best 9 weeks scores from 12 weeks.
anone any ideas?

Do you really want 9 scores in one cell?
Or do you want the SUM of the best 9 scores in a cell?
Is a score a number, like 5 or 8, or is it text like "Pretty good" or
"Very bad"?

You may get the result you ask for quicker if you give some
information about your problem that is not obvious for everyone.
Having said that, if I may assume that the twelve scores are positive
numbers, one score in each of the cells A1,A2,...,A12 and that a
higher score is better than a lower score, you may try this formula:

=SUMPRODUCT(A1:A12,0+((100*(A1:A12)+ROW(A1:A12))= LARGE(100*(A1:A12)+ROW(A1:A12),9)))

This will give the sum of the nine highest numbers in cells A1:A12.

If any of my assumtions are not correct, please give more information.

Hope this helps / Lars-Ã…ke

Thanks xlm.
i have successfully put that formula ( for 10 best scores ) into one cell
and it works. how do I now put that into every other line for each individual
who is playing. is there a way of copying that easily?. I have tried the copy
formula instructions and it does not seem to work?

"xlm" wrote:

try this formula to give you the total of top 10 scores
assuming that your scores are in column B2, place this in C2 and copy down
you need to change the cell's references to yours.

=SUM(LARGE($B$2:$B$20,{1,2,3,4,5,6,7,8,9,10}))

or this formula which will give you the ranking of your top 10 scores

=RANK(B2,$B$2:$B$20,0)+COUNTIF($B$2:B2,B2)-1

Alternatively, you can Sort Ascending or use Auto Filter in Excel to
identify the top 10 scorers and scores.
To use Auto Filter,
click on cell A1
Select Data on the menu Filter
Click Auto Filter. your spreadsheet
click the arrow down small button
choose Top 10

Excel will populate the top 10 scores for you

Pls post back if you encounter any problem

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"papius" wrote:

I am a golfer and am organising a competition. I want to formulate a cell on
a spread sheet that will give me the best 9 weeks scores from 12 weeks.
anone any ideas?

Hi Papius,

I am glad it work for you :).
Are these other cells in the rows below the first one that you have paste?
If they are, click on the cell with the formula and move your cursor to the
right
bottom corner of the cell, your cursor will change to a + sign, then right
click
the mouse and hold follow by dragging down.

If not, I need you to explain more or provide a sample

Let me know how its goes, I will try to assist.


--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"papius" wrote:

Thanks xlm.
i have successfully put that formula ( for 10 best scores ) into one cell
and it works. how do I now put that into every other line for each individual
who is playing. is there a way of copying that easily?. I have tried the copy
formula instructions and it does not seem to work?

"xlm" wrote:

try this formula to give you the total of top 10 scores
assuming that your scores are in column B2, place this in C2 and copy down
you need to change the cell's references to yours.

=SUM(LARGE($B$2:$B$20,{1,2,3,4,5,6,7,8,9,10}))

or this formula which will give you the ranking of your top 10 scores

=RANK(B2,$B$2:$B$20,0)+COUNTIF($B$2:B2,B2)-1

Alternatively, you can Sort Ascending or use Auto Filter in Excel to
identify the top 10 scorers and scores.
To use Auto Filter,
click on cell A1
Select Data on the menu Filter
Click Auto Filter. your spreadsheet
click the arrow down small button
choose Top 10

Excel will populate the top 10 scores for you

Pls post back if you encounter any problem

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"papius" wrote:

I am a golfer and am organising a competition. I want to formulate a cell on
a spread sheet that will give me the best 9 weeks scores from 12 weeks.
anone any ideas?

=SUM(LARGE(G3:S3,ROW(1:9)))

will give the sum of the nine highest scores in G3:S3

Hope that is what you wanted / Lars-Åke

On Mon, 15 Dec 2008 14:59:02 -0800, papius
wrote:

Lars thank you for your help.. I am a relative beginner at this so please
accept my apologies for the lack of information. in the particular
spreadsheet the cells that I have chosen to input the scores start at G3 and
actually go on for 13 entries up to S3. a numerical score for 13 weeks. after
that I have a cell totalling ( sum) all those weeks. the best 9 from that 13
is what I need. is it a simple case of changing the cell ids in your formula?

"Lars-Åke Aspelin" wrote:

On Sat, 13 Dec 2008 08:48:01 -0800, papius
wrote:

I am a golfer and am organising a competition. I want to formulate a cell on
a spread sheet that will give me the best 9 weeks scores from 12 weeks.
anone any ideas?

Do you really want 9 scores in one cell?
Or do you want the SUM of the best 9 scores in a cell?
Is a score a number, like 5 or 8, or is it text like "Pretty good" or
"Very bad"?

You may get the result you ask for quicker if you give some
information about your problem that is not obvious for everyone.
Having said that, if I may assume that the twelve scores are positive
numbers, one score in each of the cells A1,A2,...,A12 and that a
higher score is better than a lower score, you may try this formula:

=SUMPRODUCT(A1:A12,0+((100*(A1:A12)+ROW(A1:A12))= LARGE(100*(A1:A12)+ROW(A1:A12),9)))

This will give the sum of the nine highest numbers in cells A1:A12.

If any of my assumtions are not correct, please give more information.

Hope this helps / Lars-Åke