AVERAGEIF function (returns the average value according to the condition) in Excel

The AVERAGEIF () function helps you calculate the average of the data cells with a given voltage. If you do not know how to use the function, you can follow the article below.

Article instructions syntax and how to use the AVERAGEIF () function in Excel.

AVERAGEIF function (returns the average value according to the condition) in Excel Picture 1

Description

The AVERAGEIF () function returns the average value of all selected cells that meet the conditions you specify.

Syntax

= AVERAGEIF (range, criteria, average_range)

Inside:

- range: is one or more cells to calculate the average value, range can be numbers, range names, arrays or references that contain numbers.

- criteria: a condition in the form of numbers, expressions, cell references or text that identifies the cells that will calculate the average value.

- average_range: the set of cells that really need to calculate the average value, if average_range is blank, the range will be used to calculate the average value.

Note

- The empty cells in average_range are ignored.

- In range containing logical values (TRUE or FALSE) are also ignored.

- If range is empty or contains strings, the function AVERAGEIF () will give an error

. - In the criteria containing an empty (empty) cell, the function will treat it as 0.

- If no cell in range meets criteria criteria, AVERAGEIF () function will return an error.

- You can use wildcards such as question marks (?) Instead of a certain character, asterisks (*) instead of a certain string in criteria. When the condition in criteria is a? or the *, the more you type ~ before it.

- Average_range and range are not necessarily the same size, the actual average cells will use the top left cell of average_range as the starting cell, and include additional cells corresponding to the size of the range. To better understand you see the example below:

AVERAGEIF function (returns the average value according to the condition) in Excel Picture 2

For example

Example 1:

AVERAGEIF function (returns the average value according to the condition) in Excel Picture 3

Example 2:

AVERAGEIF function (returns the average value according to the condition) in Excel Picture 4

Hopefully, with the above two examples, you will better understand how to use the AVERAGEIF () function in Excel. Good luck!

Isabella Humphrey

Update 19 May 2020

You should read it

May be interested

Basic Excel functions that anyone must know
the basic functions in excel such as the excel function, the excel statistics function we summarized below will be very helpful for you who often have to work on excel spreadsheets, especially in the field of accounting. let's refer to offline.
WEIBULL function - The function returns the Weibull distribution in Excel
the function performs the calculation and returns the weibull distribution. based on this distribution to analyze reliability in theory such as calculating the average life of the device or used in the field of meteorology, hydrology and weather forecast.
AVERAGEIFS function - The function returns the average of the arguments that meet multiple conditions in Excel
averageifs function: the function returns the average of the arguments that meet multiple conditions. syntax: averageifs (average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
How to use the IFS function in Excel 2016
the ifs function in excel is a logical function introduced in excel 2016. this function is a function that replaces the nested if function and is much easier to use. the ifs function checks one or more conditions and returns a value that meets the first true condition.
NORM.S.INV function - The function returns the inverse of the normalized distribution with an average value of 0 and a standard deviation of 1 in Excel
norm.s.inv function: the function returns the inverse of the normalized distribution with an average value of 0 and a standard deviation of 1. support from excel 2010 onwards. syntax: norm.s.inv (probability)
Daverage function in Excel - Daverage function calculates the average value with a given condition
daverage function can help you calculate the average value for a given condition.
COVARIANCE.P function - The function returns the covariance of a set, the product of the average of degrees of instruction for each pair of data points in Excel
covariance.p function: returns the covariance of the population, the average of the product deviations for each pair of data points in the two data sets syntax: covariance.p (array1, array2)
NORMDIST function - The function returns the normal distribution with the standard deviation and the average value specified in Excel
the following article details the normdist function - the function returns the normal distribution in excel.
RANK.AVG function - The function returns the rank of a number in a list of numbers in Excel
rank.avg function: the function returns the rank of a number in a list of numbers whose size is in correlation with other values. if multiple values have the same rank -> the function returns the average rank. support functions from execl 2010 version and above
How to use MAXIFS function in Excel 2016
maxifs function in excel is a statistical function that returns the largest value based on one or more conditions from the specified cells. maxifs function was introduced in ms excel 2016.