SOLVED

Large Function Duplicate Entry Error While Giving A Student's Rank

Copper Contributor

Hi ! 
My Worksheet image is in Below Link. I also attach excel file.
My Excel Sheet Image

 

  1. Information About 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". 

  2. Information About Worksheet : 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.

  3. 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. 

  4. In case of Tie situation :
    In case of Tie we use following method as per It's order. Means first use 1 If tie not solve than use 2.
    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. You see the formula I use in Formula bar of Image. So, anyone can please help me for the same and solve my problem as per the above condition.

    Thanks in advance
6 Replies
best response confirmed by Jignesh Parmar (Copper Contributor)
Solution

Hi Jignesh,

 

I'm not sure if you need to automate 1) and especially 3). As for 2) you may create one more Index like

=(2nd Highest)*10000 + (Grand total)

and put places based on it using your LARGE formula. In your sample Chris and Kevin have the same 2nd Highest and same "O", but based on Index Chris will be on first place since he has higher Grand Total.

 

Please see attached.

 

Jignesh,

 

my solution is in the same direction as Sergei's.

 

Wow ! It's great. Thank You. 

I got the solution. But I can't understand it. Can you explain it for me OR give me some answers of my above two questions.

1) Why are you multiplie by exact 10000. Not other than 10000.

2) Why you add again the Grand Total value (Out of 210) to the value of (2nd Highest*10000).

 

Kindly explain it briefly because I think it but I can't get it your logical thinking.

 

Thanks

Jignesh,

 

Detlef divides on on power of 10, that allows to show result more close to figures as you have for scores. But from idea point of view it doesn't matter.

 

That's how we understood your algorithm. In brief, you take the person with better 2nd Highest, and if some have the same result here from them you select with highest Grand Total.

 

As multiplier you may take any power of 10 which gives the number always larger than highest possible Grand Total. I took 10^4, since in your example that always more than any Grand Total. You may take 10^5 or 10^6, whatever.

 

Adding Grand Total to the result you have combined index. If 2nd Highest:s are different that doesn't matter what is at the lowest part of the index, it sorts results based on 2nd Highest. If they are equal higher result will be for the person who has larger =((2nd Highest)*10^4 + Grand Total).

 

Or, again, that could be =(2nd Highest + Grand Total/10^4). If only your marks as in the sample, not results of other calculations which is to be rounded then.

 

તમારો ખૂબ ખૂબ આભાર.
1 best response

Accepted Solutions
best response confirmed by Jignesh Parmar (Copper Contributor)
Solution

Hi Jignesh,

 

I'm not sure if you need to automate 1) and especially 3). As for 2) you may create one more Index like

=(2nd Highest)*10000 + (Grand total)

and put places based on it using your LARGE formula. In your sample Chris and Kevin have the same 2nd Highest and same "O", but based on Index Chris will be on first place since he has higher Grand Total.

 

Please see attached.

 

View solution in original post