Position or rank formula in excel 1

[vaughn9]

I need a formula that will give position or rank according to marks received. eg if a class received marks ranging from 20 - 90 I need a column next to the marks saying who came 1st, 2nd etc. I can sort the marks in descending order then I can fill down in the position column from 1 down but if more than one person gets the same mark then that does not work because if two people got a mark of 20 and the first person came 3rd, using my method the next person getting 20 would be 4th, when they should also be 3rd. Is there a rank or position formula that I can use that will give the result without resorting to sorting the marks and using fill down from 1 for the position.

 

[DaleWatson123321]

vaughn9,

Here's a set of two formulas which work:

With my example, I've placed these numbers in Column A

A2=20, A3=20, A4=20, A5=40, A6=40, A7=40, A8=45, A9=65

In Column B, enter the value 1 in B2

Enter the following formula in B3: =IF(C3=B2,C3,B2+1)

Enter the following formula in C3: =IF(A3=A2,B2)

Then copy these two formulas down for each row of mark results.

Column B will give you your desired results - i.e. if there are ties for any position, the formulas will generate the proper results.

Hope you understand this. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[jonsi]

Whilst Dale's answer above works (as all his answers invariably do ;-) ) you might be saved some time using the RANK worksheet function.

Again, using Dale's example above, the following statement in B2 would give the same results. Drag down for the fill.

=RANK(A2,$A$2:$A$8,0)

The Zero following the range Ranks the results in descending order, omitting or putting in another non zero Integer gives the results in ascending order.

hwyl
Jonsi

 

[vaughn9]

Could you explain about the 0 interger meaning descending or ascending for me please. I am not quite clear on that

 

[DaleWatson123321]

Jonsi,

Thanks for kind comment about my postings! ...And THANKS for your "high RANK-ing" solution. The RANK function is a GOOD one to know about, and worthy of a "STAR".

Interestingly, the RANK solution provides the "correct" results in a situation where the teacher/examiner wants to "skip" ranks, based on the number of participants who "tie". This of course takes into account the total number of participants.

In my example, using your RANK function - as follows =RANK(A2,$A$2:$A$9,1) ...produces the following results: The three 20-mark scores are ranked as "1", the three 40-mark scores are ranked as "4", the 45-mark score is ranked "7", and the 65-mark score is ranked "8". These results are possibly "preferable" in the majority of situations.

In some situations, however, I suppose the teacher/examiner might want to use my alternative solution. It shows the three 20-mark scores as ranked "1", the three 40-mark scores ranked as "2", the 45-mark score ranked "3", and the 65-mark score ranked "4". It therefore doesn't "skip ranks".

Vaughn9,

If you use Jonsi's RANK function with my example as described above - =RANK(A2,$A$2:$A$9,1) - the "ranking order" will start with "1" at the top. - i.e. the marks will be ranked from "top to bottom".

If, however, you change the "1" to a "0" - i.e. =RANK(A2,$A$2:$A$9,0) - then your "range of marks" (A2 to A9 in this case), will be ranked in "reverse order" - bottom-to-top. Thus, the 65-mark will be ranked "1", the 45-mark will be ranked "2", the three 40-marks will be ranked "3", and the three 60-marks will be ranked "6".

Hope this helps. Please advise if there are still questions.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

A little too quick ...Correction on that last example...

...It currently reads "and the three 60-marks will be ranked "6".

...It should read: "and the three 20-marks will be ranked "6".

Sorry for the mixup. I hope this NOW makes sense. If you try the formula, you'll see that it generates the same results as the above example.

Let us know how you make out, and it will ALSO be interesting to see which of the two alternatives is preferable in your situation - i.e. Jonsi's solution or mine.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[euskadi]

If you don't want to skip ranks as Dale suggested you might, do this (array function), and fill down.


{=SUM(IF(A$2:A$21>A2,1,0))+1}