In this video, I am going to show you how to use Excel to calculate

the expected value and standard deviation for discrete random variables,

and this is the example I'm using.

The manager of a convenience store wants to

understand the demand for one of the products she

sells so that she can make a better decision on how many to stock every day.

She has the following data gathered over several weeks.

Based on this data,

what is the value of expected demand and its standard deviation?

So, what we have is that a table that shows the demand has gone anywhere between 1-20,

and we can see over the time that she studied

how many times demand for this item was just only one.

So, the three times there was only one,

10 times it was two and so on.

So, let me go to the next tab that I only have the data and show you how we will do it.

First thing I need to do is find the probability of each.

So, I'm going to create here for that.

In order for me to answer this question,

I have to know how many times daily demand of one occurred over this total period.

So, what I need to find out is first of all,

what is the sum of all the currencies.

The sum of all occurrences,

is just sum of the column right here,

so I'm going to hold Control+Shift,

parentheses and close it and we have total of a 140 different types of occurrences.

So, then this is simply three out of 140 was daily demand of one.

So, I can B2 divided by 140.

That this 140 is going to be a value that I will use for every type of daily demand.

So, I'm going to use the same thing in the denominator when daily demand is two,

when daily demand is three and so on.

To make it easy for me to copy and paste,

this 140 is not going to change,

so what I can do is that we can press function F4 and you would see that

the dollar signs are appearing between each value of i and one.

That means that cell is going to be locked.

So, when I press return and I copy this down,

you would see that in the next one again B3 in this one,

B3 is being divided by the same number, 140.

So, 140 is going to say the same as I copy this.

So, now that I have that the easiest way to copy this to put your cursor in the corner,

as you see that plus sign appears,

just double-click and it will fill the whole thing for you.

You want to make a double sure that you have done nothing wrong.

So remember, all probability should sum up to one.

So, if I sum this column then I should get

a value of one otherwise I have left something out.

So, I'm going to double-check

and I pick the entire column and sure enough it adds up to one.

So, I have accounted for all the probabilities.

Now, that I have done this,

the expected value is simply

the daily demand multiplied by its probability of occurrence,

summed up to the next one,

multiplied by its probability and so on.

So, what we want to do is that take the values from this column and one by one,

multiplying them by their corresponding value that they

see in the probability and then sum as we go along.

This is something we have in Excel done for us automatically,

it is known as the SUMPRODUCT.

So, SUMPRODUCT takes two array,

and in this case, our array is our daily demand,

so am going to pick all the values that show up in our daily demand and scroll back up

and the second array is the probability of each of these daily demands occurring.

So, I will do the same thing,

close the parentheses and return.

So, therefore our expected value,

the mean demand is 11.49 but this is only part of the answer.

What is the variability of demand that we experience?

For that we need to calculate the standard deviation.

This is the equation for the standard deviation.

So, what I need for the standard deviation is first of all,

calculate what is x

each daily demand occurrence minus mu squared and then multiplying it by its probability.

Let me just do that by creating a column here that I will

call x minus mu raised to the power of two.

So, this would be basically for each column.

It will be this value,

minus the expected value or the mean

and this is something that I'm going to use as I go along.

So, I'm going to lock the location by by pressing

F4 and then raise this to the power of two.

So, if I now drag this down,

you will see what happens.

In the next one,

location of A3 has become updated but I4,

where is the mu has remained locked.

So, it's going to be repeated.

So, now that I know this is right,

I can put my cursor here when I see the cross here, double-click.

So, now I can calculate my standard deviation,

and once again this is going to be a SUMPRODUCT of which columns,

x minus mu times the probability and then square root of the whole thing.

So, I'm going to say square root of the SUMPRODUCT,

the array one is x minus mu and just I'm

following the formula and array two are the probabilities.

So, here we go.

These are my square root of SUMPRODUCT of values

in this column multiplied by this column and summed up as we've gone along.

Exactly what we need here.

So, when I return I see that the standard deviation is 6.205.

So, if I think about one standard deviation away, so what is that?

One standard deviation away is this value loss 6.205 and this value minus.

So, 68 percent of the time

the demand is actually between something between 17 and five roughly.

So, while it is good to know average,

standard deviation will tell you what is the variability that you will

notice in that average on a long term basis.