• 581K Members
• 9,981 Online
• 703K Conversations

Overall/Final Rank from multiple ranked lists

Highlighted
Occasional Contributor

Overall/Final Rank from multiple ranked lists

Lets say I have from 25 sources of the same lists of data of items that have been ranked.

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?

For simplicity sake here is the list of data arranged in the following  layout:

Item                Tom's Rank   **bleep**'s Rank  Harry's Rank    etc  etc    Overall Rank

Watch TV        3                  1                   2

Gardening       5                 4                   3

Cleaning          4                 3                   4

Cooking            1                2                     5

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.

Any Ideas?

Thanks,

Jay.

6 Replies
Highlighted

Re: Overall/Final Rank from multiple ranked lists

You can use AVERAGE() or MEDIAN() or MODE() to calculate a value for each item.

I would suggest either MEDIAN() or MODE().

Highlighted

Re: Overall/Final Rank from multiple ranked lists

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

Re: Overall/Final Rank from multiple ranked lists

@Detlef Lewin  I tried few of these and The MEDIAN and AVERAGE gives more accurate results and better suited. Thanks.

Highlighted

Re: Overall/Final Rank from multiple ranked lists

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

Thanks.

Highlighted

You’re welcome.
Highlighted

Re: Overall/Final Rank from multiple ranked lists

@Twifoo  Further to this, I have a final step here on the next tab sheet I just want to show the item, rank, and average summaries in ascending order by item. So what I am trying to do is on the Summary tab is have a formula in column A that will lookup the Data tab in ranked column B  and populate the Summary tab in ascending order. Likewise similar formula in column B & C.  Will this be a Index/Match with Indirect or similar?

See attached modified file.

Thanks,

Jay

Related Conversations
How to move a list to another site?
alfie_s in SharePoint on
2 Replies
Format list view => icon if attachment
chhe5 in SharePoint on
1 Replies
Resulting NaN when calculating between two dates
JunHo_KIM in SharePoint on
1 Replies
List column customization
chinmaykulkarni in SharePoint on
1 Replies
How to manage multiple lists
BangorKeith in SharePoint on
2 Replies