Copyright

Using Excel for Descriptive Statistics

Instructor: Maria Airth

Maria has a Doctorate of Education and over 15 years of experience teaching psychology and math related courses at the university level.

Microsoft Excel is a powerful tool used to manipulate data. Excel allows you to calculate descriptive statistics on data sets in a matter of a few keystrokes. This lesson presents the formulas for common descriptive statistical calculations in Excel.

Why Use Excel?

Imagine that your company has had a fundraiser event. Your boss has asked you to give him a summary of the donations. He wants to know information like the minimum donation, maximum donation, the average donation, what amount was the most popular, etc.

Your boss is asking for a summary of the descriptive statistics related to the donations. Any statistical information that describes a set of data is descriptive statistics.

Working through the data by hand would be time consuming and could risk human error. A better idea is to use a strong mathematical tool to analyze the data for you. Microsoft Excel is a spreadsheet software package that allows you to compute myriad equations based on entered data. Descriptive statistics are a breeze in Excel.

You will find that formulas in Excel are very intuitive and easy to enter. In this lesson, you will learn the formulas to find the max, min, mean, mode, median, sum, range, and mean of a set of data. You will also learn how to identify items in exact positions without having to manually count out the positions (such as the 3rd largest, or 2nd smallest number in a series).

Getting Started

The great thing about Excel is that you do not have to do anything special to the data before entering it into the spreadsheet. Let's use the following set of 20 numbers as our sample set to learn the formulas required to calculate basic descriptive statistics in Excel.


null


Notice that the numbers are not entered in order. They do not even need to be entered in a straight line. Here, the 20 numbers are entered in the cells A1 through D5. This is the array, the area of the sheet within which data to be manipulated is entered. In our formulas, you will see this array listed as A1:D5 meaning all cells from A1 through D5 inclusive.

The function bar is to the right of the cell identifier. This cell always shows exactly what has been typed into a cell. When you type in a formula, the formula will appear in the function bar while the return (answer to the formula) will appear in the cell.

So, now that our donations are entered into the spreadsheet, we can get to work on those formulas.

The Formulas

The first thing you need to know about formulas is that they always start with an = (equal sign) . This is very important. The equal sign is the signal for the software to begin priming formulas. Otherwise, the software sees what you have typed as common text and does not activate any formula.

The second thing you need to know is how to enter an array into a formula. You can enter an array by manually typing in the cell name of the top left and bottom right cells separated by a colon (A1:D5) or you can highlight the enter array with the mouse and the array will be formatted in the formula for you.

Every formula has this format:

=command(array or argument)

Notice that the command can be in all caps or all lower case and the array must be in parenthesis.

Let's begin with an easy one.

Max and Min

Excel formulas are very intuitive. Excel also uses predictive text to assist you with finding the correct formulas.


null


The formulas for finding the maximum and minimum of our set of data are:

  • =min(A1:D5)
  • =max(A1:D5)

Clicking Enter initiates the calculation. You will see the formula change to the return answer in the cell, but it remains in the function bar.

Here is what it looks like in Excel.


null


Intuitive Formulas

The formulas for counting and finding the mean, median, mode, and the sum of a data set are just as intuitive as the previous formulas. In this image, you can see each return with the formula written out in text.


null


The mode includes an instruction for accommodating multiple modes (.mult), which is always a good idea with a large group of data. You must choose mode.mult or mode.sngl when entering the formula.

To unlock this lesson you must be a Study.com Member.
Create your account

Register for a free trial

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Free 5-day trial

Earning College Credit

Did you know… We have over 160 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it free for 5 days!
Create an account
Support