Welcome to part 7 of Excel-ing at Fantasy Baseball. If you haven’t read the other installments of the series yet, I’d recommend you start there (part 1, part 2, part 3, part 4, and part 5, and part 6), so you can see the material we’ve covered (and maybe even go back to the other article I wrote linked that showed some of the benefits of Excel), to see how powerful and useful a tool Excel can be in Fantasy Baseball. It is also vital as we move on this series that you understand the fundamentals of the previous articles. Finally, we have the beginning of your own spreadsheet built over the last few weeks that we will continue to build this week, so skip the first 6 parts at your own risk.
What?! Your league is not using Fantrax? Inconceivable! Check out everything Fantrax has to offer and I’m sure you’ll come around to our way of thinking.
Using Excel for Fantasy Baseball
Review/Recap
Part 1 we covered:
- the basics of formulas
- cell references and how to lock a cell reference
- some basic formulas such as:
- sum
- average
- count
- and how to download and import data into a sheet
Part 2 we covered
- Tables
- How cell references work
- How to name a table
- The ease of copying formulas within tables
Part 3 we covered
- Ways to look up data
- Vlookup
- Index-match
- Xlookup
Part 4 we covered
- Conditional Formatting
- Logic Formulas
- Importing tricky ranks
- Delinitation
- Text To Columns
- Combining text
- Cleaning up text
Part 5 we covered
- Updating ranks
- Cleaning up duplicates and some of the ways to do that (this one got a little messy)
Part 6 we covered
- Team pages
- Indirect functions
Today we’re going to change the ages piece of our sheet so it automatically updates and build a “shares” sheet so you can see how many, and in what leagues, you roster certain players.
Age
First, since players’ ages change (yaaa time!) and you don’t want to keep re-downloading the database spreadsheet, we’ll build a formula based on the player’s birthday. First, go here and download the spreadsheet so that we have a list of player’s birthdays.
Add a new sheet to your workbook called age, copy and paste that data in, and then make that a table and name it age. You can get rid of all columns other than player and DOB and then add an age column. In that column, we will add a formula to calculate the age. We start with the today() formula that returns today’s date.
Dates are an odd thing in excel – as they are simply a number, with 1 being January 1, 1990 (also making some projects with dates before 1990 really annoying, but that won’t matter here). Then we will subtract DOB and divide that total by 365. The formula will look like this: =(TODAY()-[@DOB])/365. Make sure your parenthesis are in the right spot. This will return the player’s age.
Now we will go to the ranks sheet and change the age column to look up this column using your preferred lookup formula. As always, I will use xlookup and it looks like this: =XLOOKUP([@Players],Age[Player],Age[Age],””,0,1).
Your conditional formatting will still hold, but now you’ll have up-to-date ages whenever you are looking at the sheet and, since all the other sheets pull from this rank sheet, those are updated as well.
Now for a shares sheet.
Shares
On your Ranks sheet, you have a column with every league you are in so that each league shows the owners of each league. So, you can build a shares table either on that page or create a new page. But – we’ll still start with it on this page.
First, we’ll build a formula to see how many shares you have. Since I play in both redraft and dynasty leagues I like to keep track of all shares, dynasty only shares, and redraft shares. Hopefully, you are consistent across your leagues with your team abbreviation so that it is just one name, but if not we will adjust for that.
So, to the right of the league columns at the end of your table add “all shares,” “dynasty shares,” and “redraft shares” columns. If you want to break it down further (best ball etc) you can do that, but we will just go with this structure and you can adjust as needed.
To figure this out we will use the countif function. Countif tells us how many instances of something occur given a condition. The condition here will be this: do you roster that player in league, ie is the name in the team column your team?
So in all shares the formula will be =COUNTIF(the column ranges of your leagues, your team name).
For your team name you can either put “Team abbreviation/name” as a text string in quotes, or you can create a table with all various team names in it/a cell with your one team indication and do a locked cell reference to that cell.
If you do have multiple abbreviations, either because you use “creative” names or for whatever reason, then you will need to do the countif for each of those and add them up.
=COUNTIF(Ranking[@[LOE]:[TGFBI]],$U$2)+COUNTIF(Ranking[@[LOE]:[TGFBI]],$U$7) etc (the U2 and U7 being where the various team names are in a table.
Next, you’ll do the same thing in the dynasty column, but only including the columns that are your dynasty leagues in the range that the formula(s) is(are) looking at. Finally, you can either do the same thing again in the redraft column (or others you are doing). Or, since it’s easier, you can just subtract the dynasty shares from all shares to get your redraft shares.
Ok – that’s part one. Now create another table – either on the ranks sheet or on a separate shares sheet.
On that sheet we’ll have a player column, all shares, dynasty, redraft, and, if you want, a column for each league. It is worth noting, that you can also not make this table if you want to simply search for any player on your ranks sheet and you will see in each column who rosters that player and see the total shares for player’s you roster. But – if you want to have it on a separate sheet where you can just search you can follow these steps:
The player column will be empty because you’ll type in the player every time. Then in each shares column write a lookup formula to pull the countif formula totals that we just built. Finally, if you have the league columns, write a lookup to show what manager rosters the player in each league using either a separate lookup OR – remember from last week – use an indirect in your xlookup formula to pull it in to copy it across each column with an indirect.
In my shares table like that it shows this for my leagues if I want to see where I roster Kris Bryant and who rosters him in every league:
There you have it! A master spreadsheet to manage all of your fantasy leagues. You can use this to do things like make a CSV to upload to rank your players for dynasty drafts in Fantrax, to analyze trades, to look at player distribution, and so many other things. If there is anything you would still like to know how to do in Excel or a spreadsheet that I didn’t address drop a comment below or get at me on Twitter @dynastyonestop.
Also, if you made it to the end – treat yourself with some custom Excel baseball swag here or here!
For more great Fantasy Baseball Check out Mick Ciallela’s Rest of Season Rankings! Mick was the No. 1 Ranker on FantasyPros.com for the 2020 MLB season.
Fantrax was one of the fastest-growing fantasy sites of 2020, and we’re not stopping now. With multi-team trades, designated commissioner/league managers, and drag/drop easy click methods, Fantrax is sure to excite the serious fantasy sports fan – sign up now for a free year at Fantrax.com.