How to use AVERAGEIF function in Excel
To calculate the average of the data in the table on Excel, we will use the AVERAGE function, the familiar average calculation function. However, almost calculating the average value on Excel will come with many different conditions. And so, you need the AVERAGEIF function to be conditionally averaged.
AVERAGEIF will proceed with the average plus the zoned data areas in the table, along with the conditions that the user set. How to use the AVERAGEIF function to calculate conditional averages on Excel will be guided by Network Administrator in the article below, through each specific example.
- These are the most basic functions in Excel that you need to understand
- Forgot password protected Excel file, what should you do?
The AVERAGEIF function syntax on Excel is: = AVERAGEIF (range, criteria, [average_range]) .
Inside:
- Range is the range for which the average value can be calculated, including numbers, names, references, arrays.
- Criteria are conditions in the form of numbers, expressions, cell references or text to determine the average value. Conditions are in "".
- Average_range includes cells that need to calculate the average value. If Average_range is empty, it will replace with Range to calculate the average value.
Note when using AVERAGEIF function :
- Empty cells in Average_range, TRUE, FALS and AVERAGEIF functions will be ignored.
- If Range does not contain a value or contains a string function, the result will indicate an error.
- If the Criteria is empty, the function is 0.
- If we determine that there is no value matching the criteria, we will get an error.
- Average_range does not need to be mandatory in the size of the Range range. If not, the AVERAGEIF function will determine from the top left cell as the first cell and then match the range.
We will proceed to calculate the average score in the data table below.
Example 1 : Calculate the average score of students with scores greater than 5 .
Enter the formula = AVERAGEIF (D2: D6, "> 5") and press Enter.
The result will look like the image below.
Example 2 : Calculate the average score of students with scores less than 5 .
Enter the formula = AVERAGEIF (D2: D6, "<5") and press Enter.
The end result is # DIV / 0! Because Excel relies on user-defined conditions and has no value corresponding to that condition, there are no students below point 5.
Example 3 : Calculate the average of male sex students .
Here you need to delineate 2 areas as Gender to filter the condition and the Score area to calculate the average score.
The syntax used here is = AVERAGEIF (C2: C6, "Male", D2: D6) , press Enter to perform.
The end result will look like the image below.
Example 4 : Calculate the average of students who do not have the Thi in the Last and Last names column .
We will have the formula = AVERAGEIF (B2: B6, "<> Marketing", D2: D6) and press Enter.
The result is the average of the classification score by name as shown below.
The above is a tutorial on how to use the AVERAGEIF function with a common usage syntax, and each case with different conditions. You need to specify the area to be calculated as well as the conditions to apply the AVERAGEIF function accordingly.
I wish you all success!
You should read it
- Basic Excel functions that anyone must know
- How to fix the SUM function doesn't add up in Excel
- How to use Hlookup function on Excel
- How to use the LEN function in Excel
- How to use Excel's VALUE function
- How to use the DAVERAGE function in Excel
- How to use MID functions to get strings in Excel
- How to use the SUBTOTAL function in Excel
May be interested
- How to use Excel's VALUE functionexcel's value function will convert a string to a digital form, which can be combined with other functions such as left function, right function, mid function.
- How to use the WRAPROWS function in Excelhaving trouble with handling large data sets in excel? then learn how to use the wraprows function to split them into more manageable rows.
- How to use the IFS function in Excel 2016the 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.
- How to use the NPER function in Excel to plan loans and savingsdo you want to effectively manage and control your personal finances? then we invite you to learn how to use excel's nper function.
- How to use the function to delete spaces in Exceldeleting white space with functions in excel makes it easier for users to handle content, instead of traditional editing.
- How to fix the SUM function doesn't add up in Excelin the process of summing with sum in excel, you will encounter some errors such as not jumping the number, not adding the sum. so how to handle this problem?
- How to use the SUBTOTAL function in Excelthe subtotal function in excel is used in many different cases, helping you to sum subtotals in a list or database, unlike the sum function in excel such as counting cells, calculating average, finding the largest / smallest value. or sum the filtered list values in excel
- Save time with these text formatting functions in Microsoft Excelmicrosoft excel is a main application for anyone who has to work with numbers, from students to accountants. but its usefulness extends beyond a large database, it can do a lot of great things with text. the functions listed below will help you analyze, edit, convert, change text and save many hours of boring and repetitive tasks.
- SUMPRODUCT function in Excel: Calculates the sum of corresponding valuesthe sumproduct function is an extremely useful function when you have to deal with a lot of data numbers in microsoft excel. here are the things you need to know about the sumproduct function in excel.
- How to use the SUMIF function in Excelthe sumif function in excel is a function used to compute values in a specified range. the sumif function can be used for summing cells based on the date, data and text that are connected to the specified area.