Looking for the best method to find each player's ranking then averaging them

Hello,

I'm trying to create my own ADP (Average Draft Position) rankings list. I'm compiling a list of player rankings from different experts. Each player will be assigned a rank from each expert. I'm trying to figure out the best way for excel to average those rankings for each player to create an ADP.

Here is an example. I'm trying to figure out a formula to use that will automatically calculate the ADP highlighted in yellow without me having to do it manually.


example.png

Also here is a sample spreadsheet that can be used too.
ADP Sheet.xlsx

I'm guessing the formula would search for the Player's name then reference column A in the same row it finds the name to get the values to add together than would divide by the number of times a ranking was found. I can do some of these steps manually but am unsure how to do them all together. A combination of VLookup and other functions perhaps?

Any help would be greatly appreciated as I'm stumped.

So close! Thank you so much. The only thing that needs a tweak is the dividing. The number shouldn't be the number of experts/columns but the number of experts that included the player in their rankings. Each column will not contain the exact same list of players. So one player may only be in one column.

Originally Posted by AliGW

Then this:

=SUMPRODUCT(($A$2:$A$6)*($B$2:$E$6=G2))/COUNTIF($B2:$E2,G2)

Slight correction, I think this should be

=SUMPRODUCT(($A$2:$A$6)*($B$2:$E$6=G2))/COUNTIF($B$2:$E$6,G2)

Changes in bold.

Per post #4, the denominator needs to be the "number of experts that included the player in their rankings"

To get this we need to count how many times the player's name appears in the grid B2 to E6, and that grid won't change based on the row.

As a test, if you replace Player C in cell B6 with Player F, the average for Player C should be 2.33, being (2+2+3)/3.

My formula in post #6 does this:

=SUMPRODUCT(($A$2:$A$6)*($B$2:$E$6=G2))/COUNTIF($B$2:$E$6,G2)

Another option:

=AVERAGE(IF($B$2:$E$6=G2,$A$2:$A$6,""))

Enter with Ctrl+Shift+Enter.

.

Originally Posted by SimonLock

Slight correction, I think this should be

=SUMPRODUCT(($A$2:$A$6)*($B$2:$E$6=G2))/COUNTIF($B$2:$E$6,G2)

Changes in bold.

This did it! Thank you all so very much!!