All language subtitles for 12. Statistical Formulas Case Study - Solution

In this video we will solve the setting in a case study using statistical functions in Excel.

The first thing we will do is calculate settings overall batting average.

So select sell it do on the formula tab click more functions most of us statistical and from the list

select average in the number one fee we will enter the sales with Sutton's batting record.

As you can see his batting a good start in a two and go right up to the sell a 3 in the 30 click Okay.

Excellent displays Sutton's overall batting average in cell S2 but you'll notice that it is slightly

lower than the batting average shown elsewhere.

If you go to any cricket site you'll see cities betting that rate is higher than what you see in Celeste

too.

The difference comes from a nuance of calculating batting average in cricket.

The formula in cell is to added all the runs scored by setting across all the innings and B why did

it.

By the count of all the innings he played.

But the batting average is calculated by dividing runs scored by the number of innings minus the number

of innings the batsman does not own.

So only combining it with a batsman got out.

Let us calculate the regular batting average for city select select three and this time will type a

custom formula to calculate batting average.

Let us add up

the runs scored by setting across all leanings so there's some A2 through to 30.

Calculate all the runs scored researching and let us divide it by counting the number of innings he

played when he got out.

So let us do the count of formula the county formula counts rows in Excel that meet the criteria that

we specify.

The first argument is the range the range you are looking for is column B where the mode of dismissal

is recorded.

So let us select the range B2 through B 330 which has all lost that is dismissal mode and now define

that criteria.

We want to select innings First the team was out.

So basically we want to count rows where the mode of dismissal is not equal to not all.

So let us write that

another function we have got we count the rows in the same length B2 through under 330 where the cell

value is not equal to not out close the bracket and PRESENTER And now we have set his batting average

that you see every day.

Well the batting average is useful.

It also has the problem average gets skewed by extreme values.

So if the tennis court 0 0 is scored 100 they will have enormous impact on the average.

Also as we saw if he scored one hundred and even not out that would push up the average.

Quite a lot.

If you look at this batting average and compared to the runs he scored you'll clearly that on most occasions

he did not score these many runs so it is not really a true indicator of how much runs to expect every

time he went out the back a much better measure of that number is the median the median finds the value

that is in the mickey of all the values given in ring.

By definition 50 percent of the numbers will be above median and 50 percent will be below the median.

So let's look at the median score of such an excel as a formula called median that we can use here on

the formula stack play more functions more stories statistical and from the list select median in the

function arguments dialog box number 1 let us and that is batting record range it is a 2 to 18 to 30

like okay an a local place the median is called for Sachin it is thirty two.

Let us look at the score that is scored most often the most frequent score of thirteen in a list of

numbers the most frequently occurring number is called the mode the will use mode to calculate the most

frequent score of such an Excel it's 5 on the formulas that like more functions more so in statistical

and from the list select mode not a single which stands for single.

It calculates the single number that is the most frequently occurring number in a list.

Again in the number one box will enter Saturn's writing scores to enter the range and click ok.

What's that is zero did you expect that zero is the score scored by setting.

Most often he actually scored zero 15 times and that happens to be and scored.

He got more often than any other score.

So that's why his mode is zero.

Let us points at its highest score in Test cricket for this will use a simple statistical function max

type equal to Max and the range once again we enter a two through a 330.

And there you go.

So six has set its highest score.

Two hundred and forty eight.

Let us calibrate one more number.

Number of center is scored by certain.

While you might know it.

Let's see how you can do that in Excel.

The values that count.

Function define the number of innings we set in scored more than ninety nine.

So on the formulas that play more functions more so statistical and scroll down to find Countess in

the function arguments box in the range feel will enter it to through a 330 and in the idea field will

enter into them ninety nine select seven now shows the numbers of to scored by set which is fifty one

let us calculate the standard deviation of that is performance and we will use the standard deviation

formula in Excel will select selected on formula start more functions statistical and in the list will

select STV dot B not P stands for population so there are two ways you can calculate standard deviation

in Excel one is for the entire set of data and we use the formula as did the e reader before that you

can also calculate standard deviation for a sample from the total population.

We can sample the data and calculate the standard deviation for the sample and we use STV dot s for

that the formula works the same way it does the number of data points that is different in the two functions

and the number one box.

We will enter Saturn's batting record range 2 2 8 330 okay.

And we have set in standard deviation which is fifty point eight four which is very close to his batting

average.

What it means is the Russes coordinate each innings are spread wide apart.

They're not closely packed on the mean.

Let us use that analysis tool back in Excel to calculate summary statistics for setting to use summary

statistics on the data that in the analysis group play data analysis from the data analysis dialog box.

Select descriptive statistics and analysis tools list and click Okay.

In the descriptive statistics dialog box we need to first select the range for which we need to calculate

the descriptive statistics.

So let us enter that as a tool through a 330 in the output options will insert the descriptive statistics

in a new worksheet from the new worksheet box.

Let us type.

Summary statistics

to the new worksheet will be titled some minister districts select the summary statistics check box

and click Okay.

You can see Excel has created a new worksheet and this culture does summary statistics which includes

mean standard error of the mean median mode standard deviation and a host of other statistical values.