Adding selected numbe in a column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Les Golf - 30 Aug 2006 00:24 GMT

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.

Reply to this Message

Franz Verga - 30 Aug 2006 01:56 GMT

> 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.

Signature

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

SteveW - 30 Aug 2006 04:02 GMT

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

>> I am using Excel to get results of a competition where there are 100
>> results of which the top 50 count for each player.
[quoted text clipped - 12 lines]
> Where the results I assume are in $A$1:$A$100, but you can change this
> range.

Reply to this Message

Dave Peterson - 30 Aug 2006 04:13 GMT

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

> 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 !
[quoted text clipped - 23 lines]
> > Where the results I assume are in $A$1:$A$100, but you can change this
> > range.

Signature

Dave Peterson

Reply to this Message

SteveW - 30 Aug 2006 04:23 GMT

Neat.
And Ta

Steve

> =SUM(LARGE($A$1:$A$100,ROW(indirect("1:50"))))
>
[quoted text clipped - 25 lines]
>> > Where the results I assume are in $A$1:$A$100, but you can change this
>> > range.

Reply to this Message

Les Golf - 31 Aug 2006 04:04 GMT

Franz,

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

Tks again
Les

> > I am using Excel to get results of a competition where there are 100
> > results of which the top 50 count for each player.
[quoted text clipped - 11 lines]
> Where the results I assume are in $A$1:$A$100, but you can change this
> range.

Reply to this Message