Copyright

How to Use the AVERAGEIF Function in Excel

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: How to Use the COUNTIF 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 Average When…
  • 0:34 Review of the Average Function
  • 2:25 The AVERAGEIF Function
  • 4:00 Step By Step
  • 5:59 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 Speed Audio mode

Recommended Lessons and Courses for You

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.

If you need to take your AVERAGE function to the next step, adding the IF function will give you more power. This lesson will first review the basic AVERAGE function, then demonstrate using examples, how to include IF in your formulas.

Average When Conditions Are Met

The AVERAGEIF function is similar to the standard AVERAGE function. The difference? You can specify to calculate the average, only when a particular condition has been met. This adds another layer of complexity but also gives you additional power to run calculations under certain conditions. Confused? It might be a bit intimidating, but really, it is a fairly simple function of Excel.

In this lesson, we will take a look at a couple examples and learn how to use the AVERAGEIF function.

Review of the Average Function

Before we get too far, let's do a quick review of the standard AVERAGE function. Refreshing the memory will be helpful when we add the IF component to a formula.

The term average refers to a mean. In the case of using it in Excel, the function is used to average a range of numbers to identify the mean. For instance, take the numbers 2, 4, 6 and 8. The average (or mean) would be 5. How does Excel actually perform the calculation to reach an answer of 5? By adding all the numbers together (20) and dividing by the number of digits added (4). Thus, 20 / 4 = 5. Next question, how is this used in a formula?

Let's say you are preparing for a local 5k run. Over the last month, you have been training, running five miles each day. You have also tracked your time. In fact, you created a spreadsheet to track each run, the distance, the date and time (minutes) it took to complete the five miles. On your worksheet, times are displayed in C3 through C27. You would like to find the average time for your 5k run. You have 24 practice runs, ranging from 35 to 60 minutes. To get the average, enter the following formula.

=AVERAGE(C3:C27)

In our example, the average time for the 5k run is 45.996 minutes. Perfect! Now you have an idea of approximately how much time it should take you to run the 5k next week.

The AVERAGEIF Function

Alright. Let's step it up a notch. Adding the IF variable to the AVERAGE function is not much different. The formula will still find the mean or average. However, this time, the average is based on specific circumstance or criteria. Let me explain.

In the example we used earlier, we tracked 24 practice runs. Assume when you started, you had taken a break and not been running for some time. While your usual time is less than 50 minutes, the first four runs were more or less to get back up to speed. You would like your average time only if it's less than 50 minutes. In other words, average only the times that are 49.99 minutes or less. Make sense? Here is the formula:

=AVERAGEIF(C3:C27,''>49.99'')

Notice that the criteria, less than 49.99, is in quotation marks. Now you have a more accurate average time of 43.26.

Criterion can be numbers, strings, or references. For example, valid criteria could be 49.99 or >49.99. You can also reference a cell. For instance, A4.

Step by Step

Okay, now that you have a good understanding of how the AVERAGEIF function works, let's take a look at using the function in another example. This time, I will take it step by step.

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