How can I total the top 9 scores of 12 weeks in a sporting contest

I need to keep track of the scores in a quiz for 12 weeks running, then total 
each contestant's best 9 scores.

Is there any way to sum a column, automaticaly ignoring the 3 worst scores?


0
Burton (5)
1/17/2005 9:57:02 AM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
541 Views

Similar Articles

[PageSpeed] 4

=SUM(LARGE(A1:A12,ROW(1:9))

it is an array formula, so commit it with Ctrl-Shift-Enter.

-- 
 HTH

Bob Phillips

"Vance Burton" <Vance Burton@discussions.microsoft.com> wrote in message
news:914AED28-4FC3-42EF-AB5A-D8943147F795@microsoft.com...
> I need to keep track of the scores in a quiz for 12 weeks running, then
total
> each contestant's best 9 scores.
>
> Is there any way to sum a column, automaticaly ignoring the 3 worst
scores?
>
>


0
phillips1 (803)
1/17/2005 10:08:59 AM
On Mon, 17 Jan 2005 10:08:59 -0000, "Bob Phillips" <phillips@tiscali.co.uk>
wrote:

>=SUM(LARGE(A1:A12,ROW(1:9))

There are two potential problems with this formula.

1.  If you copy/drag it to another row, the row references in the ROW function
will change.  Usually, I have avoided this by using a construct like
ROW(INDIRECT("1:9")).  

However, I have JUST discovered that this problem can also be avoided by using
absolute references:  ROW($1:$9).  Can you think of any disadvantages to this
approach?  If not, it would seem preferable to the INDIRECT construct as it
saves a level of nesting.

2.  The formula will give an error if there are less than 9 entries.  If this
is undesirable, one could do something like:

=IF(COUNT(A1:A12)<9,"",SUM(LARGE(A1:A12,ROW($1:$9))))

(**array-entered**)


--ron
0
ronrosenfeld (3122)
1/17/2005 12:16:18 PM
"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:bnanu055i4l17neposhmhf133959ctver9@4ax.com...
> On Mon, 17 Jan 2005 10:08:59 -0000, "Bob Phillips"
<phillips@tiscali.co.uk>
> wrote:
>
> >=SUM(LARGE(A1:A12,ROW(1:9))
>
> There are two potential problems with this formula.
>
> 1.  If you copy/drag it to another row, the row references in the ROW
function
> will change.

Can't see that this is a problem given the OPs question.


>Usually, I have avoided this by using a construct like
> ROW(INDIRECT("1:9")).
>
> However, I have JUST discovered that this problem can also be avoided by
using
> absolute references:  ROW($1:$9).  Can you think of any disadvantages to
this
> approach?  If not, it would seem preferable to the INDIRECT construct as
it
> saves a level of nesting.

It may work okay, but again it cannot be an issue for one or maybe 100
formulas. This sort of 'efficiency' drive is rarely necessary IMO. When you
have a poor performing SS, look for improvements, but it does not need to
become gospel.

> 2.  The formula will give an error if there are less than 9 entries.  If
this
> is undesirable, one could do something like:
>
> =IF(COUNT(A1:A12)<9,"",SUM(LARGE(A1:A12,ROW($1:$9))))

Granted, this is more serious, but rather than blank it out with less than
9, it would be better to sum what we have, such as

=SUM(LARGE(A1:A12,ROW(INDIRECT("1:"&MIN(9,COUNT(A1:A12))))))


0
phillips1 (803)
1/17/2005 12:59:40 PM
> However, I have JUST discovered that this problem can also be 
> avoided by using
> absolute references:  ROW($1:$9).  Can you think of any 
> disadvantages to this
> approach?

If you insert a row somewhere in 1:9, the reference will change 
and the formula will return an incorrect result. It is thus 
preferable to use the INDIRECT function.


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



"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:bnanu055i4l17neposhmhf133959ctver9@4ax.com...
> On Mon, 17 Jan 2005 10:08:59 -0000, "Bob Phillips" 
> <phillips@tiscali.co.uk>
> wrote:
>
>>=SUM(LARGE(A1:A12,ROW(1:9))
>
> There are two potential problems with this formula.
>
> 1.  If you copy/drag it to another row, the row references in 
> the ROW function
> will change.  Usually, I have avoided this by using a construct 
> like
> ROW(INDIRECT("1:9")).
>
> However, I have JUST discovered that this problem can also be 
> avoided by using
> absolute references:  ROW($1:$9).  Can you think of any 
> disadvantages to this
> approach?  If not, it would seem preferable to the INDIRECT 
> construct as it
> saves a level of nesting.
>
> 2.  The formula will give an error if there are less than 9 
> entries.  If this
> is undesirable, one could do something like:
>
> =IF(COUNT(A1:A12)<9,"",SUM(LARGE(A1:A12,ROW($1:$9))))
>
> (**array-entered**)
>
>
> --ron 


0
chip1 (1821)
1/17/2005 3:43:17 PM
On Mon, 17 Jan 2005 09:43:17 -0600, "Chip Pearson" <chip@cpearson.com> wrote:

>If you insert a row somewhere in 1:9, the reference will change 
>and the formula will return an incorrect result. It is thus 
>preferable to use the INDIRECT function.

Ahh.  Thank you for that information.


--ron
0
ronrosenfeld (3122)
1/17/2005 4:35:53 PM
On Mon, 17 Jan 2005 12:59:40 -0000, "Bob Phillips" <phillips@tiscali.co.uk>
wrote:

>
>"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
>news:bnanu055i4l17neposhmhf133959ctver9@4ax.com...
>> On Mon, 17 Jan 2005 10:08:59 -0000, "Bob Phillips"
><phillips@tiscali.co.uk>
>> wrote:
>>
>> >=SUM(LARGE(A1:A12,ROW(1:9))
>>
>> There are two potential problems with this formula.
>>
>> 1.  If you copy/drag it to another row, the row references in the ROW
>function
>> will change.
>
>Can't see that this is a problem given the OPs question.

I agree.  I was writing more generally.

>
>
>>Usually, I have avoided this by using a construct like
>> ROW(INDIRECT("1:9")).
>>
>> However, I have JUST discovered that this problem can also be avoided by
>using
>> absolute references:  ROW($1:$9).  Can you think of any disadvantages to
>this
>> approach?  If not, it would seem preferable to the INDIRECT construct as
>it
>> saves a level of nesting.
>
>It may work okay, but again it cannot be an issue for one or maybe 100
>formulas. This sort of 'efficiency' drive is rarely necessary IMO. When you
>have a poor performing SS, look for improvements, but it does not need to
>become gospel.

As it turns out, Chip posted the way in which even the absolute reference
method can get messed up.


>
>> 2.  The formula will give an error if there are less than 9 entries.  If
>this
>> is undesirable, one could do something like:
>>
>> =IF(COUNT(A1:A12)<9,"",SUM(LARGE(A1:A12,ROW($1:$9))))
>
>Granted, this is more serious, but rather than blank it out with less than
>9, it would be better to sum what we have, such as
>
>=SUM(LARGE(A1:A12,ROW(INDIRECT("1:"&MIN(9,COUNT(A1:A12))))))
>

It might be better but I think which method would actually be "better" depends
on what the OP wants.  For example, if there are no valid values until there is
a minimum of nine entries, then either Blank, 0, or some informative message
(like "too few entries" or "only " & COUNT(A1:A12) & " entries") might be
"better".


--ron
0
ronrosenfeld (3122)
1/17/2005 4:40:54 PM
"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:4cqnu0tk1rcn5hvpuqrm39vjan4npltcik@4ax.com...
> On Mon, 17 Jan 2005 12:59:40 -0000, "Bob Phillips"
<phillips@tiscali.co.uk>
> wrote:
>
> >> However, I have JUST discovered that this problem can also be avoided
by
> >using
> >> absolute references:  ROW($1:$9).

> As it turns out, Chip posted the way in which even the absolute reference
> method can get messed up.

> It might be better but I think which method would actually be "better"
depends
> on what the OP wants.  For example, if there are no valid values until
there is
> a minimum of nine entries, then either Blank, 0, or some informative
message
> (like "too few entries" or "only " & COUNT(A1:A12) & " entries") might be
> "better".

I think that the one thing that can be taken from this conversation is that
generalisations are wrong in at least one case. Chip's point is valid, but
there are bound to be instances when inserting a row will want the range
covered to be increased, there are others when it will not.

So, as my old Tandem guru first said to me, it depends ... SO my better
depends on the OPs actual needs, as you rightly state, but so does yours.


0
bob.phillips1 (6510)
1/17/2005 10:58:39 PM
On Mon, 17 Jan 2005 22:58:39 -0000, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> wrote:

>I think that the one thing that can be taken from this conversation is that
>generalisations are wrong in at least one case. Chip's point is valid, but
>there are bound to be instances when inserting a row will want the range
>covered to be increased, there are others when it will not.
>
>So, as my old Tandem guru first said to me, it depends ... SO my better
>depends on the OPs actual needs, as you rightly state, but so does yours.

Concur
--ron
0
ronrosenfeld (3122)
1/18/2005 12:08:12 AM
Reply:

Similar Artilces:

Can't bring up web pages
Hello, A couple weeks ago, a particular workstation couldn't bring up webpages with IE v7. Then it cleared up on its own. Yesterday the same workstation had the problem again as well as another workstation on the lan. I don't know if this is related, but my workstation cannot access the main server even though the drives are successfully mapped and the antivirus won't update. Has anyone seen this type of problem? Could it be a known virus? Thanks for any help and God Bless, Mark A. Sam Internet Options... Connections Tab.... Lan Settings Button.... ...

how can i get the megastat add-in
I want to install the add-in "megastat" but don't know how to do it Hi EC, >I want to install the add-in "megastat" but don't know how to do it Perhaps you should contact the author direct: orris@butler.edu --- Regards, Norman ...

how many emails can outlook hold?
I just wanted to know if there is an amount to how many e-mails outlook can hold, or how much space it can hold? thanks for any info I can get!! Jacki Jacki wrote: > I just wanted to know if there is an amount to how many e-mails > outlook can hold, or how much space it can hold? thanks for any info > I can get!! > > Jacki Up to and including Outlook 2002, about 1.8GB. Outlook 2003, using new Unicode pst file format, almost unlimited. (You have to tell it that though.....) ...

How can I update endnote cross-references in Word 2007 ?
I created a long document with many endnotes and some cross-references to these endnotes. When I insert a new endnote somewhere in the document, the following endnote numbers change but not the cross-reference numbers. This seems to be a bug in Word 2007 as it worked perfectly in my previous Word version. How can I update endnote cross-references in Word 2007 ? Ctrl+A, F9 should work in all versions of Word. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Joe56" <Joe56@discussions.microsoft.com> wro...

Can't create file: FAX.TIF
I keep getting this error message when I try to open faxes that come down over our server. Here is the entire message. Can't create file: FAX.TIF. Right click the folder you want to create the file in, and then click Properties on the shortcut menu to check your permissions for the folder. I have checked the properties for the Inbox and for the Office Document Imaging and all of that is fine. We were able to open faxes fine, then one day a Smiley bar appeared in Outlook and then we were not able to open the faxes anymore. Also, I have removed Outlook and reinstalled. I hav...

Can't insert Page Break (Continuous)
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When I try to insert a CONTINUOUS page break, it starts the section on the NEXT page. Right now, my doc has only one section. I am trying to isolate one part in the middle of the doc as a separate section to change some settings. This problem does not occur if I try to perform the same task a few paragraphs up. ETA: My doc only has one section plus footers. OK, you're going to have to be more exact about what you're attempting to do as well as more specific about the structure of your document content as well a...

Can't sell or transfer out all of my investment
In Money 2002, I have 250 shares of 'stock'. If I attempt to sell or transfer out more than 150 shares, the program reports that my account balance would go negative at some point, and refuses the transaction. 150 shares, no problem. After selling 150 shares, if you go back into the portfolio, there are still 100 shares remaining. Try to move them at all, and you get that same problem. I've been scratching my head over this one for a while. This is definitely a bug. I have even tried deleting all transactions related to that investment and then re- entering them. No...

How can I retrive the contents of a comment box in microsoft excel
I have inserted comments, and now i want to get the contents of each comment in a cell. Is it possible? Rg The code below will take the comments from the active sheet and put them in column A (Address) and column B (Text) on a new sheet inserted into the book Sub ExtractCommentsToNewSheet() Dim wksActive As Worksheet Dim wksNew As Worksheet Dim lCnt As Long Dim rngComm As Range, myCell As Range Set wksActive = ActiveSheet Set wksNew = Worksheets.Add Set rngComm = wksActive.Cells.SpecialCells(xlCellTypeComments) lCnt = 1 For Each myCell In rngComm With wksNew.Range("A" & ...

How can I move emails from a Windows Mobile device to Outlook
Upgraded to MS Outlook 2007 Office Pro (alas!) on my Vista desktop. Before doing it i backed up all my critical emails, calendar, contacts etc to my Trion Pocket PC PDA running Windows Mobile 6. Outlook install went pear-shaped and corrupted the old Outlook file - and its backup. I eventually (thanks to having inadvertently signed up to Plaxo) got my contacts and calendar back into Outlook. BUT I can't move the emails from PPC back to Outlook - and all the software I've tried seems only to be interested in moving them from the desktop PC to the Pocket PC, not the other way round. Any i...

How can I remove an unused shortcut label from a Worksheet row?
I put a macro shortcut on Row 1 of a worksheet using Forms controls. Now I no longer need this shortcut; I've deleted the associated macro and am now left only with a labelled box which I cannot remove using the Help suggestions. -- tanda You should be able to delete it. Press the F5 key, click on Special, select Objects click on OK It should now be selected - press the Delete key to get rid of it. "tanda" wrote: > I put a macro shortcut on Row 1 of a worksheet using Forms controls. Now I > no longer need this shortcut; I've deleted the associated macro and...

can I create a Mailbox without creating a user y AD???????
Hi Anyone knows if is it possible to create a maibox without creating the User in the AD? If it's possible How can I do that? I have Exchange 2003 and Win Server 2003 Thanks Not sure what the purpose is - but try this: create a user with a mailbox. Second a test message to it. Delete the user. Now you'll have an orphaned mailbox and after mailbox cleanup runs it will be flagged as such. -- Bharat Suneja MCSE, MCT -------------------------------- "lmelm" <lmelm@discussions.microsoft.com> wrote in message news:B1F6AE7C-2480-4745-9393-F580F123EEC7@microsoft.com......

Is there any way a macro can be created for various print area selections?
Hi, I'm looking to see if anyone knows of a way to have a macro select a print area of a constant width (columns A thru K) but with a variable length. Our MRP system will dump our BOM's into Excel and all of the BOM's vary in the number of rows used. Some can be 3 pages while others will be 15 pages. Currently we have to set up a print area to capture the information we want printed under columns A thru K everytime we go to print a BOM. Please advise, Steve If you hit ctrl-end, do you go way past where you think the data should end? If yes, you could use the techniques at...

I can't find a calendar template for 2005 and beyond.
I want to display a calendar which will show at least the six months period, or preferably the entire year, on a single sheet to print. Is there such a thing? I thought it might be the Excel selection of calendar types, but they do not cover 2005, but stop in 2005. -- Doug Thomas Consultant in Effective Communication Hi I made one - when you are interested, then I can post it (zipped), when you do give your mail address. Actually there are 2 modifications, in english (simply a yearly calendar, you tupe a year in, and the calendar for this year is displayed), and in estonian (on separat...

Can't receive e-mail
Hi there We are using Windows Mail to read e-mail, but have now got a problem. When we attempt to read e-mail we get the following error message: - An unknown error has occurred. Account: 'pop3.blueyonder.co.uk', Server: 'pop3.blueyonder.co.uk', Protocol: POP3, Port: 110, Secure(SSL): No, Error Number: 0x80004005 We've re-set the Windows Mail settings, but still get this message. Can anyone help? Graham PS - We can send e-mail!!!!! The 0x80004005 error often indicates corruption of the mailbox on the server. In your browser go to https://webmai...

Can xml request go through firewall by using web service?
Hi, I am doing a course project now for practive xml. I heard from somewhere that xml request can go through firewall. currently in our school there is a firewall which only can log on by vpn when user is outside firewall. I want to create a web form which can save the data into the database which is inside the firewall. Do you think xml request can do that? can i create a asp.net web service then using xml request to save the data to the database by using web service? I really appreciate if you can give me some advice Thanks The transfer of data via a webservice would only be res...

2nd Request
The two tck_201_ fields are text boxes on a form that are being set by to calculated text boxes on a different form!subform. As you can see by the debug statements and results, the values will just not transfer. I have set the format for both of the TCK _201_ fields to Fixed! Any Ideas? Me.TCK_201_Done = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual Me.TCK_201_Tot = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_NumDates Debug.Print Me.TCK_201_Done Debug.Print Me.TCK_201_Tot Debug.Print Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual Debug.Print Forms!frmPT...

Printing labels, a second blank page is "printing" ?can i stop it
I put artwork for labels and selected the label maker and number that i am printing on. The printer feeds a blank sheet through and then prints on the second sheet each time. In publisher when I look at the document and do a print preview, it only shows the 1 page. What do I need to adjust to be able to print on every page? Now I have to put 20 sheets of labels in the printer to get 10 printed pages and I have to recycle the blank label sheets. ...

Please help
Problem: I can't get my Exchange 2000 migration wizard to communicate with an Exchange 5.5 server. Environment: On an "inherited" network, I have a newly built (by me) Win2K SP4 server running AD (only server in AD), and an old Exchange 5.5 server on a separate NT4 SP6a domain (PDC) - on the same subnet. I have removed TCPIP and have reinstalled SPs for Exch and NT4. I have 2-way trusts set up between the domains, and they can browse each other's files. DNS has been set up, and I have forward zones for both of the domains. There is no internal DNS on the NT4 domain....

Totals in main form
Hey Freinds!! i have form called Workshops based on Workshops Table and subform called Participants based on Participants Table.the relationship between the two tables is one to many. how to count number of males and females in each workshop and show them in the main form. thanks in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1 1. Open the subform in design view. 2. Show the Form Header/Footer (on toolbar/menu.) 3. Add a text box to the Form Footer section. 4. Give it this Control Source: ...

can no longer open office applications
Version: 2004 Operating System: Mac OS X 10.3 (Panther) In the middle of an automatic update (11.5.5, I think), I selected to stop the install as it was having problems loading. When I went to open a document necessary for work today, I found I could no longer open word, excel, or powerpoint. Office 2004 is still listed as a folder in my applications folder. I attemtped to automatically install the update from the microsoft site, but it states that I do not have the system requirements necessary for that update. Is there anything that can be done to get Office working again on my computer? ...

Xpath / XPathExpression equivalent of SQL's "Top" function
Is there one? Also in either xpath or XPathExpression whats the best way to get the full xpath of a node. As i am using xpath query of (//books/.......) So I would like to see what is before the /book tag? > Is there one? Yes you can do /books/book[position()<6] That will select the first 5 book elements in the books node. > As i am using xpath query of (//books/.......) > > So I would like to see what is before the /book tag? Hmm you can get the parent node once you get the child I guess. -- Victor Hadianto http://www.synop.com/Products/SauceReader/ "ree32"...

Graphing a cumulative count by month: Can it be done?
I am trying to not use any add-ins or BI tools. I'm wanting to use a pivot table to create a histogram (graph) that shows: A.) Count of new action items for the week B.) Count of action items resolved for the week C.) The cumulative count of unresolved action items for the week My columns are in a table called tblMain and I figured I could get by using columns [ID] (a unique ID number) and [DateComplete] (a short date). Filtering out the action items that weren't completed is easy enough using a "null" qualifier for the date field if it's not yet resolved and usin...

Is there any way of calculating a running total within a single cell in a column
Can anyone help with this query? e.g. I want to work out how many people are working at any time in a 24 hour period. The number of people rostered on differs each day of the week. Monday Time No. rostered on 6.00 2 7.00 2 8.00 2 9.00 2 10.00 (and so on) Can I add additional staff number in my 'no. rostered on column' by just entering the number into the cell. Say, if there were an extra 2 staff starting their 8 hour shift at 9. am. is there a way of just typing in '2' into that cell and getting '4'? What I do at the moment ...

How can I enarge the font size in a drop-down list?
1. How can I enarge the font size in a drop-down list? 2. How can I prevent someone from changing or deleting the formula in a cell? When I try to protect the cell it prevents the formula from being executed. Formulas still work when protected. If you are using data>validation you cannot change the font, you can use a macro that zooms http://www.contextures.com/xlDataVal08.html#Font -- Regards, Peo Sjoblom "BobH" <BobH@discussions.microsoft.com> wrote in message news:A63E65DE-CFB3-40FA-B1D8-0E576A436885@microsoft.com... > 1. How can I enarge the font size i...

OWA, can i put username without domain?
Hi all, I have 2 questions as follow: I just setup an exchange2003 as test. In owa, Do i have to put in my whole email address as the username or there is a way to shorten it to username only for login? I have this c1030af3 error when i try to get into public folders in ex. manager. But the problem solved by disable auto-protect in norton anti-virus. Is Norton conflict with exchange 2003? is there a fix for that? Thanks a lot all. Hi Derrick, Take a look at this KB Article: http://support.microsoft.com/kb/820378 It will explain how to edit the OWA Login.asp page to automatically i...