Adding selected numbe in a column
 

I am using Excel to get results of a competition where there are 100 results
of which the top 50 count for each player.
Need the Total of the column to only add the best 50 scores.

Adding selected numbe in a column
 

Les Golf wrote:
I am using Excel to get results of a competition where there are 100
results of which the top 50 count for each player.
Need the Total of the column to only add the best 50 scores.


Hi Les,

try with this formula:

=SUM(LARGE($A$1:$A$100,ROW($A$1:$A$50)))

array-entered, i.e. you have to press CTRL + SHIFT + ENTER instead of just
ENTER.

Where the results I assume are in $A$1:$A$100, but you can change this
range.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Adding selected numbe in a column
 

Franz, works, but if I add a row at the top of the data (for a heading)
The formula changes to have 2:51 for the rows !

Might be clearer to have ROW($1:$50), to indicate that its just a
shorthand for the numbers 1 to 50

but it still changes when I add a row
irratating :)

Steve


On Wed, 30 Aug 2006 01:56:18 +0100, Franz Verga wrote:

Les Golf wrote:
I am using Excel to get results of a competition where there are 100
results of which the top 50 count for each player.
Need the Total of the column to only add the best 50 scores.


Hi Les,

try with this formula:

=SUM(LARGE($A$1:$A$100,ROW($A$1:$A$50)))

array-entered, i.e. you have to press CTRL + SHIFT + ENTER instead of
just
ENTER.

Where the results I assume are in $A$1:$A$100, but you can change this
range.

Adding selected numbe in a column
 

=SUM(LARGE($A$1:$A$100,ROW(indirect("1:50"))))

SteveW wrote:

Franz, works, but if I add a row at the top of the data (for a heading)
The formula changes to have 2:51 for the rows !

Might be clearer to have ROW($1:$50), to indicate that its just a
shorthand for the numbers 1 to 50

but it still changes when I add a row
irratating :)

Steve

On Wed, 30 Aug 2006 01:56:18 +0100, Franz Verga wrote:

Les Golf wrote:
I am using Excel to get results of a competition where there are 100
results of which the top 50 count for each player.
Need the Total of the column to only add the best 50 scores.


Hi Les,

try with this formula:

=SUM(LARGE($A$1:$A$100,ROW($A$1:$A$50)))

array-entered, i.e. you have to press CTRL + SHIFT + ENTER instead of
just
ENTER.

Where the results I assume are in $A$1:$A$100, but you can change this
range.


--

Dave Peterson

Adding selected numbe in a column
 

Neat.
And Ta

Steve


On Wed, 30 Aug 2006 04:13:17 +0100, Dave Peterson
wrote:

=SUM(LARGE($A$1:$A$100,ROW(indirect("1:50"))))

SteveW wrote:

Franz, works, but if I add a row at the top of the data (for a heading)
The formula changes to have 2:51 for the rows !

Might be clearer to have ROW($1:$50), to indicate that its just a
shorthand for the numbers 1 to 50

but it still changes when I add a row
irratating :)

Steve

On Wed, 30 Aug 2006 01:56:18 +0100, Franz Verga
wrote:

Les Golf wrote:
I am using Excel to get results of a competition where there are 100
results of which the top 50 count for each player.
Need the Total of the column to only add the best 50 scores.


Hi Les,

try with this formula:

=SUM(LARGE($A$1:$A$100,ROW($A$1:$A$50)))

array-entered, i.e. you have to press CTRL + SHIFT + ENTER instead of
just
ENTER.

Where the results I assume are in $A$1:$A$100, but you can change this
range.

Adding selected numbe in a column
 

Franz,

Thanks worked perfectly....I can now produce the results easily

Tks again
Les

"Franz Verga" wrote:

Les Golf wrote:
I am using Excel to get results of a competition where there are 100
results of which the top 50 count for each player.
Need the Total of the column to only add the best 50 scores.


Hi Les,

try with this formula:

=SUM(LARGE($A$1:$A$100,ROW($A$1:$A$50)))

array-entered, i.e. you have to press CTRL + SHIFT + ENTER instead of just
ENTER.

Where the results I assume are in $A$1:$A$100, but you can change this
range.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy