Formula for How to Find Average Position Rank in a List

[Props5102]

Hi All,

For full disclosure, I'm not even sure if the subject of my post is the exact way verbiage I should be using to ask for help. I'll try and be as concise as possible while describing what I'm trying to accomplish.

Attached below you will find a screenshot of the sheet I'm working with. I've asked 6 people to rank 20 different items in terms of their favorite to least favorite (The items in the columns- Poison 1, Poison 2, Blood 1, etc). I'm trying to figure out some sort of metric to determine what items rank the highest when you take everyone's lists into account. My head is so spun around trying to figure this out that I'm not even sure what approach to take. I'm very green when it comes to formulas in excel, but my intuition says to use some sort of average formula.

Any help would be much appreciated.

Thanks in advance!

 

[RobP]

Hi, welcome to the forum.

I played a bit, and came up with the idea of scoring each entry by means of their row number in the sheet. (so the best would be row2, or score 2, and the lowest rank would be rank 21 for row21).
I do that for each column of data and sum them together to get a total score.

I then made a column B, and put this formula inside to show the scores:

=SUM(IF($A2=$C$2:$C$21,ROW($C$2:$C$21),0),IF($A2=$D$2:$D$21,ROW($D$2:$D$21),0),IF($A2=$E$2:$E$21,ROW($E$2:$E$21),0),IF($A2=$F$2:$F$21,ROW($F$2:$F$21),0),IF($A2=$G$2:$G$21,ROW($G$2:$G$21),0),IF($A2=$H$2:$H$21,ROW($H$2:$H$21),0))

its a bit long, but at least you have a column of numbers that represent a "weight" of preference to each item.

I'm sure there other ways someone will come up with too ..

Rob

 

[Props5102]

Hi, welcome to the forum.

I played a bit, and came up with the idea of scoring each entry by means of their row number in the sheet. (so the best would be row2, or score 2, and the lowest rank would be rank 21 for row21).
I do that for each column of data and sum them together to get a total score.

I then made a column B, and put this formula inside to show the scores:

=SUM(IF($A2=$C$2:$C$21,ROW($C$2:$C$21),0),IF($A2=$D$2:$D$21,ROW($D$2:$D$21),0),IF($A2=$E$2:$E$21,ROW($E$2:$E$21),0),IF($A2=$F$2:$F$21,ROW($F$2:$F$21),0),IF($A2=$G$2:$G$21,ROW($G$2:$G$21),0),IF($A2=$H$2:$H$21,ROW($H$2:$H$21),0))

its a bit long, but at least you have a column of numbers that represent a "weight" of preference to each item.

I'm sure there other ways someone will come up with too ..

Rob

Hi Rob,

Thanks so much for this.

I just started dabbling with SumIf and CountIf type of formulas and I did dabble with trying to do something of this nature. I thought I was probably making it way harder than it had to be, but I'm glad that you went down the same path that I initially guessed would be a solution.

 

[RobP]

You’re welcome, glad it helped

 

[RobP]

Oh, and sorry , forgot to mention in col A I just made a list of your items, so that col B showed the score of each…0