Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Ranking scores Formula

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.

ANy help much appreciated!!!


Post your answer or comment

comments powered by Disqus
Hi to all

[Apologies if this post has already been attended to.
After posting the question, I could not download any headers and hence could
not see if my question was indeed posted (or answered). I tried twice so it
might have appeared twice!

Then due to a glitch at the service providers, all postings from Saturday
afternoon till Monday morning were lost including my post and possible
responses.

So please could somebody help me. If there were any responses, please
repost or send to me (jbircher@iafrica.com)]

If not seen before, here is the original query:

I would really like to enhance a formula:
In bridge (the game) the raw scores are ranked and a value assigned where
the top score will get the result of: (no of scores-1)*2 and the next will
get 2 less than the top. So for 9 raw scores you get 16, 14, 12, etc . . .
down to 4, 2, 0 as shown under the column headed SCORES (shared positions
share both score values)

Board Results SCORE Should be
1 500 16 4
1 -300 0 0
1 400 8 2
2 -200 3 1
2 450 14 4
2 -200 3 1
3 -50 6 0
3 430 11 3
3 430 11 3

In the column headed by "SCORE" I have the following formulae:
=(COUNTA(B$4:B$12,B4<>"")-RANK(B4,B$4:B$12))*2-COUNTIF(B$4:B$12,"="&TEXT(B4,"0.00"))-1

Up to now the formula has worked well as all the boards were all the same
number (say, all board 1 or all board 2 on separate sheets). However I now
wish to have a formula that applies across different boards (e.g boards 1, 2
and 3) all in the same column (A) and I want the formula to apply (match?)
only to similar board numbers giving the scores shown in the column headed
"Should Be" e.g. giving a score of 4, 2, or 0 (and again shared if tied)

My questions are:
Can MATCH and/or INDEX be incorporated into my formula and, if so, how?
Is there a better way of achieving my objective? (other functions?)
Is my original formula unnecessarily complex? Can I improve it?

Thanks in advance,
Peter
Kwazulu-Natal, SA

Hi Guys,

I need some help on this. Im trying to get an overall score by multiplying a raw score to a weighted percentage. Hmm...here, it goes something like this.

Percentage Weight 30% 10% 10% 50% - Total
Score 1 Score 2 Score 3 Overall
1.00 3.00 2.50 = (Score1*30%)+(Score2*10%)+(Score3*10%)/0.5

Note: Score 1,2 and 3 is using vlookup from a data dump.

Problem:

There are instances that either of the scores don't have any data on it (e.g #NA) that will somehow result a #Value error on the Overall Score(formula).

What i need to to do is that, if one of the score is blank or #NA the formula/function will just distribute the weighted percentage of the blank score to the remaining scores present then will divide it with the total of the weigthed percentage present as well.

Am i making any sense?

Hope you can help me with this.

In cases this is not quite clear please dont hesitate to reply so i can try to re-phrase it.

P.S.
Im not that good with excel.

Hi there,

I'm currently putting together an evaulation form to grade User Interface designs. I need some help creating a formula and it's not really something I'm familiar with.

I have several criteria that I am ranking out of 5 as well as an "importance" score depending on how important the particular criteria is to the user interface.

Eg:

Reading characters on the screen is easy - Score: 4 out of 5 - Importance: 5 out of 5
Uses Highlighting to help simplify task - Score: 1 out of 5 - Importance: 2 out of 5
Organisation of information is clear - Score: 5 out of 5 - Importance: 5 out of 5
Sequence/flow of screens is clear - Score: 3 out of 5 - Importance: 4 out of 5

For example 'Uses Highlighting to help simplify task' scores poorly (1 out of 5), however it's not really that important to the user interface (2 out of 5).

So what I need is a formula that can give me an adjusted percentage for the Score and Importance. The Importance score is like a Weighting (is that the right term?) to show a true percentage that can be compared with other user interface evaluations.

Hopefully this makes sense....any help or advice would be much appreciated!

I'm about to organize a competition where the rules are weighted to make the scoring as even as possible - giving the lowest ranking player the most advantage in the subsequent round. The anticipated result of this will be very close scores (and much excitement) needing multiple tiebreaks to sort them out.

Name Points 1st 2nd 3rd ....... 8th
John 77 5 2 .... 1
Fred 65 3 6 .....0
SAm 77 5 1 .....2
Rich 99 4 8 .....5

Now I think I'm halfway to my solution. I'm thinking the solution (I'm happy to use a helper cell) would be to add the points + weighted 1sts + weighted 2nds + weighted 3rds ...... weighted 8ths. This way I can rank the order of the helper cell. What I want is that no matter how many 2nd places there are they won't equal a 1st. No matter how many 3rds they won't equal 1 2nd place.... etc. In my example 2nd place would be John because of the tiebreak in 2nd places. The first part of my formula is:

this is where I come unstuck. 

If anyone can help out to 3rd or 4th place I think I'll be able to extrapolate to 8th places.

Hello Everyone-

This is a reposting of sorts-

As a newbe, I have learned a lot researching the topic of ranking (sorting) scores from the powerusers at this site and others, but I am still having trouble finding a formula that will result in a player's unique standing for a team:

I have the following sample data:

ColA
PlayerName
Player1
Player2
Player3
Player4
Player5
Player6

ColB
TeamName
TeamA
TeamB
TeamB
TeamA
TeamA
TeamB

ColC
PlayerScore
48
43
55
48
38
46

ColD
PlayerLeagueStanding
=RANK(C3,$C$3:$C$8) [Copy Down]

Results:
ColD
PlayerLeagueStanding
2
5
1
2
6
4

ColG
PlayerLeagueStandingUnique
=RANK(C3,$C$3:$C$33)+COUNTIF(C$3:C3,C3)-1 [Copy Down]

Results:
ColG
PlayerLeagueStandingUnique
2
5
1
3
6
4

ColE
PlayerTeamStanding
{=SUMPRODUCT(--($B$3:$B$8=B3),--($C$3:$C$8>C3))+1} [Copy Down and Use Ctrl-Shift-Enter For Array Formula]

Results:
ColE
PlayerTeamStanding
1
3
1
1
3
2

ColF
PlayerTeamStandingUnique
Formula = ????????? [Copy Down]

Results Should Be:
ColF
PlayerTeamStandingUnique
1
3
1
2
3
2

If anyone has a few minutes to help me out I would greatly appreciate it. Thank you very much for any assistance you can lend me.

Ron

I run a 10 team league which uses a 10-point rotissirie type system and need a formula that will automate this. here is a scoring example:

bob 25 9.5
bill 25 9.5
joe 23 8.0
jim 22 7.0
dan 21 5.0
hank 21 5.0
fred 21 5.0
etc....

I need a formula for the third column
If there were no ties...the scoring would be 10,9,8,7...1. When there are ties, the system adds the "non-tie" values and divides by the number of teams tied.
In this example, bob and bill tied at 25 points each, normally a 10 goes to 1st place and a 9 would go to 2nd place. To break the tie, I add 10+9 to get 19 ,, and then divide it by the number of players tied (in this example 2) to get 9.5.
As you can see, this can get very complex with numerous ties throughout the season. If all 10 guys were tied, I would add 10+9+8+7+6+5+4+3+2+1 to get 55 and divide by 10 to get 5.5 pts for all.
I hope i've explained well enough to ask for help...thank you much!!!

I have been using this formula to rank by group:
=SUMPRODUCT(--(N8=$N$8:$N$228),--(Q8>$Q$8:$Q$228))+1
but it ranks each group from 1 to x
How can I adjust this to rank 228 items from 1 to 228 - sorting by two criteria. I want to rank "leaders" first, then "members" than "NA", by the Score. Example below. I would like the Member to be 3, not start over at 1. THe number of leaders or members in any given time period could vary.

Status Rank Score
Leader 1 10.61
Leader 2 46.02
Member 1 25.22
Member 2 27.43
Member 3 28.99
Member 4 33.18
Member 5 37.80
Member 6 47.32
NA 1 23.87
NA 2 24.42
NA 3 27.06

I have been playing with the sumproduct, but to no avail. THanks in advance

Hi, I have a workbook for working out Net scores and Stableford points based
on players handicap, par of the hole and stroke index of the hole (link below)

I have had a lot of help from you guys with this(thanks) and would like to
call on you once more!
Hopefully it's just a formula tweak!
The problem I am having is that when my Gross score for a hole returns a
zero or minus Net score it won't show the points correctly
i.e.
1 over par= 1point
par=3
1 under par=2
2 under par=3
3 under par=4
4+under par=5
Hole 17 on the 19/04/2006 highlights the problem
(I didn't get a hole in one :-(, just hit the wrong key when I entered the
score, but I can dream!! Entering the correct score"2" has the same effect)
Hope it makes sense to someone.

http://www.flypicture.com?display=updone&id=rtD2lKXd

Thx in advance
Kev

ARE YOU UP TO THE CHALLENGE?

OKAY GUYS I HAVE A FEW QUESTIONS HERE TO ASK YOU EXCEL EXPERTS. THESE
ARE QUESTIONS I COULD NOT FIGURE OUT ON MY EXCEL TEST.

1. First is getting my INDIRECT function to work properly.

What I'm trying to do is refer to the row number in a specific column
in a seperate worksheet using the value in a cell.

='Menu Data'!C&(INDIRECT("B85"))

So I want to basicaly display the value(a text string) in column C in
the Menu Data worksheet, using the value in cell B85 as the ROW value.

2. I'm trying to use the rank function to rank the values in a column
in ascending order AND DESCENDING order. I got the Ascending to work as
follows:

=RANK(B51,B51:B59,0)...According to the excel help, to rank the values
in DESCENDING order I simply replace the 0 in the third argument with a
1. I tried this but it still ranks it in ASCENDING order.

3. The third and most challenging question is regarding what I would
imagine to be a wrather complex array formula.

Here is the text the question gives:

"Using Excel formulas, populate the following table for each keyword.
For the word APPLE, for example, you'll have to determine: (1) the
number of times it appears in the DESCRIPTION column on the MENU DATA
tab, (2) the average price of items that have the word ""apple"" in
their description, and (3) the MEDIAN price of items that have the word
""apple"" in their description.

HINT: you'll need to use array formulas for to fill out the AVERAGE and
MEDIAN columns."

I think I need to use the Find function...populate an array with the
price values of all descriptions that have the word apple in them. The
problem is, description is in column D and price is in colum E. So say
we find all the rows that contain the word apple, how do we then
populate an array with their corresponding E and ROW#?

In the end I will be populating a table that looks something like
this:

# of Incidences Avg. Price Median Price
Apple
Banana
Peach
Muffin

I'm at a complete loss on this one. The name of the worksheet that the
menu data is on is "Menu Data".

Thank you all in advance, any help will be greatly appreciated. If I
can figure this stuff out I may just be able to land a job doing these
fun things all day!

--
Mlowry
------------------------------------------------------------------------
Mlowry's Profile: http://www.excelforum.com/member.php...o&userid=25757
View this thread: http://www.excelforum.com/showthread...hreadid=391709

I have created a ranking sheet for sales performance that automatically ranks
based on sales numbers. The rank formula works fine, but when ever I try to
sort the data based on the numeric ranking the formula in the column loses
its integrity.

Are YOU up to the challenge?

Okay guys I have a few questions here to ask you excel experts. These are questions I could not figure out on my excel test.

1. First is getting my INDIRECT function to work properly.

What I'm trying to do is refer to the row number in a specific column in a seperate worksheet using the value in a cell.

='Menu Data'!C&(INDIRECT("B85"))

So I want to basicaly display the value(a text string) in column C in the Menu Data worksheet, using the value in cell B85 as the ROW value.

2. I'm trying to use the rank function to rank the values in a column in ascending order AND DESCENDING order. I got the Ascending to work as follows:

=RANK(B51,B51:B59,0)...According to the excel help, to rank the values in DESCENDING order I simply replace the 0 in the third argument with a 1. I tried this but it still ranks it in ASCENDING order.

3. The third and most challenging question is regarding what I would imagine to be a wrather complex array formula.

Here is the text the question gives:

"Using Excel formulas, populate the following table for each keyword. For the word APPLE, for example, you'll have to determine: (1) the number of times it appears in the DESCRIPTION column on the MENU DATA tab, (2) the average price of items that have the word ""apple"" in their description, and (3) the MEDIAN price of items that have the word ""apple"" in their description.

HINT: you'll need to use array formulas for to fill out the AVERAGE and MEDIAN columns."

I think I need to use the Find function...populate an array with the price values of all descriptions that have the word apple in them. The problem is, description is in column D and price is in colum E. So say we find all the rows that contain the word apple, how do we then populate an array with their corresponding E and ROW#?

In the end I will be populating a table that looks something like this:

# of Incidences Avg. Price Median Price
Apple
Banana
Peach
Muffin

I'm at a complete loss on this one. The name of the worksheet that the menu data is on is "Menu Data".

Thank you all in advance, any help will be greatly appreciated. If I can figure this stuff out I may just be able to land a job doing these fun things all day!

I have a list of seven people (golfers) who will have scores. For the
team scoring I would like the top-five of those seven to be
calculated and omit the bottom two scores. I would also like for a
specific one of those golfers to count regardless of their score. Is
there a formula that would do this?

Let me know if I need to explain this better.

A quick recap...
-I have seven golfers
-One of those golfers (preselected) counts in the team score no matter
what
-The two worst scores (outside of the one that counts no matter what)
must be ommitted.

Thanks for the help!

Todd

Hi

I have a worksheet that records the result of a customer satisfaction survey
we carry out each time a customer buys from us.

Each score is recorded as points out of a maximum of 90 with ten score
columns (C4-C14) on the worksheet. I have another cell (C3) which I need to
display the total score so far for that customer as a percentage figure.

So I need C3 to display a total of all the scores entered as a percentage of
the maximum possible score. The problem i've had is getting the formula to
only count the score cells that have a value entered.

Any ideas would be much appreciated.

Thanks
Simon

I have data Score for games as bellow :

Data

No Name Score Rank

1 Bill 10 5
2 flora 10 5
3 victor 30 3
4 Afti 23 4
5 Dizz 45 1
6 Samuel 37 2

I need to automatic data as below:

Rank Score Name

1 45 Dizz
2 37 Samuel
3 30 victor
4 23 Afti
5 10 Bill
6 10 flora

How to Formula in Name Field?

Good Evening,

I was wondering if I could get some help.

I am putting together an excel spreadsheet where I need to rank scores of students who are auditioning for an honor orchestra. However, this audition will also count toward reaching the all-state orchestra level. Students have already registered as one of the following...honor orchestra only(HO), honor orchestra and all-state(BOTH), or all-state only(AS).

All the students are listed on an excel database where their scores will be entered and tabulated. Then, using the RANK function, the overall ranking of their scores will be placed in a column so we can easily determine the order for their seats for only the Honor Orchestra.

However, I would like to not include in the ranking the students who are only auditioning for the all-state level. I have a column set aside for listing which group they are auditioning for. I used the following formula:

=IF(X5="AS"," ",(RANK(T5,$T$5:$T$49)))

This ranks the students, and leaves a blank for those students who are not eligible for the honor orchestra. This creates the problem, however, that any number left blank is still included in the order. Thus, the ranking returned may look like this....1,2,3,_,_,_,7,8.

We have a large number of students auditioning, so it will be difficult to determine the true ranking with these values just not printed in the cell.

My question is:

Is there a function (maybe COUNTIF?) that can be used to rank the students who meet the condition of auditioning for either the Honor Orchestra or Both but NOT the all-state orchestra only?

Thank you in advance for ANY help you can give me.

Hi, I have a workbook for working out Net scores and Stableford points based
on players handicap, par of the hole and stroke index of the hole (link below)

I have had a lot of help from you guys with this(thanks) and would like to
call on you once more!
Hopefully it's just a formula tweak!
The problem I am having is that when my Gross score for a hole returns a
zero or minus Net score it won't show the points correctly
i.e.
1 over par= 1point
par=3
1 under par=2
2 under par=3
3 under par=4
4+under par=5
Hole 17 on the 19/04/2006 highlights the problem
(I didn't get a hole in one :-(, just hit the wrong key when I entered the
score, but I can dream!! Entering the correct score"2" has the same effect)
Hope it makes sense to someone.

http://www.flypicture.com?display=updone&id=rtD2lKXd

Thx in advance
Kev

Hey everyone,

Here is what I am trying to accomplish for a class of mine...
Each student has their own profile sheet. Among other things it will have their name and score. Such that name is in cell A1, and score in B4 or something.

I would like to have a summary page that will display the top 5 scores and the corresponding names. I used the "=Large" formula to get the top 5 scores. Now I need to reference the name that goes with each score.

I'd also like the formula to automatically adjust for new sheets (students) being added to the workbook. If I have a constant beginning and end sheet this should be sufficient right?

Thanks so much...

Jon

Hello,
This has something to do with the Microsoft Excel's RANK function. In the table below,
Column A represents for example the scores by students.
Column B is the result using the RANK function of Excel.
Column D is their initial rank

What formula must be used to arrive at ranking in Column C?
Here, we can see that students who got scores of 27 has initial ranks of 2 and 3(see column D).
Getting the average of their ranks will become (2+3)/2=2.5.
Likewise for student who got scores of 13 has initial ranks of 5, 6, and 7(column D) therefore the average of their ranks is (5+6+7)/3=6.
How do I arrive at the ranking based on column C

A == B== C==D(initial rank)
32== 1== 1===1
27== 2 == 2.5==2
27== 2== 2.5==3
15== 4 == 4===4
13== 5 == 6===5
13== 5 == 6===6
13== 5 == 6===7
11== 8 == 8===8

Gud day,

I would like to ask on how to rank + sort using a formula.
A          B         C
2       Test 1       2
4       Test 2       3
1       Test 3       1
2       Test 4       2
5       Test 5       4
what i have already did is the ff:
I have rank the column A using the rank formula= (RANK(C1,$C$1:$C$5,1)) and then I have used the small formula =SMALL($A$1:$A$5,ROW(A1)) to sort the column A into Ascending. after having the list of sorted Rank.. the column after the sorted rank list, is the vlookup of the column B and Column C. but using this trick doesn't display the correct sorted list of column A,B,C. these is the result table of what i did.
 
A          B      C
1	Test 3	1
2	Test 1	2
2	Test 1	2
4	Test 2	3
5	Test 5	4
Looking at the column B it only repeats the value of Test 1. which gives wrong result.

See the attached .XLS for your reference.

Any comments/suggestion is very much appreciated.

I hope someone can help with this!

Thanks in advance,

Regards,
ivan

I am trying to create a macro with conditional formatting in excel. My spreadsheet contains a column of calculated scores. I have built a macro to sort the scores in descending order. Now, I want to build a macro that once my responses are in descending order it will take the number of responses and break out into 3 equal categories (by rank score) and shade the cell in 3 colors - - red, green, yellow. Can anyone help.

If values are repeated in the left most column of my vlookup table, how do I get Excel to return differing values from other columns? I'm rank ordering golfers according to score, lowest score (ranks 1st) to highest score. The Rank is the Vlookup first column, and if two golfers scores are the same, their rank is the same, yet the Vlookup function repeatedly returns only the first golfer's name. Is there a means for returning the name of the second/third/fourth etc golfer which has the same rank?

Example: Random golfers names, scores and ranking

A B C
1 Rank Score Name
2 2 74 Adams
3 5 76 Anderson
4 2 74 Christopher
5 1 72 Martin
6 2 74 Smith

Without using Sort function, since I would like the spreadsheet to be static, I wish to place golfers in rank order as follows on another section of the spreadsheet:

A B C
10 Rank Score Name
11 1 72 Martin
12 2 74 Adams
13 2 74 Christopher
14 2 74 Smith
15 5 76 Anderson

Formulas for Rank and Score are fine, but formula for Name returns Adams for each occurance of Rank "2", when need second and third occurrances of Rank "2" to return Christopher and then Smith.

Current formula being used which doesn't accomplish objective:

=VLOOKUP(A11,A2:C6,3,FALSE)

(When I went to post, spacing between columns went away; hope you can read.)

Thanks in advance for any assistance.

Hi,

I have a file where i am trying to rank and want to show only the top five ranking.

I am able to do this with conditional formatting.

I want the ranking to ignore the minus
before the figure and make it aboslute for the rank.

my formula : =RANK(E63,E$62:E$81,1)
whereas E63 is eg -10, E65 is eg +20

greetings,

Jack

I have a continuous Excel list of raw data that consists of company, district, revenue and month. Each month the list is updated by system downloads. The pivot table sums up the revenue by company by month.

I have added a Rank column/formula outside of the pivot table so it works fine when all the data is displayed. But once a pulldown selection in the table is picked (specific months, companies, districts) the Rank comes back as #N/A.

What is the best way to keep Rank order working? I have (unsuccessfully) tried to add Rank to the raw data or change the formula in the table; maybe I need a macro?

i have participants that belong to different categories
their names are in Col C, Category in Col D, Time in Col M
the order is random for names & category
can i rank withing each category?

mark


No luck finding an answer? You could always try Google.