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.

Groups	1st Exam	2nd Exam	3rd Exam	4th Exam	Result
A	85	87	45	76
B	26	45	85	78
C	85	96	63	62
D	48	49	85	74
E	47	74	58	75

<tbody>
</tbody>

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!