From data to report in one minute or less. Ajelix BI is launched! 🚀 Get started for free.

AVERAGEIF function MS Excel

What is AVERAGIF Function in Excel?

The AVERAGEIF function in Excel is a statistical function that allows users to calculate the average of cells that meet certain criteria, such as cells containing text, dates, or numbers within a specific range.

AVERAGEIF Function Description

This function is especially useful when you need to calculate the average of a large data set but only want to include certain values.

The AVERAGEIF function is a combination of the Average and If functions. The Average function calculates the arithmetic mean of a selected range of cells. The If function evaluates a logical expression and returns one value if the expression is true and another value if the expression is false.

To use the function, you must specify the range of cells that you want to analyze and the criteria for which the cells should be included in the average. You can also specify an optional range of cells that should be omitted from the average. An AVERAGEIF function is a great tool for quickly analyzing data sets that contain a large number of values. 

AVERAGEIF Function Syntax

=AVERAGEIF(range, criteria, [average_range])

Range: The range of cells that you want to evaluate.

Criteria: The criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged.

Average_range: (optional) The range of cells to average. If omitted, range is used.

AVERAGEIF function MS Excel - Excel Formula Cheat Sheet

How to use AVERAGEIF function in your workbook:

  1. Open your Microsoft Excel worksheet and select the range of cells that contain the data.
  2. Click the cell where you want the AVERAGEIF function to appear.
  3. Enter the AVERAGEIF function in the cell. The syntax for the AVERAGEIF function is: =AVERAGEIF(range, criteria, [average_range]).
  4. In the range argument, enter the range of cells that contain the data.
  5. In the criteria argument, enter the criteria for which to average the data. This can be a value, formula, or text. In the [average_range] argument, enter the range of cells that contain the data to average. This can be left blank if the range argument is the same as the average_range argument.
  6. Press Enter to calculate the result.
  7. The function will return the average of cells in the range that match the criteria.

Frequently Asked Questions

Why should I use Averageif?

Averageif is used to calculate the average of cells that meet a certain criteria. It is a useful tool when you need to calculate a subset of data out of a larger set.

What is the difference between average and Averageif in Excel?

Average is a function that calculates the arithmetic mean of a given set of numbers. Averageif is a function that calculates the average based on specific criteria. For example, you could use Averageif to calculate the average of all the numbers in a range that are greater than 10.

Struggling with your Excel formulas?

Try out for free AI Excel Assistant who can generate Excel and Google Sheets formulas from your text. Explain your formulas and give descriptions to the most complicated formulas. Translate your Excel files in 28 lanugages. Write and explain your VBA and Google Apps Scripts. Register for free!

Start Generating Formulas

Struggling with your Excel formulas?

Check out our blog for the latest tips and tricks to help you become an expert. We have the insider scoop on the best strategies and resources to help you stay ahead of the curve. Click here to read our blog and start your journey towards Excel and Google Sheets mastery today!

Explore our blog