+ Reply to Thread
Results 1 to 2 of 2

Consolidating multiple rankings

  1. #1
    Registered User
    Join Date
    10-18-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1

    Consolidating multiple rankings

    First time post so I appreciate any help because this one is hurting my head!!!

    So I am trying develop a project priority list which consist of 10 projects (Project 1 - Project 10), I ask my peers to rank the 10 projects based on importance to the company and return them to me. I am now trying to take their rankings and consolidate them to a final ranking. Could someone please help me with the formula needed to accomplish this including tie breakers if not too difficult? Thanks in advance!

    A B C D E F
    Project Peer 1 Peer 2 Peer 3 Peer 4 Peer 5
    Project 1 1 3 2 9 5
    Project 2 2 4 1 7 6
    Project 3 3 7 3 3 2
    .
    .
    .
    .
    .

  2. #2
    Registered User
    Join Date
    04-29-2008
    Posts
    7

    Re: Consolidating multiple rankings

    The easiest way, I think, is to build a tiebreaker into an AVERAGE formula, then RANK the average.

    I'll write this with the vote of one of the peers as the tiebreaker, since with the small number of voters an absolute tie is possible using other methods that I know of. If peer 1 is the tiebreaker:

    Column H would be "project average", cell h2 would be "=average(b2:f2) + b2/10000", and spread h2 down the column. Column I would be "project rank", cell i2 would be "=rank(h2,h2:h#,1)", with "#" being the row of the last project. The 1 makes the ranking in ascending order.

    Dividing by 10,000 in column H makes the tiebreaker only matter when it's needed. You can use a different tiebreaker; just calculate it in column G (or another open column) and replace b2/10000 with g2/10000.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Consolidating rankings from 3 different sources
    By bighurt87 in forum Excel General
    Replies: 8
    Last Post: 07-23-2013, 12:40 AM
  2. Replies: 0
    Last Post: 03-20-2013, 11:18 AM
  3. converting weekly rankings into monthly rankings
    By sfinns in forum Excel General
    Replies: 6
    Last Post: 02-09-2012, 08:51 AM
  4. compiling multiple rankings
    By muzdog in forum Excel General
    Replies: 3
    Last Post: 11-03-2010, 04:11 AM
  5. How Do I Combine Rankings for Multiple Categories?
    By Howard in forum Excel General
    Replies: 1
    Last Post: 05-15-2006, 11:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1