Aug 04 2017
11:34 PM
- last edited on
Jul 25 2018
09:53 AM
by
TechCommunityAP
Aug 04 2017
11:34 PM
- last edited on
Jul 25 2018
09:53 AM
by
TechCommunityAP
Hi !
My Worksheet image is in Below Link. I also attach excel file.
My Excel Sheet Image
Aug 05 2017 05:23 AM
SolutionHi 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.
Aug 05 2017 06:02 AM
Jignesh,
my solution is in the same direction as Sergei's.
Aug 05 2017 07:26 AM
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
Aug 05 2017 07:53 AM
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.
Aug 05 2017 07:59 AM
Aug 05 2017 08:41 AM
You are welcome
Aug 05 2017 05:23 AM
SolutionHi 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.