YouTubersLoveExcel#40: Extract top 5 scores

I teach history to four different groups of students that, in my spreasheet, I have labeled "1", "2", "3", and "4." The number of the group to which each student belongs is listed in CK2:CK100

Each quarter, I give all students a test and list the corresponding score next to each student's name. The scores are listed in CM2:CM100.

The name of each student appears in CL2:CL100.

I am looking for the formulas to extract the highest five scores in each group starting at CS2. So CS2 would contain the value of the highest score obtained by a student in group 1, CS3 the second highest score in that group and so on through group 4.

I would be grateful for any suggestions.

Hi, i have some data with student names and scores. What i wanted to do is have a formula to retrieve the bottom 5 scores for that student. I have have a formula which works fine with duplicate scores. i.e the bottom 5 scores for TOM is showing as 20, 20, 26, 27, 34 which is the bottom 5 scores however i want the bottom 5 scores excluding duplicate scores, i.e
20, 26, 27, 34 and 40 (TOM'S Scores).

The formula i currently have is in the SCORE COLUMN (G6)

=IF(F6="","",SMALL(IF(B$6:B$27=E$2,C$6:C$27),ROWS(G$6:G6)))



Name Count Tom Bottom 5 Names Tom 8 5 Tom Fred Shela Name Score Name Score Joe Tom 20 Tom 20 Tom 60 Tom 20 Tom 34 Tom 26 Tom 27 Tom 27 Tom 74 Tom 34 Tom 26 Tom 20 Tom 40 Fred 23 Shela 54 Shela 70

So I tried to get this figured out earlier today, and even had some help from Blake over in the general forums but to no avail we're still stuck.

http://www.excelforum.com/excel-gene...rt-filter.html

^ original thread if you want to reference it.

I have an idea on the logic behind the code, but I'm not sure on the syntax to write it.

Basically the module would do a find function which would locate "100", using something similar to Ctrl+F's Match Entire Cell Contents ability. Upon finding the value, it would copy the value(100), the number in the cell directly to the left of it (which is the scores corresponding time), and paste those into a new sheet with the same header they were under in the previous sheet. After finding the first value of 100 in a column, the module would begin to process the next-next column over (every-other one). If a score column didn't contain 100, it simply gets skipped over.

I know there's going to be some IF logic in there but it's been about 4 years since I've coded so I know it'll be rough trying to write out the syntax for it.

In short I need to sort every-other column (the score columns) to figure out in which cases a participant hit 100, and if they did I need the time which they hit 100.

I've tried using a table and pivot table to do this but in both cases it's just pointing and clicking through the various values and with a master document of about 12000x400 its taking dreadfully long.

I've attached a a document with a small portion of the data. If any of the VB guru's out there can help you guy's would be life savers.

This is an NFL office pool, just trying to clean the code up a little, i figured out how to return the highest value in row 6 (total score for all weeks), but what i'd like to do is be able to add up the weeks without having the current value in weeks that haven't played yet (rows below the "JEFF S" row are doing what i'm trying to fix). If I can get help with this to at least return a 0 then i can make a conditional format for that cell to return white coloured 0, or even better to get a code that just doesn't display a value if = 0.

thank you kindly in advance

breal33

In my scoring sheet, I need to pull the names of the people with the two lowest scores of their ride.

Names are in Column D (rows 4 - 18). Their score is in column H (rows 4 - 18).

Once I get these identified, I will pull them to another sheet. All I need is to ID them (along with their score) lower in the sheet and I will then pull the names and scores to another sheet.

Thank you

I have a group of 100 agents who have to evaluate a telephone call and then select the type of call from a drop down list of 200 entries. Because all agents have the same chance of getting the same mix of calls, their selection mix should be relatively similar.

I want to be able to show using a chart agents that are deviating from the average but am not sure the best way to go about it.

Any thoughts?

There are scores in B1:B10 (10 scores). I want to take the top 8 scores. So I used this formula which works just fine.

=SUM(LARGE(B1:B10,1),LARGE(B1:B10,2),LARGE(B1:B10,3),LARGE(B1:B10,4),LARGE(B1:B10,5),LARGE(B1:B10,6) ,LARGE(B1:B10,7),LARGE(B1:B10,8))

But I'm trying to make the formula a bit more dynamic.

Is there anyway to have a set of data in B1:B50 (50 scores will probably be the most) and take the top x scores? X will be set in a separate cell (lets just say A1)?

Is it possible to make a top 5 scores section?
Ive been reading up on Arrays and how to use Arrays etc so i believe that it could be done with an array but i could be complete wrong.

what i have is players names in A4 and there averages in H4. i have 22 players listed but i want to take the top 5 scores from h4 and display them in a different section with there as so: NAME(A4): AVERAGE(h4) repeated 5 times and updates Automatically.

Any help on this would be great or if someone can point me towards a tutorials or just get me started on it or a start point would be awesome
thank you all :-)

I have golf scores each week which I need to average the last 6 scores, however not everyone plays every week, so there may be blanks, therefore I need a formula that will average the last actual 6 scores, not the last 6 columns

Anyone have any ideas?
Appreciate any and all help

5/6 5/13 5/20 5/27 6/10 6/17 6/24 7/1 7/8 7/15 7/22 7/29 8/5 8/12 8/19 8/26 9/2 9/9 Average 49 57 58 59 59 54 54 49 55 46 56 50 48 46 50.2 37 39 47 43 34 41 39 41 35 38 42 43 39 41 38 38 40.2 59 60 50 50 52 49 45 48 48 53 48 52 51 56 44 50.7 49 45 45 47 43 42 46 44 42 45 40 43 41 42.5 41 44 42 41 45 44 42 45 41 41 41 42 44 49 43.0 43 37 43 42 43 44 38 39 37 40 43 39 40 41 40 40.5 46 44 48 52 44 46 47 43 46 46 40 45 43 43 43 41 42.5

Greetings,

I need some help in creating a function.

Example:

A B
1 John 25
2 Mary 50
3 Tim 35
4 Todd 15
5 Nicole 5
6 Kathy 75
7 Nic 25
8 Justin 100
9 Harley 125
10 Mercedes 125

I need a function that will find the top 5 highest scores from column B
and return the corresponding name from column A. In the above example,
it should return Merceds, Harley, Justin, Kathy, Mary.

Can someone help? Thanks!