Welcome to part 4 of Excel-ing at Fantasy Baseball. If you haven’t read the first three installments of the series yet, I’d recommend you start there (part 1, part 2, and part 3), 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 3 parts at your own risk.
What?! Your fantasy baseball 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.
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 Excel-ing at Fantasy Baseball
Now we will dive into a few things today, starting with conditional formatting. To start, open your spreadsheet. On the Ranks page you should have something that looks like this with your league names at the end instead of 30 Rock:
Along the bottom you should have sheets for ranks, Cross 150, Cross top 500, Clegg top 500, Cross 300 prospects, and your league(s):
Conditional Formatting
Conditional formatting is a helpful way to see sets of data in color depending on what the data is and how you color it. You could make the cell be green or red depending on if something is good or bad in a formula, you can use it to highlight all values greater than, less than, or equal to a value.
Today we’re going to simply use it to get an idea of the ages of the players in a simple gradient. We’ll also do this when we create our league pages in more detail too so you can see ages on your roster, but for now we’ll do it on the Ranks page.
Go to the ranks page, click on the first row (2) of the “ranks” column. Then cmd+shift+down arrow to highlight that column. Finally, go up to the top on the home tab, go to conditional formatting – color scales -and pick one. Then go to the conditional formatting again, click on manage rules, double click on the formula, and then change the color to green for lowest and red for highest and click ok – it will look like this:
You might notice that even young players are yellow…. So let’s do some troubleshooting because they should be green.
If we hit the arrow we’ll see some players with very low ages. Let’s just see the players who are VERY young:
Obviously, these are clearly not their ages? So – let’s do a simple fix and here we’ll introduce logic formulas.
Logic formulas
So, it looks like IF the player’s assigned age is 0, 1, OR 3 – then we should probably just not have a number there because it’s wrong (alternatively we can pull in other birthday sheets from something like Fangraphs to find the birthdates and calculate ages – something we’ll probably do later) .
For now – let’s write an IF.
IF is a simple, yet helpful function. It runs a test, if it’s true returns one value, if it’s false it returns another.
I used an xlookup to get the ages so substitute your lookup formula in if it isn’t xlookup:
The function will be:
=IF(XLOOKUP([@Players],ThirtyRock[Player],ThirtyRock[Age],””,0,1)<5,””,XLOOKUP([@Players],ThirtyRock[Player],ThirtyRock[Age],””,0,1))
This says, if the age it looks up is less than 5, return a blank cell. If it isn’t, return the looked up value.
When you do that you’ll notice the color scales change.
Finally – the other logic functions are OR and AND.
OR does a logic test if EITHER value is true then do something. AND requires both values be true. These tests will return true or false – so they are often either accompanied by another formula, embedded in an if, or for a column of true or false.
At this point we don’t need either of them so we won’t write a formula, but play around with them, the notation is below:
Now, back to our regularly scheduled programming. But before we move on – one more not on conditional formatting. There are a number of options, and if you go to conditional formatting – new rule – style – classic – use a formula – with this you can craft a broader formula to make the coloring more complex if needed.
Less Friendly Formatted Ranks
Let’s figure out how to get ranks into this spreadsheet that AREN’T in a nice spreadsheet or that has some other variations like the data.
So, let’s hop over to Fantasy Six Pack and take a look at Dave Eddy’s over 1000 player deep dynasty ranks. Dave is the Tsar Director of Dynasty Baseball at Fantasy Six Pack and does some great work. First, let’s add a sheet to our workbook before 30 Rock (your league) and call it Eddy. Copy and paste the table into the sheet and make it an Excel Table (the top row is white text so it will show up when you make it a table – or change the font color).
But – you’ll notice – the way it’s formatted on the site has player, age, and team in one column, and since lookup formulas need EXACT matches, that won’t help us pull these ranks into our overall sheet. But fear not! This can be fixed. First, unless you want them, let’s delete columns C-E (if you want them move them somewhere else for now). Now highlight the player column, and then go to data – text to column. Make sure delineated is clicked and hit next. You’ll notice the formatting is that there is a “,” after the player name ends so click comma on and you’ll see a preview of what it will look like below and hit finish. It will look like this:
Now let’s delete column C and name the table Eddy.
Then go to the Ranks sheet, add an Eddy column after the Clegg ranking column, and then do a lookup to pull in those ranks.
Then we’ll edit the average formula so it includes the Eddy ranks too. I did by simply changing the column name at the end of the average formula. When you do that, it will change those values so let’s format them – sort again by top to bottom and then highlight the column, go to the home tab at the top, and change the style to number. It will look like this:
Now, let’s do this again with another rank that is also not formatted as conveniently, yet valuable and should be included. Let’s hop over to Imaginary Brick Wall here for the ranks.
Notice that he doesn’t have a table there. So, highlight starting right before the 1 before Acuña and go down until the last word under Rodolfo. Paste it into a new worksheet called Halp or Brick wall.
Your sheet will look like this:
Geeeez. Well that’s not great. So let’s turn back to our helpful text to columns.
Star with this, using ) as the wildcard delineation and turn off treat consecutive as one. It will look like this:
The end result still isn’t great. You’ll notice nothing column E on is unhelpful so delete all of that.
Highlight A1 to D 2018 and create a table (make sure my table has headers is unchecked). Then sort column 1 from least to greatest. Now go to the bottom (row 1011) and delete all the empty rows from the table.
But – you’ll notice that we still have spaces in front of first names and team names in there… this is because the “space” used online sometimes isn’t the same code that Excel recognizes as a space. So we will, in a bit, copy the space before Ronald and then go to find – find and replace and replace it with nothing. But before we do that we need to get rid of the teams. So first in column D we’ll go to Tatís and copy the space between Jr. and SD and then delineate text and copy that “space” in the gap flexible one. Then we’ll do it again in column C. Watch the video below BEFORE doing anything yourself:
Whoops – you’ll notice that doing that to column C replaces the Jr.s and other names in C. So first we’ll copy and paste C into D and then do the delineate in B again. Then delete column 4. Now find and replace (as outlined above) the space gap in too so that it is gone from the first name column.
There are still a few rows, at least on mine, that area is a little off. In row 922 for example just copy and paste those back into b and c. Then in the last one Jon Gray is a little odd, so just fix that so that the row is just 433 Jon Gray in the three cells. Then re-sort the order. There may be more of these we’ll find later, but for now, let’s just make sure Vlad is Vladimir so it matches the other ranks and a Jr. with Acuña.
At this point let’s also name the columns Rank, First, last, extra. And name the table Halp.
Now we need to make these complete names again. Let’s add a column named “name.”
Text combining
Whenever you have text across cells and you want it in one, you will use the & which means join text and then use “” around anything you want to add. So here we want the first name, a space, and then the last name. But, if we have a Jr., we want that too.
So we’ll go back to the IF function.
We’ll use this:
=IF([@Extra]<>””,[@First]&” “&[@Last]&” “&[@Extra],[@First]&” “&[@Last])
Let’s break it down:
IF – check if first is true, if it is, use the first option, if not, the second.
[@Extra]<>”” – This looks at column D and sees if it does NOT equal blank, an empty cell “”.
So, if it’s NOT blank, then give the first name, a space, the last name, a space, and the extra (mostly Jr., sometimes players like Hyun Jin Ryu).
If it is blank – give the first and last name with a space in between.
So now we have the full names! Now it’s important to note sometimes when doing things like this you may end up cutting a piece of a name. IE 595 the Rosa got cut off. So, add that with the La. Catch these as you go.
Lastly, go to Ranks and add a Halp column and put in the lookup and adjust the average.
It should look like this:
We’ll stop there today. If you have access to Baseball Prospectus go get those ranks (and notice how they use the accents, tildes, etc so you’ll need to adjust that). If you have other ranks go and pull those in.
We’ll be back next week with more to keep building your sheet!
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.