Overall/Final Rank from multiple ranked lists

Question

Lets say I have from 25 sources of the same lists of data of items that have been ranked.&nbsp;The data list has 5 items ranked from position 1 (best) to position 5 (worst). How do I create an overall rank list based on the input from 25 lists?&nbsp;For simplicity sake here is the list of data arranged in the following&nbsp; layout:&nbsp;&nbsp;&nbsp; Item&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Tom's Rank&nbsp;&nbsp; **bleep**'s Rank&nbsp; Harry's Rank&nbsp;&nbsp;&nbsp; etc&nbsp; etc&nbsp; &nbsp; Overall Rank&nbsp;&nbsp; Watch TV &nbsp; &nbsp; &nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Read Book&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp; Gardening&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp; Cleaning&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp; Cooking&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5&nbsp;&nbsp;Not sure if this is known as weighted average? Its a subjective list from 25 people with their rankings and I just need to calculate the final average ranking.&nbsp;Any Ideas?&nbsp;Thanks,&nbsp;Jay.&nbsp;

detlef_lewin · Answer

AVP68&nbsp;You can use AVERAGE() or MEDIAN() or MODE() to calculate a value for each item.I would suggest either MEDIAN() or MODE().&nbsp;

twifoo · Answer

AVP68&nbsp;You may use a helper column and an overall rank, like in the attached file.&nbsp;

avp68 · Answer

Detlef_Lewin&nbsp; I tried few of these and The MEDIAN and AVERAGE gives more accurate results and better suited. Thanks.

avp68 · Answer

TwifooYes, thanks for the file as I was thinking along the same line and it works. The RANK on the total SUM gets me the same result if applied on MEDIAN or AVERAGE in lieu of the SUM.&nbsp;Thanks.

twifoo · Answer

You’re welcome.

Forum Discussion

Overall/Final Rank from multiple ranked lists

6 Replies

Resources