Excel Formulas (1 Viewer)

[Rhythmdoctor]

I'm not sure if this thread has long-term sustainability, but it might.

Let me back up a bit. In another thread, I was reading some interesting opinions/data from EBF regarding BMI. BMI has never been on my radar as far as potentially predictive indicator. Being an analyst by nature and by profession, last season (my 2nd year in a dynasty) I started a spreadsheet where I'm tracking WR combine/pro day metrics. Along with Name, height, weight, draft position, team and some other calculated metrics I came up with.

So, tonight I added a new column for BMI and created a formula for it. I figured some other stat geeks on here could use it. I started thinking about what thread to post it in but then figured it might be a good idea for excel equations to have it's own thread. I dunno.

That's a lot of words to post the formula I created for BMI in excel. I already have my sheet formatted where height reads as 6-3, for instance or 5-10 etc... Since the BMI formula requires height in the form of inches... I did the following (below). Cell column identifiers and matching names (per my sheet) below:

Column A - Player Name

Column B - Height

Column C - Weight

Formula - =703.06957964*(C2/((LEFT(B2,1)*12+IF(LEN(B2)=4,RIGHT(B2,2),RIGHT(B2,1)))^2))

 

[Ignoratio Elenchi]

You could replace this:IF(LEN(B2)=4,RIGHT(B2,2),RIGHT(B2,1))with this:RIGHT(B2,LEN(B2)-2)

 

[Statcruncher]

Not trying to be a jerk here, but I don't see what value could be derived from an arbitrary and highly flawed calculation like BMI. I do appreciate you taking the time to share formulas and help other folks out.

 

[cdubz]

Not trying to be a jerk here, but I don't see what value could be derived from an arbitrary and highly flawed calculation like BMI. I do appreciate you taking the time to share formulas and help other folks out.

I think most of the criticism about BMI is that it doesn't accurately reflect who is truly overweight or obese. Not exactly what we are looking for when it comes to predicting future performance. If you look at top WR production last year, it does seem that there might be a relationship between weight and production. So not that BMI would help predict anything, but worth giving a shot.

 

[Statcruncher]

BMI comparison

 

[cdubz]

BMI comparison

How many of the first type are NFL players? We are dealing with a select subset of people that share very similar characteristics (within a position). When it comes to data analysis, how does checking this hurt? If there is no statistical significance then you throw it out.

 

[Rhythmdoctor]

You could replace this:IF(LEN(B2)=4,RIGHT(B2,2),RIGHT(B2,1))with this:RIGHT(B2,LEN(B2)-2)

Good call. Mine "looks" cooler though

 

[Rhythmdoctor]

BMI comparison

How many of the first type are NFL players? We are dealing with a select subset of people that share very similar characteristics (within a position). When it comes to data analysis, how does checking this hurt? If there is no statistical significance then you throw it out.

I was going to respond to Statcruncher but you said it perfectly.

 

[Ignoratio Elenchi]

You could replace this:IF(LEN(B2)=4,RIGHT(B2,2),RIGHT(B2,1))with this:RIGHT(B2,LEN(B2)-2)

Good call. Mine "looks" cooler though

I like to keep things concise. To each his own.

 

[BaBastage]

You could replace this:IF(LEN(B2)=4,RIGHT(B2,2),RIGHT(B2,1))with this:RIGHT(B2,LEN(B2)-2)

Good call. Mine "looks" cooler though

Hopefully you won't think I'm being snide or condescending, and I commend you for using Excel's formulas. It's one step of a journey that opens up a whole new world of what you can do with numbers.But after you work with a bunch of these, especially in complex spreadsheets, you'll appreciate the abridged versions, which are much easier to understand. Think of it this way... if yours is the only formula in a spreadsheet, it's easy to remember, "Hey, that's my formula for BMI", and you don't even look at the contents. But if you have a bunch of different formulas, and especially if it's been a few months since you worked on the sheet, you'll know what I mean. And if you're working with someone else's work, you'll even more appreciate them using the most efficient formulas.As for whether BMI is a useful stat, I don't know. But that's ok too... the only way to discover something new is to go looking where others don't.

 

[Ignoratio Elenchi]

You could replace this:IF(LEN(B2)=4,RIGHT(B2,2),RIGHT(B2,1))with this:RIGHT(B2,LEN(B2)-2)

Good call. Mine "looks" cooler though

Hopefully you won't think I'm being snide or condescending, and I commend you for using Excel's formulas. It's one step of a journey that opens up a whole new world of what you can do with numbers.But after you work with a bunch of these, especially in complex spreadsheets, you'll appreciate the abridged versions, which are much easier to understand. Think of it this way... if yours is the only formula in a spreadsheet, it's easy to remember, "Hey, that's my formula for BMI", and you don't even look at the contents. But if you have a bunch of different formulas, and especially if it's been a few months since you worked on the sheet, you'll know what I mean. And if you're working with someone else's work, you'll even more appreciate them using the most efficient formulas.As for whether BMI is a useful stat, I don't know. But that's ok too... the only way to discover something new is to go looking where others don't.

When I started at my current job, one of the first tasks I was given was to update/recreate an old Excel file that was used to produce a bunch of reports we needed to file with the state. Not only was I new to the company, I was new to the whole industry so all of the reports were pretty foreign to me - in fact the main reason I was given this task was moreso that I would learn what all the reports contained and how they were produced as I worked my way through the Excel file, and not really because there was a pressing need to update the file. This thing had dozens of worksheets, with easily hundreds of unique formulas, many of which were either unnecessarily complicated or totally unnecessary altogether. Spend time trying to fix something like that and you'll quickly learn to appreciate brevity and efficiency (and documentation!)

 

[Dippa]

When height is listed as 5-10 or 6-2, etc, I just do a quick "text to columns" and separate the feet from inches. Then multiply feet times 12 and add the inches. Then you have the height in inches, and the formula is much simpler:=703.06957964 * (Weight/Height^2)

 

[Ignoratio Elenchi]

When height is listed as 5-10 or 6-2, etc, I just do a quick "text to columns" and separate the feet from inches. Then multiply feet times 12 and add the inches. Then you have the height in inches, and the formula is much simpler:=703.06957964 * (Weight/Height^2)

But then you have to do that every time you paste in a new set of data. Seems like a waste of time when you can just write a formula once that will do it for you.

 

[ummich10]

I never thought I'd get to read about two of my favorite things all in one thread, football and Excel.If someone comes up with a custom function in visual basic that will blow my mind.I like I.E.'s approach, use it all the time. If/when you modify your excel file and you have to update the cell references, you might like the shorter formulas.

 

[BaBastage]

If someone comes up with a custom function in visual basic that will blow my mind.

For the example discussed, Excel is going to handle it more elegantly than a vb / vba call.If you have something else in mind, It'll be hard for someone to post that function for you unless you state what the function is supposed to do...

 

[Maurile Tremblay]

You could replace this:IF(LEN(B2)=4,RIGHT(B2,2),RIGHT(B2,1))with this:RIGHT(B2,LEN(B2)-2)

Good call. Mine "looks" cooler though

I'll skip opining on which one looks cooler, but it's worth noting that IE's version has the advantage of giving you the correct answer when somebody is 6-1.5 (instead of 6-1 or 6-2).

 

[ummich10]

If someone comes up with a custom function in visual basic that will blow my mind.

For the example discussed, Excel is going to handle it more elegantly than a vb / vba call.If you have something else in mind, It'll be hard for someone to post that function for you unless you state what the function is supposed to do...

I agree with you, no need for VB, just joking around a bit this morning.

 

[Ignoratio Elenchi]

Had to do something at work this morning, thought you guys might be able to use it=IF(AND(I49=1,OR(D49<>D48,I48<>1)),1,0)

 

[Nick Vermeil]

 

[Ignoratio Elenchi]

=SUMIFS(Q!$E$2:$E$50000,Q!$S$2:$S$50000,W$5,Q!$AF$2:$AF$50000,$B13)/(T13*T$9)

 

[Dinsy Ejotuz]

I always just convert the heights to inches (keeping the 1/8ths) and use that number.

And BMI is useful in conjunction with everything else. Intuitively you know that 6'3" and 210 isn't the same as 5'10" and 210, BMI is just a way to measure that.

It would be ideal if we had body fat %s for prospects, but the vast majority of the time it's safe not to worry about it since most of the guys who are serious prospects are in elite physical condition. (It would also be nice to have some way of measuring how weight is distributed on the body.)

ETA: didn't see that discussion was old. Carry on.

 

[Dinsy Ejotuz]

Slightly off topic, but it seems as good a place as any and I didn't want to start a new thread...

Over the next couple years I'm going to be adding a ton of data to what I've got so that I can readjust my NCAA performance measures to reflect the specific level of competition each player faced in college as well as some other things.

If I'm using R for some of that, what are the tradeoffs between having Excel tables that I import each time vs taking the time to put it into proper databases and pulling it from there? Is it more time than it's worth, or will I eventually wish I'd set up the databases? I've used SAS and R, but am not all that database savvy.

 

[BaBastage]

If you have a one-time use for the data, I'd stick with Excel. But it locks you in to fairly narrow use, until you extend and extend until you realize you've really used it to write a database application.

When I work with data, every answer I get from it begs another question, and having a database to work with just makes future questions that much easier to get answers for.

Since you mention "Over the next few years", a database seems like it's the clear choice. Just my $.02 though.

 

[Dinsy Ejotuz]

If you have a one-time use for the data, I'd stick with Excel. But it locks you in to fairly narrow use, until you extend and extend until you realize you've really used it to write a database application.

When I work with data, every answer I get from it begs another question, and having a database to work with just makes future questions that much easier to get answers for.

Since you mention "Over the next few years", a database seems like it's the clear choice. Just my $.02 though.

Thanks BB... very helpful. That's exactly what's happened too. Extend, extend, extend... and now I'm maxed out.

 

[Chad Parsons]

If you have a one-time use for the data, I'd stick with Excel. But it locks you in to fairly narrow use, until you extend and extend until you realize you've really used it to write a database application.

When I work with data, every answer I get from it begs another question, and having a database to work with just makes future questions that much easier to get answers for.

Since you mention "Over the next few years", a database seems like it's the clear choice. Just my $.02 though.

Thanks BB... very helpful. That's exactly what's happened too. Extend, extend, extend... and now I'm maxed out.

I know you are one of the other 'data heads' around these parts, Rob. If you want to chat sometime about the college-NFL connection and our data and future investigation, let me know. Would be fun to talk shop about it.

 

[DropKick]

You could replace this:IF(LEN(B2)=4,RIGHT(B2,2),RIGHT(B2,1))with this:RIGHT(B2,LEN(B2)-2)

Good call. Mine "looks" cooler though

I'll skip opining on which one looks cooler, but it's worth noting that IE's version has the advantage of giving you the correct answer when somebody is 6-1.5 (instead of 6-1 or 6-2).

Certainly more extensible. The OP should note how his formula would become cumbersome if it had to deal with multiple string lengths, such as 6-1.5 above.

IE's version is right until the league sees it first 10 footer! Obviously, the formulas work within the expected range of data values, but the lack of robust text manipulation functions in Excel is surprising. We need a "word" function that will parse a string given the string, and the word number (1st, 2nd, etc.) within the string and a delimiter.

 

[Jercules]

You know what we need in fantasy football? A variance, or standard deviation calculation. How many guys have fantastic per game averages, and fantastic gross points, but are ultimately useless because they live off these massive games that you can't see coming?

 

[ZWK]

Another useful Excel formula: VLOOKUP.

It's useful for combining 2 data sets, or for importing data from one data set into another, by finding the matching row. For example, it can find the "Giovani Bernard" row of your data set of college stats, so that you can add his college stats to your data set which has his NFL stats. Example:

=VLOOKUP($A4,$A$501:$M$1000,2,0)

If the cell A4 contains the text "Giovani Bernard", and the college stats are in rows 501-1000 of your spreadsheet with the player names in column A, this formula will find the row from 501-1000 which has "Giovani Bernard" in column A and output whatever is in column B of that row. It will give you an error if there is no "Giovani Bernard" row in that range.

Other details:

• You can also use VLOOKUP to get data from other Excel files, rather than from other rows in the same spreadsheet.
• The dollar signs are there to fix the references so that you can copy and paste this formula into other cells - the only thing that you'll have to change is the "2" which tells it which column to take the data from.
• Watch out for cases where two players have the same name, or where a player's name appears differently in different data sets.
• Google "vlookup" for more.

 

[Dinsy Ejotuz]

The problem with "consistency" is that for the most part the variance scales with the mean, and at the individual player level it isn't consistent from year to year.

Knowing that guys who score a lot of points are going to clear a "consistent" threshold more often isn't very helpful. And players who overperform their mean score one year are no more likely than random to do it again the following year.

There are a few exceptions, but mostly it just doesn't tell you anything.

Also a big +1 for the VLOOKUP function -- I use that one a lot.

@Chad Parsons... I just looked up your bio and saw that you're here in DC too. Will PM you tomorrow.