Sports Statistics - Marginal Increase by Position

[Charlie33]

Hi All,

Long time reader here, but new poster. I'm working on a project for sports statistics and my Excel knowledge isn't advanced enough to find a good solution.

I have a list of football players and need to determine the difference in total touchdowns between each person by position.

I've attached a sample excel file to help visualize, but for example, if we have the following data:

Player----------------------Position--------TDs---------TD Rank--------1 Pick Out-----2 Picks Out

Aaron Rodgers--------------QB------------26---------------1----------------------1--------------13
Peyton Manning------------QB-------------25---------------2
Adrian Peterson-------------RB------------15---------------3
Andre Johnson--------------WR-----------13---------------4
Chad Henne------------------QB-----------12----------------5


If you take the first line (Aaron Rodgers), you can see that 1 pick out is the difference between his estimated TDs and the next person at the same position within the list (Peyton Manning). The field calculates the difference between the two values.

The "2 Picks Out" column gives a data value for the difference in projected TDs between the 2nd and 3rd best projected players at that same position. This value would still be in the Aaron Rodgers row under "2 Picks Out" and show the difference between Manning and Henne. So you see that the "2 Picks Out" column is 13 because Chad Henne is the next QB in the list and his projected TDs are only 12- far less significant than the #2 QB.

I'm trying to figure out a formula or macro that can help me calculate these values. It complicates things because I must tell Excel to find the next-best player at a specific position, etc.

Ideally, I'd like to be able to set up a macro where I can remove a row from excel (remove a player) and have the thing re-sort by statistic (TDs in this case), then update all of the relevant columns (1 pick out, 2 picks out, 3 picks out, etc.). Not looking to get that detailed in this forum (unless someone is motivated). At the least I'm looking for a versatile formula to calculate the difference between each projected stat related to one player.

This has been a great community for me. Thanks in advance!

Charlie

[DonkeyOte]

Not altogether sure I follow but based on the sample (.xlsx format) you might consider:

Please Login or Register  to view this content.

The above will create a unique positionrank key which you can use in your latter calcs (avoiding need for Arrays)

Please Login or Register  to view this content.

Using your sample file the above approach would generate the following results:

Please Login or Register  to view this content.

Were you to sort your data by position and then TD (rather than by TD) then things would be greatly simplified.