Sum of the Largest Values

Hi.

I have a gradebook.  I have 16 assignments, and thus have 
16 columns for scores.  However, I would like to include 
ONLY the highest 10 scores in the final grade.  In other 
words, how can I find the sum of the largest 10 values in 
a 16-cell range?  

Thanks!
0
chimi1 (1)
1/2/2004 11:34:12 PM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
306 Views

Similar Articles

[PageSpeed] 39

=SUM(LARGE($A$1:$P$1,ROW(INDIRECT("1:10"))))

arary entered using CTRL+SHIFT+ENTER

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

----------------------------------------------------------------------------
Best wishes to all, and hope for a good New year  :-)
----------------------------------------------------------------------------



<chimi@comcast.net> wrote in message
news:036701c3d188$ed6cbdf0$a401280a@phx.gbl...
> Hi.
>
> I have a gradebook.  I have 16 assignments, and thus have
> 16 columns for scores.  However, I would like to include
> ONLY the highest 10 scores in the final grade.  In other
> words, how can I find the sum of the largest 10 values in
> a 16-cell range?
>
> Thanks!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.557 / Virus Database: 349 - Release Date: 30/12/2003


0
ken.wright (2489)
1/2/2004 11:45:13 PM
One way:

=SUM(LARGE(A1:P1,{1,2,3,4,5,6,7,8,9,10}))

Regards

Trevor


<chimi@comcast.net> wrote in message
news:036701c3d188$ed6cbdf0$a401280a@phx.gbl...
> Hi.
>
> I have a gradebook.  I have 16 assignments, and thus have
> 16 columns for scores.  However, I would like to include
> ONLY the highest 10 scores in the final grade.  In other
> words, how can I find the sum of the largest 10 values in
> a 16-cell range?
>
> Thanks!


0
Trevor9259 (673)
1/2/2004 11:48:14 PM
Hi Chimi!

One way:

=SUMPRODUCT(LARGE(C6:P6,ROW(INDIRECT("1:10"))))

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
<chimi@comcast.net> wrote in message
news:036701c3d188$ed6cbdf0$a401280a@phx.gbl...
> Hi.
>
> I have a gradebook.  I have 16 assignments, and thus have
> 16 columns for scores.  However, I would like to include
> ONLY the highest 10 scores in the final grade.  In other
> words, how can I find the sum of the largest 10 values in
> a 16-cell range?
>
> Thanks!


0
njharker (1646)
1/2/2004 11:57:16 PM
=SUMIF(Range,">"&LARGE(Range,10))+LARGE(Range,10)

<chimi@comcast.net> wrote in message
news:036701c3d188$ed6cbdf0$a401280a@phx.gbl...
> Hi.
>
> I have a gradebook.  I have 16 assignments, and thus have
> 16 columns for scores.  However, I would like to include
> ONLY the highest 10 scores in the final grade.  In other
> words, how can I find the sum of the largest 10 values in
> a 16-cell range?
>
> Thanks!


0
akyurek (248)
1/3/2004 3:25:48 AM
Or even just:-

=SUMIF(Rng,">="&LARGE(Rng,10))      :-)

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

----------------------------------------------------------------------------
Best wishes to all, and hope for a good New year  :-)
----------------------------------------------------------------------------



"Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
news:3ff63756$0$327$e4fe514c@news.xs4all.nl...
> =SUMIF(Range,">"&LARGE(Range,10))+LARGE(Range,10)
>
> <chimi@comcast.net> wrote in message
> news:036701c3d188$ed6cbdf0$a401280a@phx.gbl...
> > Hi.
> >
> > I have a gradebook.  I have 16 assignments, and thus have
> > 16 columns for scores.  However, I would like to include
> > ONLY the highest 10 scores in the final grade.  In other
> > words, how can I find the sum of the largest 10 values in
> > a 16-cell range?
> >
> > Thanks!
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.557 / Virus Database: 349 - Release Date: 30/12/2003


0
ken.wright (2489)
1/3/2004 9:17:47 AM
For the sequence
1,2,3,4,5,6,7,7,9,10,11,12,13,14,15,16
your formula will return 107 instead of the
correct result 114.

-- 
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Aladin Akyurek" <akyurek@xs4all.nl> skrev i en meddelelse
news:3ff63756$0$327$e4fe514c@news.xs4all.nl...
> =SUMIF(Range,">"&LARGE(Range,10))+LARGE(Range,10)


0
leo.heuser (111)
1/3/2004 9:41:17 AM
Hi Ken

Your formula will fail with the sequence:

1,2,3,4,5,7,7,8,9,10,11,12,13,14,15,16


-- 
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Ken Wright" <ken.wright@NOSPAMntlworld.com> skrev i en meddelelse
news:%23kawcpd0DHA.1916@TK2MSFTNGP10.phx.gbl...
> Or even just:-
>
> =SUMIF(Rng,">="&LARGE(Rng,10))      :-)
>
> -- 
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                   Sys Spec - Win XP Pro /  XL 00/02/03
>
> --------------------------------------------------------------------------
--
> Best wishes to all, and hope for a good New year  :-)


0
leo.heuser (111)
1/3/2004 9:45:23 AM
You're right. The formula should be dismissed.

"Leo Heuser" <leo.heuser@adslhome.dk> wrote in message
news:%23W%23dE4d0DHA.1336@TK2MSFTNGP12.phx.gbl...
> For the sequence
> 1,2,3,4,5,6,7,7,9,10,11,12,13,14,15,16
> your formula will return 107 instead of the
> correct result 114.
>
> -- 
> Best Regards
> Leo Heuser
>
> Followup to newsgroup only please.
>
> "Aladin Akyurek" <akyurek@xs4all.nl> skrev i en meddelelse
> news:3ff63756$0$327$e4fe514c@news.xs4all.nl...
> > =SUMIF(Range,">"&LARGE(Range,10))+LARGE(Range,10)
>
>


0
akyurek (248)
1/3/2004 10:04:14 AM
Dohhhh - Serves me right for not testing properly - Cheers Leo

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

----------------------------------------------------------------------------
Best wishes to all, and hope for a good New year  :-)
----------------------------------------------------------------------------



"Leo Heuser" <leo.heuser@adslhome.dk> wrote in message
news:usdnL5d0DHA.1184@TK2MSFTNGP10.phx.gbl...
> Hi Ken
>
> Your formula will fail with the sequence:
>
> 1,2,3,4,5,7,7,8,9,10,11,12,13,14,15,16
>
>
> -- 
> Best Regards
> Leo Heuser
>
> Followup to newsgroup only please.
>
> "Ken Wright" <ken.wright@NOSPAMntlworld.com> skrev i en meddelelse
> news:%23kawcpd0DHA.1916@TK2MSFTNGP10.phx.gbl...
> > Or even just:-
> >
> > =SUMIF(Rng,">="&LARGE(Rng,10))      :-)
> >
> > -- 
> > Regards
> >            Ken.......................    Microsoft MVP - Excel
> >                   Sys Spec - Win XP Pro /  XL 00/02/03
> >
> > --------------------------------------------------------------------------
> --
> > Best wishes to all, and hope for a good New year  :-)
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.557 / Virus Database: 349 - Release Date: 30/12/2003


0
ken.wright (2489)
1/3/2004 11:49:48 AM
Hi Ken,

It appears we missed an important sign post. The interaction between two
things made me to post a wrong formula:

[1] The Top N issues;
[2] Eagerness to avoid the ROW(INDIRECT(...) idiom because the volatility
this causes.

Sum (or average, etc.) N largest/smallest values are similar to the Top N
questions. When it's not expicitly stated that the values to be processed
must be *exactly N* then I feel we should at least provide solutions for a
self-adjusting N for Nth value might have multiple instances. An OP at
MrExcel explicitly asked me for the values associated with all
instances/occurrences of the Nth value. A few weeks later I saw Harlan also
explicitly mention this to an OP at  worksheet.functions. Anyway, I no
longer take such questions implicitly as 'exactly N'. The same with
questions of "how can I retrieve the associated with the Min/Max value?".
I feel that "Sum largest/Smallest N" should be based on a self-adjusting
N...

=SUMIF(Range,">"&LARGE(Range,N))+COUNTIF(Range,LARGE(Range,N))*LARGE(Range,N
)

However, Chimi's question (the OP in this thread) involves a question of
summing "exactly N" values. My failed attempt to effect the required sum
without the ROW(INDIRECT(...) idiom stranded (as Leo's sample shows), how
ironic -- considering [1], on the multiple occurrences of the Nth value [
the sign post we missed :( ]. The lesson is that we can't avoid the
ROW(INDIRECT(...) idiom for computations involving questions of exactly N
(where N is variablized).

 The incident enabled me to correct an earlier mistake of mine though
(http://makeashorterlink.com/?K1C5222F6)

Aladin

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:%23kawcpd0DHA.1916@TK2MSFTNGP10.phx.gbl...
> Or even just:-
>
> =SUMIF(Rng,">="&LARGE(Rng,10))      :-)
>
> -- 
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                   Sys Spec - Win XP Pro /  XL 00/02/03
>
> --------------------------------------------------------------------------
--
> Best wishes to all, and hope for a good New year  :-)
> --------------------------------------------------------------------------
--
>
>
>
> "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
> news:3ff63756$0$327$e4fe514c@news.xs4all.nl...
> > =SUMIF(Range,">"&LARGE(Range,10))+LARGE(Range,10)
> >
> > <chimi@comcast.net> wrote in message
> > news:036701c3d188$ed6cbdf0$a401280a@phx.gbl...
> > > Hi.
> > >
> > > I have a gradebook.  I have 16 assignments, and thus have
> > > 16 columns for scores.  However, I would like to include
> > > ONLY the highest 10 scores in the final grade.  In other
> > > words, how can I find the sum of the largest 10 values in
> > > a 16-cell range?
> > >
> > > Thanks!
> >
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.557 / Virus Database: 349 - Release Date: 30/12/2003
>
>


0
akyurek (248)
1/3/2004 12:14:45 PM
Reply:

Similar Artilces:

Count and Sum and Summarize
Item # PO # # of Action Date QTY remaining Boxes X4A341SB 9962 1 Out 4/16/2003 43 X4A341SB 9964 2 Out 4/16/2003 42 X4A341SB 9965 55 in 4/16/2003 41 X4A341SB 9967 6 Out 4/16/2003 40 89121 10001 1 Out 4/25/2003 19 BBK57911 10001 1 Out 4/25/2003 -42 CBK38161 10001 1 Out 4/25/2003 23 BBK33904 10001 2 Out 4/25/2003 -1 CBK48181 10001 6 in 4/25/2003 6 ABK03915 10001 8 in 4/25/2003 6 CBK03100 10008 1 Out 4/29/2003 49 CBK30000 10008 33 Out 4/29/2003 49 AB3L1011 10013 8 Out 4/29/2003 129 AB3L1011 10014 7 Out 4/29/2003 128 B48266 10015 4 Out 4/29/2003 32 B...

Sum query (single value) to appear in a text box
I am have a SUM query (which works okay). I want to display the value of that query in a read-only text box on a form. However, when I set Control Source for that text box to =[qry_theSumQuery].[summedField] it does not work for me. I do not see any other way to do this. Can someone give me some advise on how to do this? Thanks. - Paul Schrum one way is in the form's load event, put: Me.yourTextbox = nz(DLookup("summedfield", "qry_theSumQuery"),0) Damon <paul.schrum@gmail.com> wrote in message news:1172272653.613655.323170@a75g2000cwd.googlegroups.co...

error in sum formula result
I'm no Poweruser of Excel, just an occasional user with a little experience, but I have come across a strange behavior, and wonder if you have seen this before, and what might be causing it. I recently did a new spreadsheet to track some costs, and constructed a simple formula to extract a sum from three cells. The three cells each contained currency numbers, lets say 235.45, 100.44 and 95.51. The formula I used was =SUM(F13.F15), and the result was off by -.01. In other words the last three digits of each number, 5,4 and 1 should have yielded a 0, but in fact returned a 9. I chec...

Validating values of POEMDEV
Hi all, I've written a OEMUI.DLL. The basic code comes from the WINDDK sample. The values from the gui are stored in the POEMDEV. VERBOSE(DLLTEXT("UserName :<%s>.\r\n"), ((POEMDEV)pOEMDM)->szUserName); VERBOSE(DLLTEXT("CostCenter:<%s>.\r\n"), ((POEMDEV)pOEMDM)->szCostCenter); VERBOSE(DLLTEXT("ColorMode :<%s>.\r\n"), ((POEMDEV)pOEMDM)->bColorMode ? L"on" : L"off"); I like to find a place in this DLL, where I can verify the values of POEMDEV. Greetings Kai -- ISO Software Systeme GmbH Nuremb...

Search for values between two sheets
Hi again I think I've confused everyone with the previous thread. Is it at all possible to do something like a VLOOKUP or anythin similar to search between sheets/ workbooks I appreciate any help you can give guys Joe -- Message posted from http://www.ExcelForum.com Hi Joey I gave you one answer for two sheets. If you have several this becomes more complicated. What do you want to return? - text values - numbers For the latter one there could be a solution using SUMPRODUCT, etc. So you may explain this with a little bit more detail :-) -- Regards Frank Kabel Frankfurt, Germany ...

Filtering reports on null date value
I am trying to create a custom report in RMS Active Reports which will show me all items which have not sold since a specific date. I've got the following report filter in my .QRP file: Begin Filter FieldName = "Item.LastSold" FilterOp = reportfilteropBetween FilterLoLim = "8/1/2006" FilterHilim = "8/31/2006" FilterNegated = True FilterConnector = reportfilterbooleanconAND End Filter This filter will show me items which did sell at some time, but have not sold since August 1. That mean that the item.lastsold date will be poplated with ...

Counting Different Values
Well here we go, i have coloum B which has the name of the person and column F has the number now i want to have a summary page which has : Name & Number from the other page counted up. For Example: Robert Smith 10 Robert Smith XX Jim Smith 10 Jim Cox 10 Jim Cox XX Jim Cox 10 and on the summary page it has: Robert Smith - 10 Total: 1 Robert Smith - XX Total: 1 J...

Hiding Returned Value of hlookup
Okay, this is a tough one... (At least for me) I'm using the hlookup formula to import the starting times and ending times of employees on a daily assignment sheet. I'm compiling all the times onto a weekly schedule where each individual's hours for the week are added up. Now, I've done all this, but I get #NA when a particular employee is not on a certain day. I'm currently using IF and ISERROR to convert the #NA to "0", which allows the sheet to properly add up the hours being used by each employee. Now, I need to be able to hide the zeros so I can pri...

Help with counting values that have different endings
Hi guys, i just joined SQLMONSTER and was wondering if you guys can help me with this problem... I have 10 columns and 8,000,000 rows which have stock ticker names and exchange tickers-like the following: [Column A] AQR.L GDY.LS NBAE.LS (blank) 0023CA.S FOR.L AQR.KZ ... ... How do I find the number of unique ticker names in my database and disregard the .L, .LS, .S, .L, .KZ endings (because I am looking for the unique tickers, not the exchange). I.E., I want the values counted to be only AQR, GDY, NBAE, 0023CA, FOR or 5 values (I don't want AQR to be counted twice becau...

Pass a Report Value to a Form?
Hi guys, I want to run a report in the background, take a final value from the report, and pass it back into my form, to be displayed. I know I can easily do this from one form to another form, but as I already have the report, I was being lazy, and figured I could just take the value from the report using a macro.... but it doesn't seem to work. Is there a way of doing this? Or am I being stupid (as well as lazy)? Thanks! Russell. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200911/1 Pascoe, Well, it's...

how we can retrieve the value of edit control of combobox.
hi guys, how r u, can you please tell me how we can retrieve the value of edit control of combo box. actually i want to allow the user to input data in the combobox and then save this data in the database. i don't want to select already exist data in list box of combobox. please help me. raghunandan_1081@yahoo.com wrote: > hi guys, how r u, can you please tell me how we can retrieve the value > of edit control of combo box. actually i want to allow the user to > input data in the combobox and then save this data in the database. i > don't want to select already exist da...

Value List needs updating with 600 rows
Hi , I am attempting to get a list of 600 values added into a Value List without having to type them in...I have added them into the MSP_ATTRIBUTE_STRINGS table in a MS ACCESS copy of the project file however when I open the plan back up again in Project, the look up value list has not been refreshed..any ideas?? In Project 2007 you can cut and paste into the lookup table list from excel. That should be easier and safer than mucking around in Access. Isn't 600 values a lot to choose from? The users will have to scroll a lot. -Jack "Steve" <Steve@discussions....

Excel Formula
If it possible to return a value based on the color of a cell, by usin an Excel formula? I looked at =Cell() but didn't see the option. The cell is colored manually not using conditional formatting. The use of this would be to sort a list based on the color of a cell. thank -- Message posted from http://www.ExcelForum.com Hi you'll VBA code for this. Have a look at http://www.cpearson.com/excel/SortByColor.htm http://www.cpearson.com/excel/colors.htm for some ideas HTH Frank > If it possible to return a value based on the color of a cell, by > using an Excel formula? I ...

Frx: linked data worksheet summing error
We use linked data worksheets for some of our reporting. The column format displays both current period and YTD. The YTD column is not calculating the correct amounts from the worksheet. For example, if we were in period 12, it would sum the YTD column as follows. period 1: add 12x period 2: add 11x period 3: add 10x period 4: add 9x ....and so forth period 12: add 1x Obviously, this gives us a huge error in the ytd column. Anyone have an idea why this is occurring? We are using the /cpo format for the linked worksheet. Thanks ...

finding unique values and removing all duplicates
I have a spread sheet with 4 columns and 900 rows I am trying to identify the rows that do not contain a duplicate amount. For example I have rows with 100 100 97 97 96 96 98 101 I am trying to pull the list of 98 and 101. The spreadsheet is a list of invoices paid vs invoices unpaid if it is paid there are two rows with the same data if it is unpaid there is only one row. I am trying to find the unpaid invoices. Hi try searching this group with your header! "finding unique values and removing all duplicates " regards Paul On May 3, 8:39=...

sum 10-09-07
I am creating an Access database and I would like a field in a table to have a default that automatically gives the numerical sum of two other fields in the same table, BUT also maintain the ability to edit the field. I would like the field to just give the sum as a default, not a fixed value. Example: Field 1 is "A Amount" and Field 2 is "B Amount" and I would like the sum of "A Amount" and "B Amount" to show up in Field 3, but be editable/overwritable. It would not work for me to put this expression into the Default Value for Field 3 in the t...

macro for auto sum
I am fairly new to macros in general. Does anyone have an existing macro that I can use which will auto sum the existing value in a cell with a new value added to it......i.e. if cell B10 has a value in it of 55 and I type in 5 into cell B10 it will auto sum and leave 60 as the new cell contents ? the actual code would really help...or just steps to do this. thanks Option Explicit Private prevValue Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Address = "$B$6" Then .V...

Changing values in a row based on a cell in the row.
Hi, I have a little matrix 5R x 6C. All cells within the matrix have data validation in them to restrict the input to "1" or "0". It is OK to have mutiple selections of "1's" in the same row, except if the user happen to select a "1" for the sixth or last cell in the row. If that happens I would like the other five cells in that row to have a value of "0". Something like A B C D E F 1 1 0 0 1 1 0 ok 2 1 0 1 0 0 1 Not ok 3 0 0 0 0 0 1 ok 4 5 -- Casey --------------------------------------...

Question regarding fields in a datasheet containing the same value
Hello, A piece of the database that I created is used to scheduleappointments. I have a form and then a subform detailing the day'sevents. The subform is defaulted to datasheet view. The datasheet hasthese fields: start_time, end_time, appointment, & date_scheduled. Theproblem that I am having though is this: It is necessary for my bossto have double entries in any given days schedule. I would like to addthe ability so if there are two entries that have the same start_timethe times in that field will turn red. I attempted doing this throughconditional formatting but had no luck. I...

Can EXCEL list amounts that, when added, total to a specific value? How?
My task is to generate a list of all "6 value combinations" that total a collection of specific sums. Simple Example: All "2 value combinations" that, when added, =13 List: D701:D709, filled with 1,2,3,4,5,6,7,8,9 Solutions: 4&9, 5&8, 6&7 ( 9&4, 8&5, 7&6 would be considered duplicates for my task but I can deal with this manually if they can't be "programmed out" ). This would take care of one specific sum. Summary: If I have 10 specific sums for which to perform this, and must ...

Linear interpolation between nearest values -> must be able to extrapolate too...
I have a function to interpolate between its two nearest values in table. It must also be able to take the two largest and two smalles value pairs, and extrapolate from there with their slope. Any ideas, fixes, alternatives? Thanks in advance, Louis Public Function FindX(xRange As Range, yRange As Range, _ y As Range, Optional bAscend As Boolean = True) As Double Dim maxX As Double Dim maxY As Double Dim minX As Double Dim minY As Double Dim matchPoint As Long Dim matchType As Long If bAscend Then matchType = 1 Else matchType = -1 End If matchPoint = Application.Match(y, yRange, matchTyp...

Getting the "name=" (bit, picklist) value that is returned from SOAP
Hi all, I am getting several fields via a Web service request in javascript in an OnChange event to poulate other field on a case. One of those fields is a bit, and one is a picklist. Those nodes in the response come back with <attribute="new_active" name="true">1</attribute> for the bit field and <attribute="status" name="On Hold">3</attribute> How can I read the name value? I've tried selectSingleNode("//status").name with no luck. Thanks! I am doing a similar process, using a SOAP response message. I ge...

Representing null values in SOAP
I am trying to pass in a null value into a webservice.How can this be accomplished in the raw soap message that goes to the server? I have a previous message regarding this. Thank You Cem Marifet ...

which table Picklist integer value stored?
Hi there, Which database table pick list interger values stored? thanks Kyaw this query will list all the pick list integervalues and attribute name select AttributeName,AttributeValue,Value from StringMap // for account WHERE (StringMap.ObjectTypeCode=1) //for contact WHERE (StringMap.ObjectTypeCode=2) attributename will tell u about the name of the picklist attribute value is the intigers and values are what u can see on the form i think this will help u ...

Dynamic Value in Workflows
Hello, I am creating some workflows and I want to set a value in an Account Entity based on a value in the Opportunity Entity. I believe I should be able to use the Dynamic value wizard within Workflow manager - i.e. select Account and the filed and select = and then go to Dynamic Value and select Opportunity and the field that I want the value to inherit on change of status. However when I do this I can only select Account within the Dynamic Value picklist. Any ideas? Dynamic Values are only available to children of Parent entities. Since Accounts are at the top of the CRM food c...