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

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

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" wrote in message
...
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

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 ,
Tas 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?

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

--
Regards,
Tom Ogilvy


"Ken Wright" wrote in message
...
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" wrote in message
...
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

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=392661

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

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


"Tom Ogilvy" wrote in message
...
It was a trick Ken. He said 5 in the subject, but 12 in the body.

--
Regards,
Tom Ogilvy


"Ken Wright" wrote in message
...
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" wrote in message
...
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