Introduction

What is Statistics?

Statistics is a field of study that has two principal objectives:

• Describing data (Descriptive Statistics)
• Making inferences and predictions based on experimentally observed data (Inferential Statistics)

Descriptive Statistics involves calculating the mean, median, variance, standard deviation and other properties of the data, and presenting this information in ways that make the data more meaningful, such as histograms, boxplots, etc.

Inferential Statistics involves analyzing data and inferring characteristics of a general population based on the same properties of a sample taken from the population. This is what gives the field of statistics its power since with a relatively small amount of data we are able to make significant assertions, even though such inferences are not 100% certain, but probabilistic in nature.

Why do Statistics in Excel?

There are a number of commonly used, powerful tools for carrying out statistical analyses. The most popular of these are SPSS, SAS and R. Many people choose to use Excel as their principal analysis tool or as a complement to one of these tools for some of the following reasons:

• It is widely available and so many people already know how to use it
• It is not necessary to incur the cost of yet another tool (some of the popular tools are quite expensive)
• It is not necessary to learn new methods of manipulating data and drawing graphs
• It provides numerous built-in statistical functions and data analysis tools
• It is much easier to see what is going on since unlike the more commonly-used statistical analysis tools very little is hidden from the user
• It provides the user with a lot of control and flexibility

This makes Excel an ideal tool for quick analyses and even some serious systematic analyses, but it has two major shortcomings:

• Many people are not very familiar with the statistical capabilities built into Excel
• Excel was not designed to be a comprehensive statistical package and so many commonly used statistical tests are not provided

We will address the first of these shortcomings in this book. We will also give some techniques for extending the built-in statistical capabilities included in Excel, partially addressing the second shortcoming.

Where Excel provides the statistical analysis capabilities that you need, and fortunately many of the most commonly used tests are included in Excel, it is a great tool to use for the reasons mentioned previously. Where it does not, there are a number of software packages which extend the built-in capabilities, including the software package that I have developed called Real Statistics, which you can download for free at www.real-statistics.com.

Where is Statistics used?

Statistics plays a central role in research in the social sciences, pure sciences and medicine. A simplified view of experimental research is as follows:

• You make some observations about the world and then create a theory consisting of a hypothesis and possible alternative hypotheses that try to explain the observations you have made.
• You then test your theory by conducting experiments. Such experiments include collecting data, analyzing the results and coming to some conclusions about how well your theory holds up.
• You iterate this process, observing more about the world and improving your theory.

Statistics also plays a major role in decision making for business and government, including marketing, strategic planning, manufacturing and finance.

Statistics is a discipline which is concerned with the collection and analysis of data based on a probabilistic approach. Theories about a general population are tested on a smaller sample and conclusions are made about how well properties of the sample extend to the population at large.

What will this book cover?

In this book we will provide a description of the following:

• Basic statistical tests and analyses
• Methods for carrying out these tests and analyses using Excel
• Numerous examples

We don’t have space to cover all aspects of statistics in Excel, but, as you will see, we try to give you a good idea of how to do significant statistical analyses in Excel.