sum of top 16 numbers

Barb · 04-12-2006, 05:15 PM

I'm trying to total only the top 16 scores in a row. I have 1 cell that
'counts' the number of entries and would like to use that as part of this
formula.

As the events continue, this number will change accordingly.

Something like???
if e6<=16,sum(f6:z6),???

Bob Phillips · 04-12-2006, 05:20 PM

=SUM(LARGE(F6:Z6,ROW(1:16)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Barb" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to total only the top 16 scores in a row. I have 1 cell that
> 'counts' the number of entries and would like to use that as part of this
> formula.
>
> As the events continue, this number will change accordingly.
>
> Something like???
> if e6<=16,sum(f6:z6),???

Chip Pearson · 04-12-2006, 05:30 PM

In case a row might be inserted within the first 16 rows, it
would be safer to use
=SUM(LARGE(F6:Z6,ROW(INDIRECT("1:16"))))

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Bob Phillips" <[email protected]> wrote in
message news:%[email protected]...
> =SUM(LARGE(F6:Z6,ROW(1:16)))
>
> which is an array formula, it should be committed with
> Ctrl-Shift-Enter, not
> just Enter.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Barb" <[email protected]> wrote in message
> news:[email protected]...
>> I'm trying to total only the top 16 scores in a row. I have 1
>> cell that
>> 'counts' the number of entries and would like to use that as
>> part of this
>> formula.
>>
>> As the events continue, this number will change accordingly.
>>
>> Something like???
>> if e6<=16,sum(f6:z6),???
>
>

Barb · 04-12-2006, 05:30 PM

That works great, but when I copy it down to the next row, I get an #num!
error.

"Bob Phillips" wrote:

> =SUM(LARGE(F6:Z6,ROW(1:16)))
>
> which is an array formula, it should be committed with Ctrl-Shift-Enter, not
> just Enter.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Barb" <[email protected]> wrote in message
> news:[email protected]...
> > I'm trying to total only the top 16 scores in a row. I have 1 cell that
> > 'counts' the number of entries and would like to use that as part of this
> > formula.
> >
> > As the events continue, this number will change accordingly.
> >
> > Something like???
> > if e6<=16,sum(f6:z6),???
>
>
>

Bob Phillips · 04-12-2006, 05:43 PM

Barb,

Use this version then in that case

=SUM(LARGE(F6:Z6,ROW(INDIRECT("1:16"))))

still array entered.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Barb" <[email protected]> wrote in message
news:[email protected]...
> That works great, but when I copy it down to the next row, I get an #num!
> error.
>
> "Bob Phillips" wrote:
>
> > =SUM(LARGE(F6:Z6,ROW(1:16)))
> >
> > which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
> > just Enter.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Barb" <[email protected]> wrote in message
> > news:[email protected]...
> > > I'm trying to total only the top 16 scores in a row. I have 1 cell
that
> > > 'counts' the number of entries and would like to use that as part of
this
> > > formula.
> > >
> > > As the events continue, this number will change accordingly.
> > >
> > > Something like???
> > > if e6<=16,sum(f6:z6),???
> >
> >
> >

Barb · 04-12-2006, 05:55 PM

This isn't working...

I have many rows. There are many columns. I'm trying to total only the top
16 scores in a row... not column.

for example:
sum sum
of all of top # of
name scores 16 scores scores Event 1 E2 E3 ..... E20
john doe 151 ??? 16 12 14 12
12
jim jones 145 ??? 15 11 12 11
14

"Chip Pearson" wrote:

> In case a row might be inserted within the first 16 rows, it
> would be safer to use
> =SUM(LARGE(F6:Z6,ROW(INDIRECT("1:16"))))
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Bob Phillips" <[email protected]> wrote in
> message news:%[email protected]...
> > =SUM(LARGE(F6:Z6,ROW(1:16)))
> >
> > which is an array formula, it should be committed with
> > Ctrl-Shift-Enter, not
> > just Enter.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Barb" <[email protected]> wrote in message
> > news:[email protected]...
> >> I'm trying to total only the top 16 scores in a row. I have 1
> >> cell that
> >> 'counts' the number of entries and would like to use that as
> >> part of this
> >> formula.
> >>
> >> As the events continue, this number will change accordingly.
> >>
> >> Something like???
> >> if e6<=16,sum(f6:z6),???
> >
> >
>
>
>

**vane0326** · 04-12-2006, 07:29 PM

Does this help?

=SUM(LARGE($F6:$Z6,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}))

Bob Phillips · 04-13-2006, 03:45 AM

We know that, and it does work. It would help if you explained exactly what
you did and the result you got.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Barb" <[email protected]> wrote in message
news:[email protected]...
> This isn't working...
>
> I have many rows. There are many columns. I'm trying to total only the
top
> 16 scores in a row... not column.
>
> for example:
> sum sum
> of all of top # of
> name scores 16 scores scores Event 1 E2 E3 .....
E20
> john doe 151 ??? 16 12 14 12
> 12
> jim jones 145 ??? 15 11 12 11
> 14
>
> "Chip Pearson" wrote:
>
> > In case a row might be inserted within the first 16 rows, it
> > would be safer to use
> > =SUM(LARGE(F6:Z6,ROW(INDIRECT("1:16"))))
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> > "Bob Phillips" <[email protected]> wrote in
> > message news:%[email protected]...
> > > =SUM(LARGE(F6:Z6,ROW(1:16)))
> > >
> > > which is an array formula, it should be committed with
> > > Ctrl-Shift-Enter, not
> > > just Enter.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Barb" <[email protected]> wrote in message
> > > news:[email protected]...
> > >> I'm trying to total only the top 16 scores in a row. I have 1
> > >> cell that
> > >> 'counts' the number of entries and would like to use that as
> > >> part of this
> > >> formula.
> > >>
> > >> As the events continue, this number will change accordingly.
> > >>
> > >> Something like???
> > >> if e6<=16,sum(f6:z6),???
> > >
> > >
> >
> >
> >