Welcome to Excel 2013 Statistical Video number 17.
If you want to download this workbook and follow along,
click on the link below the video.
Hey, we've got a great trick here
for when calculating a mean.
So here, we have some quiz scores.
As we saw in the last couple of videos,
if we're calculating mean, we use the average function.
It will add them all up and divide by the count.
So the average score, or mean score, on this quiz was 6.16.
But we don't always get our data points in this form.
Sometimes you will be given them in this form.
Hey, this looks just like last chapter,
where we had a frequency table.
Here's the unique list of possible quiz scores,
and here's the count, or frequency.
So sometimes you're given two people
got a score of five, three people got a score of six.
And if you have this, can we calculate the mean?
You betcha.
Well, look at over here.
We have a five and a five.
In the numerator, we'd actually add those two things.
Well, we can get that same number over here, 2 times 5.
Three sixes?
Well, there was three sixes over here.
No problem.
18, we can get that over here by multiplying
what's called the weight and the particular x value.
So 6 times the weight of 3 will give us 18.
So step 1 for in the numerator is take your weight,
the number of times this particular value
occurred, times the particular value.
Control-Enter, copy it down.
I'm going to copy and then come down to the bottom, Alt-Equals.
That's our sum function, and Enter.
So 74, that's the total if we added up all of these
over here.
Looking down in our status bar, we can see 74.
So that's looking good for a weight.
Now, what do we do over here?
We counted, right?
So I see a count of 12.
Well, what if I were to add up all the weights?
Alt-Equals.
It's exactly a count of 12.
So to calculate a weighted mean, x-bar--
but now we have a different calculation.
The sum of the particular weights
times the particular x's divided by the sum of the weights.
Now let's go ahead and calculate this over here.
There's the sum of weights times particular values.
And I want to divide it by the sum of the weights.
Oh, we get the exact same thing.
Now that's pretty cool.
We can build this extra column, add these, and then make
a division calculation.
But notice, that's a lot of extra calculation
just to get the single cell number we want.
Well, no problem.
There's a formula we can build that will
do that all in a single cell.
But I want to come and look at this.
Hey, look, all of the particular x's
times all of the particular weights and then we add it.
Let's just do a trick here.
F2, Enter, F2, Enter.
Do you see the orange one times the blue one?
It almost looks like there's a column times a column.
Let me do that again--
F2, Enter, F2.
It's the orange column times the blue column,
and then you add it.
Well, think about this.
Sum is the word for adding.
Product is the word for multiplying.
There's an actual function called SUMPRODUCT, S-U-M-P,
Tab.
It wants the array and their array,
and so I'm going to give it in any particular order.
But the two arrays or ranges have
to be exactly the same size.
This is six rows by one column.
Notice it says array 1.
I type a comma to get to the next argument.
The next array, the weights, hey, it's
also six rows by one column.
No way.
SUMPRODUCT will do it.
It will actually multiply each individual element.
That's the product part of this.
And then it will add the result. That's the sum part.
You ready?
It better be 74, right?
And Control-Enter, and sure enough, it is 74.
Now what and we also have to do before we calculated?
We also had to add up the weights.
Well, I want a single-cell formula, so F2.
My cursor's at the end, so I'm going to divide
by the sum of the weights.
No way.
Close parentheses, Control-Enter, boom,
a single-cell formula.
That is absolutely beautiful.
And here's what's even cooler.
If I were to remove all this, well, that's,
of course, not going to work.
But that's only pointing at the source data.
It works perfectly.
So for single cell weighted mean calculation,
that's the way to go.
Now, Control-Z. Let's go look at another great example
for a weighted mean.
Because in business and accounting and finance,
there's all sorts of examples for weighted means.
Hey, look, here's our dates.
Here's the quantities we purchased of the Quad Boomerang
product on each one of those days.
And there's the price per unit.
Well, at the end of the year, you have this information,
and you need to calculate what's called the weighted average.
Well, I can take 108 times that price, 72 times that price,
and so on.
So I'm going to say, hey, the weight--
that's 108 items-- times the particular x--
that's the price.
Control-Enter and drag it down.
Down at the bottom, I can Alt-Equals and Enter.
That's the total cost we expended.
Well, we now need to add up the weight, so I come over here.
Alt-Equals and Enter, 120.
Now, for our particular situation
here, we have 865 that we sold during the year,
and we need to calculate cost of goods sold
and ending inventory.
Well, there's lots of ways to do valuation of inventory,
but weighted average is one common way.
I already have the two numbers.
I'm going to say, hey, the big numerator divided
by the big denominator and Enter.
There it is-- $22.82.
That's the average price we paid throughout the year
for the Quad Boomerang.
Now, the single-cell formula--
and many times, in accounting, you
don't want to do all this extra stuff.
You're not interested in any of that.
You just want the one number.
No problem.
Equals SUMPRODUCT.
I'm going to take array 1 times array 2.
It doesn't matter in which order.
I'm going to take the x's first.
I see that I got the array comma to get to the next argument.
Now I take the weights.
And now it will product--
multiply all the individual elements and then sum add them.
Now, this only gives the numerator, right?
There, it's exactly the same.
F2 to put it in edit mode.
Weighted mean always divided by the sum of the weights.
Close parentheses, Control-Enter.
Same exact number.
Now, how do we calculate cost of goods sold in ending inventory?
Well, we looked on our records.
865 Quads sold this year.
So our total cost of goods sold is going to be equals 865
times our weighted average.
Enter.
So on our income statement, it will show $19,738.28
cost of goods sold for Quads.
Now, the ending inventory-- well,
we know the total here and the 865.
So equals, in parentheses, because we
have to force subtraction first, the total minus the 865 times
the weighted average.
There we go, ending inventory.
And when we add those two up, Alt-Equals-- oh, remember,
Alt-Equals doesn't always guess right.
I'm going to highlight these two.
This is a check number.
Control-Enter.
I'm going to move it, boop, down there.
Then I'm going to put Total, Control-Enter,
Control-B. That's just like my check figure, right?
Because those are the two numbers I'm going to--
that goes on the balance sheet.
That goes on the income statement.
And sure enough, boom, there it is.
Now we want to look at our next example.
And this is the same data set in a slightly different form.
So sometimes the accounting department
will give you the prices and the frequency,
but they might give you the percent frequency.
Now, I just want to remind you that we did this last chapter.
So percent frequency for the actual frequencies or counts--
these are the units that we purchased, right?
We could say equals, hey, this count of 108--
we bought at 108 at this price--
divided by the total, and F4 to lock it.
Control-Enter.
When I copy this down, now I have percent frequency--
or relative frequency, if you didn't
have the number formatting-- for the actual counts here.
If we add these all up, it better equal 100%.
And Enter.
So sometimes in accounting department,
you're going to be given--
or other situations, too, in finance
and lots of other situations.
You'll be given the individual values and the percent
frequency.
That's the percentage of all of the times
it happened that this particular item happened.
So $17.42 happened 10.59% of the time.
Now, what do we do?
Well, it's even a little bit easier.
So, again, the idea is we have these two columns.
Well, we can multiply equals the percent
frequency times the actual price per unit.
Control-Enter.
And when I copy it down--
remember, all of these add up to 1.
So if I add up all of these, it'll be exactly--
Alt-Equals, Enter-- our weighted average, that $22.80.
So if you're given the values and the percent frequency,
then you can calculate the weighted average very easily.
In chapter 5, this would be called the expected value.
All right, so method three is just the single column,
and then there it is at the bottom.
We could also use SUMPRODUCT.
So SUMPRODUCT allows us to multiply two arrays
and then add.
Well, we'd have our particular values-- in our case,
the prices--
comma.
And the second array are percent frequency.
That's the percent of all the total times
that this price happened, then this one, then this one.
Multiply them together, Control-Enter, and $22.82.
So we actually saw method number one was the extra column, where
we have particular x times the weight
and then add them up and add all the weights.
So we did that one there.
Then we saw method two.
That was the SUMPRODUCT, where we multiplied
the particular x's and the weights
and then we divided by the sum of all the weights.
And then if you're given a percent frequency table,
you can simply add method three, an extra column.
Multiply percent times a particular value, and then
the total is the weighted average.
Or you can use method four.
That's the SUMPRODUCT, where we took percent frequency
times the price per unit.
And that will be called expected value in chapter 5.
And expected value means if you're looking at past data
and you want to estimate for the future what the price might
be like for budgeting, that would be the expected value.
Hey, this video, we talked about the amazing weighted mean.
Next video, we'll talk about another amazing mean called
the geometric mean, used for financial data.
All right, we'll see you next video.