How to Average Data on Excel

Microsoft Excel is an extremely useful tool when it comes to collecting data, combining data and analyzing it. Few people realize that Excel can even do equations with the data you have input, to make finding the average, the median, the range, and more much easier for users. This skill is great to have, but can seem difficult to master, so this tutorial is here to help you master a task that seems intimidating, but is in-fact easy to master and incredibly useful to know.

Computer

Microsoft Suite (downloaded and functional on your computer)

Data that you would like to input and analyze or have calculated

First you will begin by opening a new excel spreadsheet on your computer. Be sure to save it under something that you will easily recognize or understand what the title is corresponding to, to avoid confusion. Additionally, save it in a place you will remember, or a folder that corresponds to the data or project you are creating. For the purpose of this instructable, I have saved my spreadsheet as "Instructable Tutorial" and put it in a folder for this tutorial on my desktop to make it easy to find and access.

First begin by determining which data will go in which row, and which row will be labeled with what title. For the purpose of this instructable, my data will be made up of the height of my classmates and I will separate them by gender. I will use column A for the heights of the male students (in inches), column B for the height of the female students (in inches) and then column D for the average male height of my class (in inches) and column E for the average female height of my class (in inches). I skipped column C to make the data seem less cluttered when the numbers are input to make it easier to read and analyze the data.

For the next step we will be inputting the data into the appropriate columns. First record the names of the columns so that you can keep track of what column contains what information. I suggest keeping it simple. I simply named each column with what the data was it would contain, and made sure that I included the measurement that was being used (inches) to keep the person who might eventually evaluate your spreadsheet informed.

Now that your data and information is input into the appropriate columns, double check that the information is correct. Since we will be performing a math equation, having the correct numbers is vital to getting the correct output you need.

Now we will start the process of creating the equation. The first step we will perform to is highlight the cells that have data in them under the column of Male Height (Inches), or column A. But do not highlight the title of the column since there is no number value in that cell and it will cause Excel to become confused and not present your information accurately.

Now, go to the task bar and select the “Insert” tab. Scroll down the tab until you reach the option “Function”. This option allows you to select a pre made equation that can calculate the data you have selected to gain the numerical response that you want.

Click on the option "Function". This option allows you to input pre made equations that will calculate the data you have selected to gain the numerical response that you want. Function is where the equation selections are stored, so you can choose from a list format if you are not comfortable with creating your own equations in Excel.

When you select the option for “Insert” --> “Function”, a small box will appear on your spreadsheet. This box contains all the pre made equations you can choose from to select and input into the equation bar to have the data automatically calculated. They offer a range of equations from the average, sum, maximum, minimum and so on. For the purpose of this tutorial, select the option that says “AVERAGE”. Once you select AVERAGE, it will be entered automatically into the equation bar at the top of the spreadsheet like this:

"=AVERAGE("

If there are any numbers entered after the first parentheses after "AVERAGE(" you can delete those to make room for the data you will be inputting to get your average of the proper set of data.

Next, you will highlight the data that is below the column titled, “Male Height (Inches)”. By highlighting this data, you are letting Excel know that this is the data that you want to use to produce a certain result in the column of Average Male Height. The equation bar will display “=A2:A12”. This represents that the cells from A2, through A12 are the cells with data that is being used in this particular equation. These numbers will differ depending how many cells you use in each column to input data and what cells you want to use to generate your average.

The number that appears underneath the column titled Average Male Height (Inches) or column D, is the average of all the heights you have input into column A, or Male Heights (Inches). This function added up all of the numbers and then divided them by the number of heights you input, in this case it added the data in column A up and divided that sum by 11, thus yielding your answer for the average male height in your class from the heights in inches you provided. Here, the answer is 68.72, meaning that 68.7 inches, or five feet and seven inches is the average male height for your class.

Now that you have found the average height for the males in your class, it is time to move on to the female average height.

Repeat steps 5 through 10, but do it from the position of female heights. Initially, instead of selecting the blank cell underneath “Average Male Heights (Inches)”, insert the equal sign underneath the title “Average Female Heights (Inches)” in the first empty cell. Then, repeat the process of selecting the average from the Insert tab, and then the Function option, and then select the data that is underneath the title “Female Height (Inches)”, so cells B2:B12. Then, hit the enter, or return button on your keyboard and the average height for the female members of your class will appear underneath the “Average Female Height (Inches)” column. The average that appears here is 64.81818182 inches, which means that the average female height is 64.82 inches or five feet and four inches.

Now you have both of your averages listed in separate columns to distinguish between the data you used to get that average, and the answer that the data yielded. Finding the average on Excel is a useful and easy tool to posses and it is easily changed to manipulate any type of data you need to average. I hope my tutorial helped you and best of luck with all your data manipulating endeavors!