Find Rank and Award accordingly

Robin_

New Member
Joined
Aug 29, 2014
Messages
13
Here we need to award each group as First, Second, Third, Fourth and Fifth according as the total marks they get in 4 exams. Addition of additional columns are not allowed. So, the Result column must contain words First, Second, Third, Fourth and Fifth as per the marks obtained by the respective group.

Groups1st Exam2nd Exam3rd Exam4th ExamResult
A85874576
B26458578
C85966362
D48498574
E47745875

<tbody>
</tbody>

Thanks in advance.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Here we need to award each group as First, Second, Third, Fourth and Fifth according as the total marks they get in 4 exams. Addition of additional columns are not allowed. So, the Result column must contain words First, Second, Third, Fourth and Fifth as per the marks obtained by the respective group.

Groups1st Exam2nd Exam3rd Exam4th ExamResult
A85874576
B26458578
C85966362
D48498574
E47745875

<tbody>
</tbody>

Thanks in advance.

Well, you asked for it, Paste this formula in cell F2, and drag down:


=IF(RANK(B2;B$2:B$6)=1;"First";IF(RANK(B2;B$2:B$6)=2;"Second";IF(RANK(B2;B$2:B$6)=3;"Third";IF(RANK(B2;B$2:B$6)=4;"Fourth";IF(RANK(B2;B$2:B$6)=5;"Fifth";"")))))&", "& IF(RANK(C2;C$2:C$6)=1;"First";IF(RANK(C2;C$2:C$6)=2;"Second";IF(RANK(C2;C$2:C$6)=3;"Third";IF(RANK(C2;C$2:C$6)=4;"Fourth";IF(RANK(C2;C$2:C$6)=5;"Fifth";"")))))&", "&IF(RANK(D2;D$2:D$6)=1;"First";IF(RANK(D2;D$2:D$6)=2;"Second";IF(RANK(D2;D$2:D$6)=3;"Third";IF(RANK(D2;D$2:D$6)=4;"Fourth";IF(RANK(D2;D$2:D$6)=5;"Fifth";"")))))&", "& IF(RANK(E2;E$2:E$6)=1;"First";IF(RANK(E2;E$2:E$6)=2;"Second";IF(RANK(E2;E$2:E$6)=3;"Third";IF(RANK(E2;E$2:E$6)=4;"Fourth";IF(RANK(E2;E$2:E$6)=5;"Fifth";"")))))

Will yied the results:
First, Second, Fifth, Second
Fifth, Fifth, First, First
First, First, Third, Fifth
Third, Fourth, First, Fourth
Fourth, Third, Fourth, Third

<tbody>
</tbody>


One issue is when they have the same scores, then they will get the same notation "First", however, "second place" first score after the top 2, will yield third, not Second. This is okay?

If you are NA, then replace the "dividers" within the formula with "," instead of ";" as I am using
 
Last edited:
Upvote 0
I think, I became unable to make you understand. I don't need those many Firsts and Seconds.....
I will make you clear.

Suppose we have an additional column in between 4th Exam and Result named Total

Then that total column contains... numbers
293
234
306
256
254

<tbody>
</tbody>
Now, we need to award the First, Second, Third, Fourth and Fifth based on the total. Now, at the end the Result column should look like
Second
Fifth
First
Third
Fourth

<tbody>
</tbody>

If we are allowed to add a column Total like this, then it is not a big deal for me. But, we are not allowed to add any additional column.
I hope, I made you clear.

Thank you.
 
Upvote 0
I think, I became unable to make you understand. I don't need those many Firsts and Seconds.....
I will make you clear.

Suppose we have an additional column in between 4th Exam and Result named Total

Then that total column contains... numbers
293
234
306
256
254

<tbody>
</tbody>
Now, we need to award the First, Second, Third, Fourth and Fifth based on the total. Now, at the end the Result column should look like
Second
Fifth
First
Third
Fourth

<tbody>
</tbody>

If we are allowed to add a column Total like this, then it is not a big deal for me. But, we are not allowed to add any additional column.
I hope, I made you clear.

Thank you.

Well, you did make it clear now -.-
Alot easier like this.. Give me a sec :)
 
Last edited:
Upvote 0
Hi,

Perhaps this, in F2:

=MATCH(SUM(B2:E2),LARGE(MMULT($B$2:$E$6,TRANSPOSE(N(INDEX(COLUMN($B$2:$E$6),,)))^0),N(INDEX(ROW(INDIRECT("1:"&ROWS($B$2:$E$6))),,))),0)

Note that ties will be given precisely the same rank. For example, if Groups A and C had the same totals, the results would be, from top to bottom: 1, 5, 1, 3, 4.

This is one of the standard ranking systems. Please say if you would prefer an alternative system to deal with ties.

Regards
 
Upvote 0
Still unsatisfied.

I need words than ranked numbers. Looking around the formula, I don't think, I can manage to insert any if condition or any function to convert those numbers into words.
 
Upvote 0
Still unsatisfied.

I need words than ranked numbers. Looking around the formula, I don't think, I can manage to insert any if condition or any function to convert those numbers into words.

Will there only ever be 5 entries to rank? It might well require VBA if not, since there is no decent shorthand method to tell Excel to return e.g. Seventy-First, etc.

Also, you didn't say whether the tie-break system was satisfactory.

I might also ask if there can ever be any empty cells within your range, as the formula would need a small adjustment if that were the case.
 
Upvote 0
Will there only ever be 5 entries to rank? It might well require VBA if not, since there is no decent shorthand method to tell Excel to return e.g. Seventy-First, etc.

Also, you didn't say whether the tie-break system was satisfactory.

I might also ask if there can ever be any empty cells within your range, as the formula would need a small adjustment if that were the case.


He wants it like this:
ASecond
BFifth
CFirst
DThird
EFourth

<tbody>
</tbody>

Without a "helping" column


And I would use VBA for this. Unless he has an assignement that requires him to report a formula

I use: =CHOOSE(MATCH(RANK(SUM(B2:E2);F$2:F$6);{1;2;3;4;5});"First";"Second";"Third";"Fourth";"Fifth")


where F is sum of rows, I thought it would be simple to implement SUM() intor rank "ref" area, but it was not.. Do you know how?
 
Last edited:
Upvote 0
Try this:-
NB:- The code will match duplicate Totals as equal places, where appropriate.

Code:
[COLOR=Navy]Sub[/COLOR] MG29Aug56
[COLOR=Navy]Dim[/COLOR] Rng             [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn              [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] n               [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
[COLOR=Navy]Dim[/COLOR] Tot             [COLOR=Navy]As[/COLOR] Double
[COLOR=Navy]Dim[/COLOR] K               [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]Dim[/COLOR] oWds            [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    Tot = Application.Sum(Dn.Offset(, 1).Resize(, 4).Value)
        [COLOR=Navy]If[/COLOR] Not .Exists(Tot) [COLOR=Navy]Then[/COLOR]
            .Add Tot, Dn
        [COLOR=Navy]Else[/COLOR]
            [COLOR=Navy]Set[/COLOR] .Item(Tot) = Union(.Item(Tot), Dn)
        [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
oWds = Array("First", "Second", "Third", "Fourth", "Fifth")
[COLOR=Navy]For[/COLOR] n = 1 To 5
    [COLOR=Navy]If[/COLOR] .Item(Application.Large(.keys(), n)) > 1 [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] .Item(Application.Large(.keys(), n))
            .Item(Application.Large(.keys(), n)).Offset(, 5) = oWds(n - 1)
        [COLOR=Navy]Next[/COLOR] Dn
    [COLOR=Navy]Else[/COLOR]
        .Item(Application.Large(.keys(), n)).Offset(, 5) = oWds(n - 1)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
 
Upvote 0
He wants it like this:
ASecond
BFifth
CFirst
DThird
EFourth

<tbody>
</tbody>

Without a "helping" column


And I would use VBA for this. Unless he has an assignement that requires him to report a formula

I use: =CHOOSE(MATCH(RANK(SUM(B2:E2);F$2:F$6);{1;2;3;4;5});"First";"Second";"Third";"Fourth";"Fifth")


where F is sum of rows, I thought it would be simple to implement SUM() intor rank "ref" area, but it was not.. Do you know how?

I know. And my formula can be adapted to give the actual words quite easily, without the need for a helper column.

I just need to know first if the data is fixed at 5 rows. It's all very well adding in a few text values to a formula, though it would soon become unfeasible if we were looking at ranking up to e.g. "fiftieth".

Regards
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top