Pearson’s correlation coefficient is a measure of how well the data in your sample is linearly related. Microsoft Excel has a built in function for the correlation coefficient, which will return a correlation between -1 and 1. The closer to 1 or -1 your value is, the higher the linear correlation (1 represents perfect linear correlation while -1 represents perfect negative correlation). For a full explanation of Pearson’s correlation coefficient, click here.
Continue Reading

This collection of articles will guide you through the most common functions in Microsoft Excel for statistics. For nearly all Excel statistical operations, you’ll have to load the Data Analysis ToolPak first. One advantage of Excel is that you can enter your data, manipulate it (for example, sort from A to Z or high values to low) and create several graphs next to your data.

1. How to Calculate the Mode in Microsoft Excel
2. How to Find an Interquartile Range Excel 2007
3. How to Calculate a Standard Deviation in Excel for a Sample
4. How to Calculate the Mean in Excel … Continue Reading
   

Microsoft Excel contains a powerful statistical package where you can calculate everything from means and medians to chi-square and hypothesis testing. That said, Excel isn’t really an intuitive package when it comes to more advanced functions, like a Hypothesis Test in Excel for the Population Mean (Large Sample). There’s no single button to press that says “Hey, test this hypothesis!”. Instead, you’re going to have to go through a series of short steps — which will feel logical if you’re already comfortable with hypothesis testing. If you aren’t comfortable with hypothesis testing — then you’re probably better off going back to the basics (i.e. using an old-fashioned z-table) until you’re comfortable with the process. Then, you can use Excel to make quick calculations for complex situations.
Continue Reading

A confidence interval for the mean is a way of estimating the true population mean. Instead of a single number for the mean, a confidence interval gives you a lower estimate and an upper estimate. For example, instead of “6″ as the mean you might get {5,7}, where 5 is the lower estimate and 7 is the upper. The narrower the estimate, the more precise your estimate is. The equations involved in statistics often involve a lot of minor calculations (such as summation), plus you would also need to calculate the margin of error and the mean of the sample. It’s very easy for errors to slip in if you calculate the confidence interval by hand. However, Excel can calculate the mean of the sample, the margin or error and confidence interval for the mean for you. All you have to do is provide the data — which for this technique must be a sample greater than about 30 to give an accurate confidence interval for the mean.
Continue Reading

The median is one measure of central tendency in statistics. The median separates the lower half of a sample from the upper half of a sample. For example, in the sample {1,2,3,4,5,6,7,8,9}, the median is the number 5, which clearly separates the lower half {1,2,3,4…5} from the upper half {5…6,7,8,9}. However, finding the median in real life isn’t that simple. If you have real life data, you’ll likely have a large sample with an array of numbers, with none of them in order! You can calculate the sample median in Excel and put the numbers in order so you can clearly see a trend.
Continue Reading

While it’s possible to look up probabilities for a normal distribution using the z-table, it’s actually much easier to calculate probabilities in Excel for a couple of reasons. First, there’s no looking at a table — the NORMDIST function does the hard work for you (what goes on behind the scenes is that Excel looks up the probabilities for you). Second, Excel does the intermediate calculations for you. Most calculation errors happen in an intermediate step (such as calculating the z-score to look up) rather than the actual z-score itself. Excel can handle three types of probability calculations: more than, less than, and in between. These instructions work for Excel 2007 and Excel 2010.
Continue Reading

Descriptive statistics are one of the fundamental “must knows” with any set of data. It gives you a general idea of trends in your data including the mean, mode, median, variance and standard deviation, range, skewness, count, maximum and minimum. Using the descriptive statistics feature in Excel means that you won’t have to type in individual functions like MEAN or MODE — one button click will return a dozen different stats. If you want to calculate Excel descriptive statistics, you must have the Data Analysis Tookpak loaded in Excel. If you don’t have the Excel Data Analysis Toolpak loaded, you can find out how to by clicking here.
Continue Reading

Microsoft Excel has an array of functions that can help you get statistics from your sample or population data. The mode is a frequently used measure of central tendency that returns the most common number in a data set. For example, the mode in the set {1,2,3,4,4,4,5,6,6,7,8,9} is 4, because 4 appears more times than any other number in the set. In some sets of data, Excel may not be able to calculate a mode — for example, if all of the numbers in your data set are different. In that case, Excel will return “n/a” instead of a number. You can calculate the mode in Excel in more than one way — either by entering a function or using the Data Analysis Toolpak.
Continue Reading

Microsoft Excel has a wealth of statistical tools you can use in probability and statistics, including formulas to calculate a standard deviation.  The standard deviation is represented by the lowercase sigma (σ) symbol, and shows how much dispersion, or variation, exists in the data from the mean. The larger the standard deviation, the more spread out the data. Computing a standard deviation by hand is complicated and prone to errors. To calculate a standard deviation in Excel, you must first enter your data.
Continue Reading

Most statistics professors recommend a graphing calculator for statistics. But many of the functions can also be performed in Microsoft Excel. Excel has many advantages over the graphing calculator in some areas: it’s much easier to print out a graph or table (with the graphing calculator you would have to link your calculator to your computer with a cable, open the software and print from there), you’ve got a larger screen — which is useful if you have a large amount of data, and it’s easier to share your findings with other people. One of the most basic statistical functions in Excel is calculating the mean. There are three different ways to calculate the mean in Excel: use Autosum, use a function or use data analysis.
Continue Reading