Using the AVERAGE Function in Excel

Using the AVERAGE Function in Excel
Coming up next: How to Use the SUMIF Function in Excel

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:01 Finding the Average
  • 0:18 The Average Function
  • 1:20 Using Average in a Formula
  • 3:05 More Complex Formulas
  • 6:05 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed
Lesson Transcript
Instructor: Karen Sorensen

Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.

Average is calculated by adding numbers together, then dividing the total by the number of numbers. This lesson will explain how to use the AVERAGE function in Excel.

Finding the Average

Finding the average value of a range of data is easy when using Excel. The average is the result of adding several amounts together, then dividing the total by the number of separate amounts. This lesson will explain how to use the AVERAGE function in Excel and give examples of how the function is used in formulas.

The AVERAGE Function

Excel can handle just about any type of mathematical calculation, including finding averages. It's much easier to write a formula, rather than leaving Excel to get out your trusty calculator to run the numbers and then enter them back into a worksheet.

Writing formulas that work require the correct syntax, or language, and structure. The syntax for this formula is =AVERAGE (range). A cell reference or range is more often used in this type of formula, rather than the data itself. But you can use both.

An advantage to the AVERAGE function, and many other functions in Excel, is that Excel only reads the number in a cell. For instance, whether the numbers are entered into the cell range directly or are the result of a calculation or another formula, the AVERAGE function just sees a number. Alright it's time to look at an example.

Using AVERAGE in a Formula

Imagine you just started a new job serving at the local pub. In your first month, you worked 13 shifts. You have been keeping track of your tips and now you want to find out the average tip total for each shift. This will help to establish a budget going forward. In your worksheet, column A has each row titled, Shift 1 through Shift 13. You have entered your total tips for each shift in column B. At the bottom, you would like to enter a formula to calculate the average. Let's enter the following formula in cell B16: =AVERAGE(B3:B15).

sample Excel sheet

And that's it! Super easy. Just tell Excel to average and give it the range where the numbers are located.

Now, one thing that is nice about using the AVERAGE function is that Excel only counts the number of cells that have a number. For instance, let's assume you use this same worksheet for next month. You have copied the formula from B16 to C16. However, you only worked 11 shifts this month.

When you entered the formula, your range included cells for 13 shifts. What happens if you leave 2 shifts empty? Will you still get the correct calculation? Will Excel calculate the average based on 13 shifts or 11?

The answer is 11. Excel will ignore the empty cells and still return the correct value. When Excel calculates the average, it will only count the cells that have a number - and that's including the zero (0).

More Complex Formulas

Okay, let's take this a bit further and try something a little more complex. I am a baseball fan, and I tend to review the player stats every now and then. I have also kept my own stats on players, using Excel worksheets. I have a sample worksheet that details a player's pitching results for the last 15 games. Using our AVERAGE function, let's calculate the players ERA, or earned run average.

Sample spreadsheet

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

Register to view this lesson

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
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 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 risk-free for 30 days!
Create an account
Support