AVERAGEIF Function in Excel


Home >> Excel Tutorials from Compute Expert >> Excel Formulas List >> AVERAGEIF Function in Excel



In this tutorial, you will learn AVERAGEIF in excel. AVERAGEIF function in excel will help you to get the average value of more than one digits based on a specific criteria.


Why do We Need to Learn About AVERAGEIF in Excel?


In processing numeric data in a spreadsheet, sometimes we want to do a calculation for the average value of a row/column of numbers in a group of data entries based on particular criteria for the data entries that we want to calculate. This can, of course, become complicated and takes a lot of time if done manually because we must filter the data entries one by one to see whether the number in the data entry needs to be calculated also based on the criteria that we have made.

An example is when we want to see the sales average of one particular product group from all of the entries in a spreadsheet. If the entries have the amount of more than hundreds or even thousands, then it will take a lot of time if we need to input the digit one by one from the product group that we want to process for the average analysis in our calculation formula in the spreadsheet.

We need the formula to do the calculation based on the evaluation of a criteria for each of the data entries. To answer this need, you can use AVERAGEIF function in excel to help you.

The formula of AVERAGEIF in excel can calculate the average of a row/column of numbers in your group of data entries based on the evaluation result of a criteria. AVERAGEIF in excel can be something necessary to learn if you often find and process data in a spreadsheet with numeric data to process and data which is very general and not specific.


What is AVERAGEIF Function in Excel?


AVERAGEIF function in excel is a function with the objective to calculate the average of a number column/row in a group of data entries that fulfill a particular criteria. AVERAGEIF in excel is an additional formula to look for an average value more specifically in the spreadsheet software which result can be hard to get if you use the standard calculation process.

A little explanation about the inputs in it as follows:

=AVERAGEIF(range, criteria, average_range)


Note:
  • range = row/column cell range where the part of the data entries that you want to evaluate with your criteria is
  • criteria = criteria to evaluate the data and select the numbers to be calculated the average of
  • average_range = row/column cell range where the average of the numbers from the data entries are calculated if they fit the data criteria



How to Use AVERAGEIF in Excel?


The following will explain how to write AVERAGEIF in excel. This AVERAGEIF function in excel needs three inputs: row/column where the data that you want to evaluate based on a criteria are, the criteria, and the row/column where the numbers to calculate the average from are.


Using AVERAGEIF Function in Excel


  1. Type equal sign ( = ) in the cell where you want to put the result in

    AVERAGEIF Function in Excel - Screenshot of Step 1

  2. Type AVERAGEIF (can be with large and small letters) and open bracket sign after =

    AVERAGEIF Function in Excel - Screenshot of Step 2

  3. Drag cursor on the row/column cell range where the data to evaluate based on your criteria are and type comma sign ( , )

    AVERAGEIF Function in Excel - Screenshot of Step 3

  4. Type your criteria then type comma sign. The criteria must be inside quote sign ( “” ) if it is in form of text or more than/less than criteria for the numbers

    AVERAGEIF Function in Excel - Screenshot of Step 4

  5. Drag cursor on the row/column cell range where the numbers in which you want to calculate the average based on your criteria are in. The cell of the digits must be inline vertically/horizontally with your evaluated data cells and should have the same number of cells

    AVERAGEIF Function in Excel - Screenshot of Step 5

  6. Type close bracket sign

    AVERAGEIF Function in Excel - Screenshot of Step 6

  7. Press Enter
  8. The process of AVERAGEIF function in excel is done!

    AVERAGEIF Function in Excel - Screenshot of Step 8



Exercise


After you understand how to use AVERAGEIF in excel from the tutorial steps above, now you can practice what you have understood by doing the exercise below!

Download the exercise file and answer the questions! Download the answer key file to check your answers if you have done the exercise questions or probably when confused about how to answer them.

Link to download the exercise file:
Download here

Questions

  1. What is the average of production amount from machine A in the period?
  2. What is the average reliability of machine D in the period?
  3. What is the average of production hours from machine C in the period?

Link to download the answer key file:
Download here


Additional Note


If you want to calculate average normally or with more than one criterion, you can learn how to do it in another part of Compute Expert tutorial if you don’t know how!



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on




Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert