How do I use excel to add up the best 5 numbers in a sequence?

Tas · 08-03-2005, 03:05 PM

I am running a spreadsheet where I need to add up the 12 best numbers from a
sequence of 19, running across. Does anyone have any idea how I can do this?

Thanks

Jon

Ken Wright · 08-03-2005, 04:05 PM

What is 'best'

Assuming it is largest, then

=SUM(LARGE($A$1:$A$30,{1,2,3,4,5}))

or if smaller is better

=SUM(SMALL($A$1:$A$30,{1,2,3,4,5}))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

"Tas" <[email protected]> wrote in message
news:[email protected]...
>I am running a spreadsheet where I need to add up the 12 best numbers from
>a
> sequence of 19, running across. Does anyone have any idea how I can do
> this?
>
> Thanks
>
> Jon

JE McGimpsey · 08-03-2005, 04:05 PM

Do you want 5 (your subject) or 12 (the body of your message)??

What makes the numbers "best"? Largest? Smallest? Something else?

If Largest:

=SUM(LARGE(rng,{1,2,3,4,5,6,7,8,9,10,11,12}))

or, equivalently (but array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(LARGE(rng,ROW(INDIRECT("1:12"))))

For smallest, see SMALL() in Help.

In article <[email protected]>,
Tas <[email protected]> wrote:

> I am running a spreadsheet where I need to add up the 12 best numbers from a
> sequence of 19, running across. Does anyone have any idea how I can do this?

Tom Ogilvy · 08-03-2005, 04:05 PM

It was a trick Ken. He said 5 in the subject, but 12 in the body.

--
Regards,
Tom Ogilvy

"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> What is 'best'
>
> Assuming it is largest, then
>
> =SUM(LARGE($A$1:$A$30,{1,2,3,4,5}))
>
> or if smaller is better
>
> =SUM(SMALL($A$1:$A$30,{1,2,3,4,5}))
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ------------------------------*------------------------------*------------
----
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------*------------------------------*------------
----
>
>
>
> "Tas" <[email protected]> wrote in message
> news:[email protected]...
> >I am running a spreadsheet where I need to add up the 12 best numbers
from
> >a
> > sequence of 19, running across. Does anyone have any idea how I can do
> > this?
> >
> > Thanks
> >
> > Jon
>
>

**swatsp0p** · 08-03-2005, 04:33 PM

As an FYI to the OP... using Large will count duplicate entries as separate entries.

Example:
---A-B-C-D-E
1]8,8,8,7,6

=SUM(LARGE(A1:E1,{1,2,3})) will return 24 not 21 as you may expect. 8 is not only the #1 Large number, it is also the #2 and #3 Large number.

HTH

Bruce

Ken Wright · 08-04-2005, 05:05 PM

LOL - Cheers Tom, shows how much of the note I read :-)

Regards
Ken....................

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> It was a trick Ken. He said 5 in the subject, but 12 in the body.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Ken Wright" <[email protected]> wrote in message
> news:[email protected]...
>> What is 'best'
>>
>> Assuming it is largest, then
>>
>> =SUM(LARGE($A$1:$A$30,{1,2,3,4,5}))
>>
>> or if smaller is better
>>
>> =SUM(SMALL($A$1:$A$30,{1,2,3,4,5}))
>>
>> --
>> Regards
>> Ken....................... Microsoft MVP - Excel
>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>
>> ------------------------------*------------------------------*------------
> ----
>> It's easier to beg forgiveness than ask permission :-)
>> ------------------------------*------------------------------*------------
> ----
>>
>>
>>
>> "Tas" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am running a spreadsheet where I need to add up the 12 best numbers
> from
>> >a
>> > sequence of 19, running across. Does anyone have any idea how I can do
>> > this?
>> >
>> > Thanks
>> >
>> > Jon
>>
>>
>
>