Does anyone make their own table of stats? And if so...how?

[Jschmuck2]

So a friend of mine wants to make kind of a composite list of fantasy stats, replete with his own notes and the ability to move players up and down in the rankings...

I was thinking Excel would be the best program to do that in but maybe ya'll have a better idea.

[bigh0rt]

Excel is definitely the best program I've used to do this.

[pappymojo]

I would start with the Median Draft Position excel file someone else created and posted on this list. I had created a similar list based on average draft position and could have saved myself the work.

I create a list of about 250 players based on the draft rankings I can find online using excel. I rank them by average position. Then I compare players in groups of ten, moving players up and down as I think they should go. When this is done I have a total list that can be sorted by Average Draft Position or by my rank.

Then I use the Data>AutoFilter option and I group players by position (such as all eligible 3B). Then I compare players in groups of ten and again rank the players that way. 3B 1, 3B 2, 3B 3, etc.

Then I create a new workbook where I list players in columns by position as I had ranked them by position. Then I create two columns to the far right for all players, one for average draft position and one for my ranking.

Then I go through each cell and set every cell for each player to equal the cell in the column for all players by my rank. So BJ Upton in the 2B column is set to " =(AC35) " and BJ Upton in the OF column is set to " =(AC35)" and BJ Upton in the column for Average Draft Position is set to " =(AC35)."

BJ Upton in the column for all players, my rank is in Cell AC35. This way, during the draft, whenever a player is taken, I can simply overwrite their name as "XXX" in the column for my rank, and that players name in all of the other columns is replaced with the text XXX.

This way when it is my turn to draft, I can look at all of the columns and see which players are available. Who is best available 2B? Who is the best available player according to Average Draft Position? Who is the best available player according to my rank?

I really wish that I could control the cell color autofill in this same method. I would love it if instead of showing Alex Rodriguez as "XXX" I could have it show a completely black cell.

I haven't figured out how to do this yet.

It's a lot of work, very time consuming, but I'm a dork and it forces me to go over my lists over and over again. The main drawback is that it is a pain to reorder my rankings right before the draft. I generally don't do it. Instead, I'll just bold the players in the my ranking column that I think should move up.

Won my league last year. Should have won the year before. Plan on winning this year.

[Gags Sports]

There is a way to auto-fill the cell with black.

Go to one of the cells you would like to turn black when you type "XXX" in it. Then go to Format -> Conditional Formatting.

Choose: Cell Value is equal to: "XXX"

Then for that rule, click on the Format button and make it so that the preview screen shows black. Then click OK. You will need to then use the Format Painter (looks like a paintbrush and is next to the copy and paste buttons) to copy the conditional formatting of that cell to all the other cells you want to turn black when you type "XXX" in them.

Conditional Formatting is pretty cool, and it's even better if you have Excel 2007.

Let me know how you make out.

[pappymojo]

wow! thank you! that's exactly what I was looking for. Thank you very much.