Adding selected numbe in a column Les Golf 29.08.2006 23:24:01 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.

Re: Adding selected numbe in a column "Franz Verga" <fra68ve[ at ]InVento.it> 30.08.2006 00:56:18 Les Golf wrote: [Quoted Text] > 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

Re: Adding selected numbe in a column SteveW <sj_walton[ at ]nothotmail.com> 30.08.2006 03:02:35 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 <fra68ve[ at ]InVento.it> wro= te: [Quoted Text] > 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: > > =3DSUM(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. >

Re: Adding selected numbe in a column Dave Peterson <petersod[ at ]verizonXSPAM.net> 30.08.2006 03:13:17 =SUM(LARGE($A$1:$A$100,ROW(indirect("1:50")))) SteveW wrote: [Quoted Text] > > 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 <fra68ve[ at ]InVento.it> 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

Re: Adding selected numbe in a column SteveW <sj_walton[ at ]nothotmail.com> 30.08.2006 03:23:04 Neat. And Ta Steve On Wed, 30 Aug 2006 04:13:17 +0100, Dave Peterson = <petersod[ at ]verizonXSPAM.net> wrote: [Quoted Text] > =3DSUM(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 headin= g) >> 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 <fra68ve[ at ]InVento.it> = = >> wrote: >> >> > Les Golf wrote: >> >> I am using Excel to get results of a competition where there are 1= 00 >> >> 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: >> > >> > =3DSUM(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 t= his >> > range. >> > >

Re: Adding selected numbe in a column Les Golf 31.08.2006 03:04:02 Franz, Thanks worked perfectly....I can now produce the results easily Tks again Les "Franz Verga" wrote: [Quoted Text] > 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 > > >