Hi !
My Worksheet image is in Below Link.
Capture2.JPG - Google Drive
Information About Worksheet and Competitions situation:
I am a school teacher. The worksheet is about to practical "Yogaasana" test of students'. In this practical exam there are four member of Judges Panel. The First main Judge is for decided about which "Yogaasana" is performed by the student. The other three judges are called Referee whose work is only for marking according to student's "Yogaasana".
The Heading name column has the names of students. There are three referees. All referee has a score sheet. In the score sheet there are seven cells. The referee must have to put every student's marks on respective cell. The marks are must be out of 10. I put "C" for First six column of each referee and put "O" for last seventh column. That means the student have to Do a first six "Yogaasana" compulsory as per the ordered by the First main judge. And the "O" means Optional "Yogaasana" which is performed by the student's as per his or her own choice. The Referee-1 Total, Referee-2 Total and Referee-3 Total column has the total of the student's obtained mark's out of 70. The Grand Total column has all Judges' Marks total out of 210. Final 2nd Highest column has second highest referee's Grand total.
Criteria of getting Top Five students' :
We give first place for that student who highest marks in the Final column. The Final 2nd Highest column has the second highest referee's Grand total. It means we ignore the highest referee and lowest referee's mark.
In case of Tie situation :
In case of Tie we use following method.
1) If the one or more student's marks are same than go to the Second highest referee's sheet and see only last Optional "Yogaasana" marks for give the student first rank. Means student A, Student B and Student C has the same mark than goto the second highest referee's sheet and see the last "O" columns marks. For example If A has 8, B has 7 and C has 9 marks than First ranker is Student C, Second ranker is A and Third one is Student B.
2) If the again one or more student has second highest referee's column "O" marks are same than go to the All referee's grand total column which has marks of out of 210. Than find the student's which has tie and give the first rank for that student who has highest All referee's Grand total. For example If Student A, Student B and Student C has the same marks in above condition one and their All referee's Grand total marks are 201 for Student A, 203 for Student B and 204 for Student C than the first ranker student is C, Second ranker is B and the third one is A.
3) If the again Tie than we decide manually by toss the coin.
I stuck at Large function :
I do the excel sheet as per my Excel knowledge but I stuck at Large formula. So, anyone can please help me for the same and solve my problem as per the above condition.
Thanks in advance
Ashu
My Worksheet image is in Below Link.
Capture2.JPG - Google Drive
Information About Worksheet and Competitions situation:
I am a school teacher. The worksheet is about to practical "Yogaasana" test of students'. In this practical exam there are four member of Judges Panel. The First main Judge is for decided about which "Yogaasana" is performed by the student. The other three judges are called Referee whose work is only for marking according to student's "Yogaasana".
The Heading name column has the names of students. There are three referees. All referee has a score sheet. In the score sheet there are seven cells. The referee must have to put every student's marks on respective cell. The marks are must be out of 10. I put "C" for First six column of each referee and put "O" for last seventh column. That means the student have to Do a first six "Yogaasana" compulsory as per the ordered by the First main judge. And the "O" means Optional "Yogaasana" which is performed by the student's as per his or her own choice. The Referee-1 Total, Referee-2 Total and Referee-3 Total column has the total of the student's obtained mark's out of 70. The Grand Total column has all Judges' Marks total out of 210. Final 2nd Highest column has second highest referee's Grand total.
Criteria of getting Top Five students' :
We give first place for that student who highest marks in the Final column. The Final 2nd Highest column has the second highest referee's Grand total. It means we ignore the highest referee and lowest referee's mark.
In case of Tie situation :
In case of Tie we use following method.
1) If the one or more student's marks are same than go to the Second highest referee's sheet and see only last Optional "Yogaasana" marks for give the student first rank. Means student A, Student B and Student C has the same mark than goto the second highest referee's sheet and see the last "O" columns marks. For example If A has 8, B has 7 and C has 9 marks than First ranker is Student C, Second ranker is A and Third one is Student B.
2) If the again one or more student has second highest referee's column "O" marks are same than go to the All referee's grand total column which has marks of out of 210. Than find the student's which has tie and give the first rank for that student who has highest All referee's Grand total. For example If Student A, Student B and Student C has the same marks in above condition one and their All referee's Grand total marks are 201 for Student A, 203 for Student B and 204 for Student C than the first ranker student is C, Second ranker is B and the third one is A.
3) If the again Tie than we decide manually by toss the coin.
I stuck at Large function :
I do the excel sheet as per my Excel knowledge but I stuck at Large formula. So, anyone can please help me for the same and solve my problem as per the above condition.
Thanks in advance
Ashu
