Let's kick things off with some basic Stets functions use the functions like Count average median mode
min percentile etc. rather than dive into each one specifically.
I just wanna show them all here.
This isn't a comprehensive list.
This is kind of a list of the ones that are more commonly used but the syntax is pretty straightforward
pretty simple to understand really the only component and most of these formulas is your reference array
.
So for instance if I want to find the median of the data from A to the read 20 that's exactly how I'd
write the formula.
The only differences for ones like percentile.
You have an additional component of the formula to specify which percentile you want to return.
So if I'm looking for the twenty fifth percentile within a given data range I'd put a point to five
.
I'm looking for this seventy five point seventy five standard deviation is s t dev variants is VAR.
So let's jump into Excel and see what we're working with for this section.
We've got the section for statistical functions workbook open as you can see there are three tabs here
right now.
And the salary data tab which is a raw data.
And as you can see we've got five years worth of Major League Baseball salary data.
So this year column team league player and salary.
You'll also see two blank columns F and G for rank and percent rank.
We'll be covering those in the next lecture on the second tab called basic stats functions.
I've just got some blank cells where we're going to be calculating some of the formulas we just walk
through.
We'll do that in a minute.
And then last but not least the first tab is team salary dashboard.
This is going to be our section wrap up demo or we're gonna use Data Validation countifs and sumifs
to aggregate and visualize some of this or our salary data.
So fun stuff there.
Let's jump in basic stats functions and just populate these cells.
So I'm going to start with sample size which basically requires a count of my data set.
So I'm going to tell you equals count.
Open the parenthesis and then here's where I insert my data array.
So I'm going to jump over to the salary data tab and select the entire column each to my salary column
.
Close the parenthesis and hit enter and it jumps back and evaluates the formula.
One thing to point out is that when you're linking data referring to data cross tabs Excel will automatically
add the tab labels there so that's why it says salary data in single quotes followed by the reference
.
So I don't have to type many of that as you click through.
It will automatically populate for you.
So let's do the average here.
I'm just going to be equals average salary data on the average salary just sort of 3.5 million dollars
which is totally nuts.
Let's look at median here which is just the median function.
So as you can see it's a very similar pattern here so I'm going to start moving a little bit quicker
mode is just mode.
Mode is one of those functions that takes quite a while to calculate.
So I'm kind of pausing here to let Excel do its thing.
Usually it takes five to 10 seconds.
There you go.
I'm not quite sure why it takes so much longer than the others.
Essentially the mode is just which number appears most often in our dataset.
And in this case we see that it's one million dollars.
So take a look at the max salary is for maximum I can just right Max 33 million which I believe is Alex
Rodriguez.
Minimum is at my end
400000 which was and MLB minimum salary in 2010.
So now we're getting into seventy fifth percentile.
So I'm going to type out percentile select my array just like normal but now I'm going to add a comma
and you see this little helper box popped up and it's looking for the K value.
That's just what percentile I'm looking to turn.
In this case it's seventy five point seventy five.
Close it off.
There you go.
Now to the 25th percentile let's save ourselves a few seconds.
Just copy the formula above.
Paste it down.
Change the 7 to 2 and we've got our 25th percentile which is just under 500000.
A standard deviation is s t dev.
This is going to basically describe the spread of our data around the mean and then the variance is
basically just calculated as standard deviations squared which is going to give us a huge huge number
not particularly meaningful in this context but that's the variance function.
These other two tenth highest and 100 lowest salary we're going to cover in the next lecture.
But there you go.
Basic stats functions