Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to enter telephone numbers in Excel sheet | Excel Discussion (Misc queries) | |||
why have column letters changed to numbers in excel | Excel Discussion (Misc queries) | |||
how write excel formula numbers in column => &=< between 20-40 | New Users to Excel | |||
My formulas can't 'see' numbers imported from Access in Excel, even when the format is the same. | Excel Discussion (Misc queries) | |||
How do i increase the size of numbers in a text in Excel. | Excel Worksheet Functions |