Formula To Show Top Scores - Including Ties

Jul 17, 2008

I have a list of team members and their scores for team building events. I need to create a list of the top ten scores (including any ties). The list will have to show the top ten scores as well as any additional names whose score ties with one of the top ten scores.

I tried large and also a pivot table using the advanced but cannot get it to show the top 10 unique scores as well as any duplicate scores.

View 9 Replies


ADVERTISEMENT

Grouping Numbers To Show How Many Scores Are Between 30 And 50

Feb 14, 2012

if i had a group of numbers , for example, the scores would be 30, 40 , 50 , 60 , 70 and 80 , how would i group that. like it would show me how many scores are between 30 and 50 , it would show up as 3 , then between , 51 and 60 it would show 1

View 2 Replies View Related

Rank Formula Error With Same Values (Ties)

Feb 22, 2008

I'm having problem with ranking results (ascending) with similar values e.g. values 1, 2, 2 and 4 fails by use of Rank-formula (rank: 1, 2, #N/A and 4) as value 2 appears twice.

I've attached a simple example of the problem.

View 9 Replies View Related

Ranking Scores Formula

Jan 21, 2009

I have a table of data where i want to rank scores. I know the general formula for this would be =RANK(B2,$B$2:$B$7) as an example however the range of data is not next to each other and I have tried this formula =RANK(B2,B2,B4,B6) but this comes up with an error.

View 3 Replies View Related

Multiple Plus Formula: Add Scores Of Which There Are None

May 24, 2007

I have a grid set up to add scores of which there are none. The boxes each have a formula, ie: =IF(FIXTURES!D1="","",FIXTURES!D1 ) this produces a blank square until a score is registered. At the end of each column, down and across there is a formula for adding up the scores. Downwards is: = SUM(C2:C25), this works fine.

Across however is: =B3+D3+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3+AF3+AH3+AJ3+AL3+AN3+AP3+AR3+AT3+AV3 This doesn't work, just gives the answer TRUE. It did work before the original formula was added.

View 4 Replies View Related

Ranking :: What Formula Would Be Used To Rank Scores

Jul 31, 2007

what formula would be used to rank scores if you had the following

name score
participant 1 14
participant 2 27
Participant 3 19

to get this result

Participant 2 27
Participant 3 19
Participant 1 14

View 9 Replies View Related

Formula To Extract Test Scores

Apr 11, 2008

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.

View 9 Replies View Related

Complex Array Formula - Scores And Weightings

Oct 10, 2008

I'm basically creating a scoring worksheet, where there are a list of questions. Each question has a weighting as some are more impotant than others. Each question also has a range of answeres as some may not be clear yes/no answers. Also not all questions apply to all assessments so some may be left blank.

I basically need a formula which does the following to produce a total score:

[Total awarded score] / [Total available score of questions scored]

which equals

[(score <> "" / range)*weighting] / [if score <> "" sum weightings]

I've attached an example document where I think I'm getting close, just not sure if its possible.

View 4 Replies View Related

Formula: Looks Up A Chart Showing Scores Referenced Against Each Other

Apr 20, 2007

I have a formula that looks up a chart showing scores referenced against each other as shown below. (Scaled down version)
******** ******************** ************************************************************************>Microsoft Excel - Table Template.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1B1C1D1E1=
ABCDE12006-2007Altofts JnrsCarleton JnrsEmpire ColtsHemsworth Terriers2Altofts Jnrs- 3Carleton Jnrs12-3-4-0 4Empire Colts - 5Hemsworth Terriers -Results
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The problem is when the score is i.e. 12-3 the formula is only looking at 2-3. Hence this actually then shows as a defeat. The formula causing this problen is an array and shown as
=SUM(IF(NOT(ISERROR(LEFT(resultsHome,FIND("-",resultsHome)-1))),
--(LEFT(Results!$B3:$K3,FIND("-",Results!$B3:$K3)-1)>MID(Results!$B3:$K3,FIND("-",Results!$B3:$K3)+1,9))))
Question. How can the formula be changed to reflect scores like 12-3 to be shown as a home win.............
.

View 9 Replies View Related

Including 0 In Formula (#DIV/0!)

Jan 31, 2010

I am trying to create a marksbook that will ignore blank cells and calculate a percentage based on the amount of tasks completed.

So far I have managed to get it all working EXCEPT if I enter 0 as a value.

For example, suppose there are two tasks, both of equal value. If a person was to get 100% for the first task, and 0 for the second task, you would expect the total to be 50%. I cannot get my formula to recognise that a 0 has been entered.

(Cells F5:T5 are what the task is out of. Cells F6:T6 is the weighting. I want to be able to enter a 0 in cells F8:T8 to include it in the final percentage.) ...

View 9 Replies View Related

Find Top X Scores Out Of 50 Scores?

Aug 17, 2009

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)?

View 5 Replies View Related

Map Scores To Scaled Scores

Feb 13, 2008

I have a raw score for each case. I need to map those raw scores to scale scores. For example, a raw score of 0 to 0.49 maps to a scale score of 120, a raw score of 0.5 to 0.99 maps to a scaled score of 110, etc. There are a total of 13 possible scaled scores (120, 110, 100, ... 20, 10, 0). Is there a way to do this in Excel2003? (Note, if anyone on the list is 'bilingual' (Excel and SPSS), I'm looking for the Excel equivalent of the SPSS command RECODE raw (lo thur .49=120), (.5 thru .99=110), etc. INTO scale.

View 2 Replies View Related

Including Worksheet Tab In Formula

Jul 8, 2013

I have a workbook with numerous worksheets. Each worksheet is similar to all the others, and I want to be able to (for example) lookup the values in cell C10 for each worksheet and record the value in a summary worksheet.

The name of each worksheet is in Row 1. Is there a way to reference the sheet name using the values in row 1?

View 3 Replies View Related

SUMIF Formula Including OR

Jan 30, 2009

How do I modify my formula (below) to include an OR statement?

=SUMIF(I4:I20,"Core",G4:G20)

I need something like this:

=SUMIF(I4:I20,"Core" or "Existing",G4:G20)

View 9 Replies View Related

Averageifs Formula With And / Or Criteria Including Odd / Even

Feb 7, 2014

I have 3 columns of information. Column A will be between 1-6, Column B is between 1-10, Column C is the results that I need averaged. I need the formula to give me the average of Column C of all 1's in Column B that are even numbers in Column A. I have been looking on forums, and can find multiple criteria for ands, but can't see where I can do an and /or. I could do Column B = 1, Column A =2, but I need it to be OR =4 OR =6. I don't see any easy answer in excel for even/odds.

View 8 Replies View Related

Including Destination Cell Value In A Formula

Mar 28, 2009

There is a big range of cells with normal numbers (ex. 100, 150 .. etc), but I need to convert them in the following formulas that give the same numbers as a result: for example if the cell value is 100, I need to convert it in =if(iserror(100);0;100) and so on with all other values. Is it possible this to be done automatically for all cells?

View 5 Replies View Related

Comparison Formula - Including Inherent Errors

Oct 5, 2009

I have two columns that I want to compare - but one of them is the result of a VLOOKUP - so it may actually contain the #N/A error - how can I compare them so that:

if col 1 agrees with col 2, then "yes" is returned
if col 1 does not agree with col 2 but the VLOOKUP in col 2 has NOT returned an error (so it's still a valid comparison) then "NO" is returned
if col 2 shows a VLOOKUP #N/A error then "NO" is returned

or, perhaps more straightforwardly:

Col 1 = ABC, col 2 = ABC then return "YES"
Col 1 = ABC, col 2 = DEF then return "NO"
Col 1 = ABC, col 2 = #N/A then return "NO"

View 4 Replies View Related

Formula To Work Out Cost Including Multiple Discounts

Mar 31, 2014

complicated formula for working out prices including applying discounts.

I'm looking for the correct formula for G6,G8,G10 and G15,G17 and G19

I think I have them correct(?) to include standard days and extended days but I can't make it work to include Half days. The formula gets too complicated for me to follow.

View 2 Replies View Related

IF Formula - Get Average Of Only Values In That Range Not Including Blanks

Nov 21, 2011

I am looking for a formula for the following:

If there is nothing is U6:W6 return blank

Otherwise sum the contents of Bf6:BH6 and divide by the number of cells that are not empty in the range U6:W6 (to get an average of only the values in that range not including blanks).

View 3 Replies View Related

Formula To Return Multiple Criteria Including Stretch Target

Jul 8, 2014

I'm struggling to come up with a formula that ill give me the following:

I have will have a row of data (cells A4:H4 on the attached example), in the respective cells below each value in row 4 I want formula that will give me a value of 10 if it sees 90% in the cell above reducing to a value of 0 if it sees 80% in the cell above. I think that there should be a relatively straightforward formulae for that, my problem is that I want to add a further criteria that says; if the value in row 4 increases above 90% and reaches 95% then I must return a value that is 10 for 90% but "stretches" to 12 for 95%. Anything below 80% should return a value of 0, and anything over 95% should return a value of 12.

View 8 Replies View Related

Ranking - Ties

Jun 17, 2009

I've a problem with scoring using the rank formula.

Example: four players score, 28,17,39,6. The 39 score wins, gets 4 points, 28 = 3points, 17 = 2 points, 6 = 1 point.

The rank formula will work this out fine.

However if you get two scores tied, for example 28, 28, 39, 6, using rank the points score for each 28 returns 2 when I actually need it to be 2.5
(Thats 3points +2points / 2).

View 11 Replies View Related

Find Top X And Ties

Jul 6, 2006

Range("A4:D23") = Sheets("410").Range("A2:D21").Value '410
Range("F4:I23") = Sheets("305").Range("A2:D21").Value '305
Range("K4:N23") = Sheets("Trucks").Range("A2:D21").Value ' truck

The above code get the top 20 race car drivers, based on their points,from 3 sheets and puts them onto same sheet. Column D on each sheet has their point value.

Ie.
A B C D
1. Jeff Gordon 24 100
2. Tony Steward 20 92
3. Dale Earnhart 8jr. 91

my list will have over 100 Drivers already sorted in decending order by points. What I need to fix, is to select the top 20 drivers and any others who are tied for 20th place.

..Range("A2:D21") 'needs to be dynamic to select the top 20 and ties

the above will only get me the top 20. But if 5 drivers are tied for 20th place then I need then also. Remember the list is already sorted and the points are in column "D" starting in D2 and going down.

View 6 Replies View Related

Rank A List With Ties?

Jul 15, 2012

I'm trying to come up with a formula that assigns ranks to an ascending list of integer values where ties are resolved by assigning tied values the mean of the ranks they would have received if there were no ties, e.g., 117, 119, 119, 125, 128 becomes 1, 2.5, 2.5, 4, 5. (If the two 119s were not tied, they would have been assigned the ranks 2 and 3, yielding a mean of 2.5.) My list looks like this:

Column_A Column_B
1
1
1
2
3
4
4
5
...

View 6 Replies View Related

Breaking Ties In Rank Function

Jun 21, 2014

I am trying to break ties in the Rank function.

Example worksheet attached.

I have an eight digit number (a digit can show up more than once) and determine the rank of the sum of each number. I had the ties broken by whatever digit showed up first.

In the following example, June 13th row showed 6 digits tied for second place and the tie was broken by the order in which the digit show up in the range.

On the June 14th row it showed digit 4 and 5 tied with 3 occurrence each (running total for the two days). But digit 4 came in twice that day, and reach a total 3 first before digit 5, but Rank2 shows that digit 5 was ranked first, which should of been digit 4.

View 14 Replies View Related

How To Break Ties In The RANK Function

Dec 8, 2008

I am using the RANK function to rank the data in several rows. There are some times ties in the result.

I want to break that tie by taking the greatest value in a different cell in that row
Greatest value wins the tie.

same situation if there are multiple ties (4 "RANK"'s of position 2) ...

View 7 Replies View Related

Top 10 List Based On Criteria With Ties

Jul 10, 2012

Suppose I have a table of data such as the one below.

ABC
CarolineRed Team90
NatalieBlue Team13
AmyWhite Team42
AlexaBlue Team58
LaurenBlue Team75
CaitlinRed Team66
ChristyWhite Team42
AnnieWhite Team36
NicoleRed Team90

I am looking to create a sorted Top 10 list based on certain criteria with ties. For example, I would like to take the top values for White Team with both people with "42" at the top, since they are the top values.

I realize that there are not 10 values in the list above but I truncated the list for space purposes.

View 1 Replies View Related

Rank Function And Percentage Ties

Jun 23, 2009

I am a meteorologist and I keep track of my forecaster's monthly statistics. One area I track is percentage of warnings issued with desired lead time. I calculate and rank the individuals by percentages.

Example:
Forecaster A: 3 of 4 75% Rank 1
Forecaster B: 1 of 2 50% Rank 2
Forecaster C: 0 of 3 0% Rank 3

Or if ties:
Forecaster A: 6 of 6 100% Rank 1
Forecaster B: 3 of 3 100% Rank 1
Forecaster C: 1 of 2 50% Rank 3

Is there a way in Excel in case of ties, to award a higher rank (1) to the individual who went 100%, 6 for 6, compared to the individual who also earned 100%, but only 3 of 3.

More reward for more chances and opportunities.

View 9 Replies View Related

Sort By Rank Accounting For Ties

Jan 2, 2010

I have a custom sort order that I use for sorting military ranks in a number of massive spreadsheets I am using. Normally the data we get has a long version for the ranks (column 1) and a rank code (column 2). When the rank code is there it is simple to sort by that column in descending order, but without it I need a custom sort. The problem is there are up to three equivalent ranks at each level across Army, Navy and Air Force as you can see in the attachment.

Is there a way I can set up a custom sort based on both columsn of data but sorting only on the second column. I could do vlookups but there are up to 50,000 rows and I don't really want to keep that formula there, and as I use it across multiple sheets (yes I know all one sheet, but it is segregated data and used in different situations) including a vba written value would have to occur for every row on every sheet - not the best option in my opinion - maybe it is.

View 9 Replies View Related

Return Nth Largest Value But Ommit Zeros & Ties

Oct 4, 2009

I am trying to use the Large function to pull out First, second and third highest values in an array. problem is with ties and zeros it returns the wrong value.

View 4 Replies View Related

Ranking Visible Cells With Ties Without Skipping Rank

May 19, 2014

I have data that has filters and hidden rows and my current formula ranks with ties which is what I want, but I do not want the ranking to skip numbers.

Value Rank Now Rank I want
508 6
1001 1
696 4
704 3
833 2
557 5
1001 1
704 3
508 6

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved