Statistical Significance in Excel

rob51852

Board Regular
Joined
Jun 27, 2016
Messages
190
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I want to understand the statistical significance of some cricket batting data.

As an example a batsman faced a total of 100 balls in a period, was out twice and scored 150 runs.

I’d calculate the batting average by dividing the number of runs by the “outs” to give 75.

I’d calculate the strike rate (runs scored per 100 balls) by dividing runs by number of balls multiplied by 100 to give 150.

Which function can I then use to gauge the statistical significance of these results?

Often there will be far fewer than 100 balls so I’d like something to quantify the reliability of the results.

Thanks
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Disclaimer: This is not my area of expertise, although I do have some practical experience in the area.

I believe you want the confidence interval, not the statistical significance. The latter is usually based on a statistic (e.g. the means) of two comparable samples.

For a 95% CI (typical), use CONFIDENCE.T(5%,sd,n), where "sd" is the sample standard deviation (STDEV) of the data, and "n" is the number of data.

Thus, for example, if C1 has the formula =CONFIDENCE.T(5%,STDEV(D1:D100),COUNT(D1:D100)), we can say that we are 95% confident that the mean is between AVERAGE(D1:D100)-C1 and AVERAGE(D1:D100)+C1.


-----
Caveat: The help page says that "sd" is the population standard deviation. I believe that is wrong or misleading.

First, by "population", I think they mean the exact sd of the sample data; that is, STDEVP(D1:D100). In fact, the help page says "population standard deviation of the data range".

In contrast, in academia, the term "population standard deviation" refers to the sd of the entire possible data that samples are taken from. In that context, the "population" sd is usually unknown.

(Arguably, in contrast, the help page says the second parameter is "assumed to be known". I consider that to be a compounding mistake; a writer's misguided attempt to clarify the ambiguity of the phrase "population standard deviation".)

Second, it appears that CONFIDENCE.T internally calculates the "standard error" by s.param/SQRT( n ), where s.param is the second parameter ("sd").

According to my statistics text, the std err is either s/SQRT(n-1) or s.est/SQRT( n ), where "s" is the exact sd of the sample (STDEVP), and "s.est" is the estimated sd of the "population" based on the sample (STDEV). And in fact, we can demonstrate that both calculations have the same result.

So, the internal CONFIDENCE.T calculation matches the form of s.est/SQRT( n ); that is, STDEV(D1:D100) / SQRT(COUNT(D1:D100)) .

And I conclude that the second parameter (s.param) should be STDEV(D1:D100), not STDEVP(D1:D100) as the help page seems to imply.

An alternative interpretation that leads to the same conclusion is: the help page really means "population standard deviation" in the academic sense, which usually must be estimated by the sample standard deviation (STDEV) of the data range.

The bottom line is.... Caveat lector! Whether to use STDEV or STDEVP is a matter of interpretation. You should use whatever your class instruction tells you to use. In fact, if you have a concrete example (data and explicit calculations and results) from a statistics text that does not rely on Excel, I would appreciate it if you would attach images of the pages in a response to this thread. TIA.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top