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!!!

ANy help much appreciated!!!

- Rank scores formula
- Overall Score Formula
- Adjusted total based on importance of ranking score
- Rank scores with multiple tie breaks
- Ranking Scores by Cateqory
- Roto football scoring formula needed!!!
- Rank by 2 criteria
- Golf Score Formula needs tweaking
- Excel CHALLENGE...3 questions(indirect,rank,array formula)...
- HOW CAN I SORT A "RANKED" COLUMN WITHOUT LOSING THE FORMULA?
- Excel CHALLENGE...3 questions(indirect,rank,array formula)...
- Omit the bottom two scores:Formula Question
- Percentage score formula
- How to make formula
- Ranking a list with conditions
- Golf Score Formula needs tweaking
- Ranking scores/names from multiple sheets on summary page
- Rank Function of Microsoft Excel
- RANKING + SORTING formula
- Conditional Format & Sort Macro For Ranking
- Can Vlookup return multiple values?
- Ranking
- Pivot tables and ranking
- Rank scores by category

[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

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.

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!

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.

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

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!!!

=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

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

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

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.

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!

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

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

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?

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.

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

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

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

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 4what 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 4Looking 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

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.

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

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