Find Rank and Award accordingly

[Robin_]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Groups[/TD]
[TD="align: center"]1st Exam[/TD]
[TD="align: center"]2nd Exam[/TD]
[TD="align: center"]3rd Exam[/TD]
[TD="align: center"]4th Exam[/TD]
[TD="align: center"]Result[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]87[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]76[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]96[/TD]
[TD="align: center"]63[/TD]
[TD="align: center"]62[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]74[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]E[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]74[/TD]
[TD="align: center"]58[/TD]
[TD="align: center"]75[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance.

 

[barry houdini]

For only 5 rows you could use this array formula in F2

=LOOKUP(SUM(IF(SUBTOTAL(9,OFFSET(B$2:E$2,ROW(B$2:E$6)-ROW(B$2),0))>SUM(B2:E2),1))+1,{1,2,3,4,5;"First","Second","Third","Fourth","Fifth"})

confirmed with CTRL+SHIFT+ENTER and copied down

 

[Arithos]

I arrived at this. Using XOR's formula

=CHOOSE(MATCH(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);{1;2;3;4;5});"First";"Second";"Third";"Fourth";"Fifth")

 

[Arithos]

{1,2,3,4,5;"First","Second","Third","Fourth","Fifth"}

How would I go about adjusting this section for EU region use?
Normally I use ";" instead of ",", but here there is both.. Which only return the numbered value(when used), not the "names"

 

[XOR LX]

If we're fixed at 5 rows and 4 columns then this non-array formula is fine:

=INDEX({"First","Second","Third","Fourth","Fifth"},MATCH(SUM(B2:E2),LARGE(MMULT(0+$B$2:$E$6,{1;1;1;1}),{1,2,3,4,5}),0))

Regards

 

[Arithos]

If we're fixed at 5 rows and 4 columns then this non-array formula is fine:

=INDEX({"First","Second","Third","Fourth","Fifth"},MATCH(SUM(B2:E2),LARGE(MMULT(0+$B$2:$E$6,{1;1;1;1}),{1,2,3,4,5}),0))

Regards

I have so much to learn =) Using a matrix like this is cooll.

 

[XOR LX]

I have so much to learn =)

We all do, my friend!

 

[barry houdini]

How would I go about adjusting this section for EU region use?
Normally I use ";" instead of ",", but here there is both.. Which only return the numbered value(when used), not the "names"

You may prefer XOR LX's latest suggestion so perhaps a moot point but effectively {1,2,3,4,5;"First","Second","Third","Fourth","Fifth"} represents a 2 row by 5 column range like A1:E5 so if you enter 1 to 5 in A1:E1 and the relevant text values in A2:E2 then put this formula in another cell

=A1:E2

and then select cell, press F2 then F9 and you should see the required syntax for your region. You can also set it up as A1:B5 or use two separate array constants like

{1,2,3,4,5},{"First","Second","Third","Fourth","Fifth"}

 

[Arithos]

You may prefer XOR LX's latest suggestion so perhaps a moot point but effectively {1,2,3,4,5;"First","Second","Third","Fourth","Fifth"} represents a 2 row by 5 column range like A1:E5 so if you enter 1 to 5 in A1:E1 and the relevant text values in A2:E2 then put this formula in another cell

=A1:E2

and then select cell, press F2 then F9 and you should see the required syntax for your region. You can also set it up as A1:B5 or use two separate array constants like

{1,2,3,4,5},{"First","Second","Third","Fourth","Fifth"}

Never a moot point, this is learning !!

And I know the syntax now, its {1\2\3\4\5;"F"\"S"\"T"\"F"\"FiF"}

Not very intuitive, but now I know .

And this shows me a very easy way to create those arrays. ( I use to type them in, which contains the possibility of a human error in a greater degree)

Even learned what the "F2" button does when typing formulas =)

 

[Robin_]

Thank you very much all. I value you all and your solutions.
But for, now, XOR LX's #14 solution is more suitable for me.

Again thank you all again. God bless you.

 

[XOR LX]

You're welcome! Thanks for the feedback!