Catalog

How To Use AVERAGEIF Function in Excel: A Comprehensive Guide for Excel Users

September 4, 2023 1.9K views

WPS Spreadsheet could be an alternative to Microsoft Office Excel. It includes 100's of built-in formulas, pivot tables, and more.

How to use the AVERAGEIF function in Excel

The AVERAGEIF function in Excel facilitates users in calculating the average of a specified range of cells that meet a specific condition. The syntax for implementing the Excel AVERAGEIF function consists of 3 arguments and is as follows:

Syntax: AVERAGEIF (range, criteria, [average_range])

Arguments:

Range: “Range” expresses the range of cells that you want to analyze based on a given criteria.

Criteria: “Criteria” is referred to when the cells in a particular range must meet to be added to the calculation of the average.

Average_range (optional): The range of cells you intend to calculate the average of. If skipped, the function will use the range parameter as the average range.

In the above-mentioned argument, the first two are compulsory, unlike the third one.

Excel AverageIF function working



Excel AverageIF function working




For a more promising comprehension of the Excel AVERAGEIF function, let's demonstrate an example. Consider you have a dataset of several years of sales of a specific car model in column A and the corresponding number of units sold in column B.

excel AVERAGEIF Function sample



excel AVERAGEIF Function sample




Let’s use this data to determine the average number of cars sold per year. While the Excel Average Function can provide us with the basic average, what if we want to consider only the years when the number of units sold exceeded 10? That's when the Excel AVERAGEIF function comes into play. By applying conditions to our average calculation, we can gain deeper insights into our data.

Step 1: Click on an empty cell where we want the average to be calculated.

Step 2: Next, we will enter the Excel AVERAGEIF formula in the empty cell B10. Make sure to start the formula with Equals Sign.

Excel begin AverageIF function



Excel begin AverageIF function




Step 3: Using the cursor, select the range for the AVERAGEIF function; i.e.B2:B9.

Excel AVERAGEIF range



Excel AVERAGEIF range




Step 4: We want our average to include only the years where the number of units sold exceeded 10, so the criteria for our function will be “>10”.

Excel AVERAGEIF criteria



Excel AVERAGEIF criteria




Step 5: Close the parentheses and press “Enter” to allow Excel to calculate the average number of units sold each year.

Excel AVERAGEIF function results


Advanced Tips You Must Know When Using The AVERAGEIF Function

While utilizing the AVERAGEIF Function, there are some aspects that you should be aware of. Here is a brief overview of these considerations:

  • TRUE and FALSE values: The function only considers numerical values for the calculation, so it ignores any logical values (TRUE or FALSE) in the range.

Excel AverageIF string values



  • Empty cells: The function does not include any empty cells in the range when calculating the average, so they do not affect your average result.

Excel AverageIF empty cells



  • #DIV/0! error: The function will return a #DIV/0! error if all the cells selected as range fail to meet the criteria, meaning there are no values to average.

Excel AverageIF error



  • Specific Range: The function requires a specific range of cells as an argument, an array cannot be substituted instead.

  • Average_range Calculation: The function allows you to choose a different range of cells for calculating the average, which can be larger than the main range used for the criteria. The calculation starts from the top-left cell of the Average_range, and only the matching cells (based on the criteria) from both ranges are averaged. Non-matching cells are ignored in the average calculation.

  • Wildcards and case-sensitivity: The function supports the use of wildcards (* and ?) for flexible matching, and it treats uppercase and lowercase letters the same when evaluating criteria.

Examples of Using Averageif Function in excel

Understanding how to use logical operators (>, , =) and wildcards (*, ?) in the criteria can help perform more complex and flexible conditional averaging operations.

Here's a table to illustrate the criteria for using the AVERAGEIF Function:

Target

Criteria

Cells equal to a hundred

100 or "100"

Cells greater than 75

">75"

Cells that are not blank

""

Cells that are blank

(empty quotes)

Cells not equal to "Red"

"red"

Cells equal to "Red"

"red"

Cells less than or equal to 100

"

Cells that begin with "X"

"x*"

Cells less than the value in cell A1

"

Cells less than today's date

"


Let’s consider some examples for a comprehensive understanding:

Using AVERAGEIF Text Criteria

Here's a simple example of using the Excel AVERAGEIF function with text criteria.

Let's say you have a list of students and their corresponding scores on a test:

Excel Student score sheet




Now, if we want to calculate the average score of all students who scored above 80, we will be using the Excel AVERAGEIF function with “>80” as our text criteria.

Step 1: Click on an empty cell, i.e. B7, and enter the AVERAGEIF formula.

Excel enter AVERAGEIF function



Step 2: We will be selecting the test score of the 5 students as our range.

Excel AVERAGEIF range argument



Step 3: As our criteria, we will enter >80 as we want the AVERAGEIF function to include a test score above 80. Make sure the criteria is entered inside the inverted commas.

Excel AVERAGEIF text criteria



Step 4: Press “Enter” after closing the parentheses. Excel will display the Average score of students excluding scores below 80; Mary’s score of 78 will be excluded from the calculations.

Excel AVERAGEIF results



Using AVERAGEIF with Dates

Here's a simple example of using the AVERAGEIF function with dates

Let's say you have a list of sales data with dates and corresponding sales amounts:

Excel sample Sales data



We can utilize the Excel AVERAGEIF function to calculate the average sales from February till the current date. Our criteria will be ">2/1/2023".

Step 1: Select an empty cell to carry out the Average calculation. Enter the AVERAGEIF formula.

Excel AVERAGEIF



Step 2: We will select the date column as our range for the AVERAGEIF function. Since our criteria will be based on the date, the date column becomes our target range for the calculation.

Excel AVERAGEIF range



Step 3: For our criteria argument, we will be entering “>2/1/2023”. Make sure the date format is followed correctly; MM/DD/YYYY

Excel AVERAGEIF criteria



Step 4: For our final argument, average_range, we will select the "Sales Amount" column. This allows us to calculate the average based on the values in the Sales column.

Excel AVERAGEIF average range



Step 5: Press “Enter” to display the results.

Excel AVERAGEIF Function with dates results



The Average sales from February till the current date will be displayed.

Using AVERAGEIF with Criteria “greater than 0”

Let's consider a dataset with the following sales figures for different products to understand how Excel AVERAGEIF function can be used with criteria “greater than 0”:

Excel Product sales data



We want to find the average sales amount for products that have sales greater than 0. We can use the AVERAGEIF function with the criteria "greater than 0" to achieve this.

Step 1: Select an empty cell where you want to carry out the average calculation.

Step 2: Enter the Excel AVERAGEIF Function. Make sure to start the function with Equal Sign.

Excel AVERAGEIF



Step 3: For the range argument, select the range of cells that contain the sales figures. In the example dataset, i.e.B2:B6.

Excel AVERAGEIF range



Step 4: For the criteria argument, enter ">0", with quotes. This specifies that we want to calculate the average of sales amounts that are greater than 0.

Excel AVERAGEIF criteria greater than 0



Step 5: To display the results, hit "Enter"

Excel AVERAGEIF function greater than 0 results



The Excel AVERAGEIF function will calculate the average of sales amounts for products that have sales greater than 0.

Benefits of Using WPS Office

The AVERAGEIF application methods apply to both Microsoft Excel and WPS Office Spreadsheet, however, WPS Office provides it for free of cost. Not only this, but the WPS Office offers various other reasons why it is better than Excel.

  • Price: If you're on a tight budget or looking for a pocket-friendly option, WPS Office provides a free version packed with powerful features.

  • Compatibility: WPS Office ensures its compatibility with multiple types of file formats, including Excel.

WPS Office compatibility



  • Features: With its pool of many tools, it ensures an optimal experience for its users.

  • User-friendly Interface: Its interface is kept beginners friendly so that new users do not face much hassle.

  • Cloud Integration: WPS Office provides cloud storage integration, allowing users to save and access their files conveniently from cloud storage services like Google Drive and OneDrive.

  • Multi-platform Support: WPS Office offers multi-platform support, making it accessible across various operating systems such as Windows, macOS, Linux, Android, and iOS.

WPS Office

How to use AVERAGEIF Function in WPS

WPS Office, a powerful productivity suite, offers similar features to MS Excel, but with a significant advantage – it's free! Just like in Excel, you can effortlessly use the AVERAGEIF function in WPS Office to calculate averages based on specific conditions.

Here's a simple example of using the AVERAGEIF function in WPS Office. Let's say you have a list of employees and their corresponding monthly sales:

WPS Office sales data



Step 1: Choose an empty cell where you want to display the average results.

Step 2: Next, enter the AVERAGEIF function.

WPS Office enter AVERAGEIF Function



Step 2: Our range will be the monthly sales column since we want to calculate the average monthly sales.

WPS Office AVERAGEIF function range



Step 3: For our criteria, we will be entering “>1000” since we want to use the AVERAGEIF function in WPS Office to calculate the average sales for employees who achieved sales greater than 1000.

WPS Office AVERAGEIF Function Criteria



Step 4: Press Enter to calculate the average monthly sales. WPS Office will display the average sales amount for the selected employees who meet the criteria.

WPS Office AVERAGEIF Function
Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

FAQs about Excel AVERAGEIF

Q1. Can I use AVERAGEIF with cell reference and how?

Yes, you can use the AVERAGEIF function with cell references in both Microsoft Excel and WPS Office Spreadsheet. Users can directly use a cell reference for a specific value, like AVERAGEIF(range, reference cell), or use logical operators in combination with cell references, such as AVERAGEIF(range, ">"&cell reference) to find averages based on specific conditions.

Q2. How to Average Cells Based on Partial Match?

To average cells based on a partial match, you can use the AVERAGEIF function along with wildcard characters (* and ?) in Microsoft Excel or WPS Office Spreadsheet. Wildcards allow you to match text patterns rather than exact matches, making it possible to perform partial matching for data analysis.

Q3. Can I use other useful formulas both in WPS Office and Microsoft Excel?

Yes, both WPS Office and Microsoft Excel use a wide range of formulas other than the AVERAGEIF function. These include SUM, COUNT, IF, VLOOKUP, HLOOKUPIFERROR, and many more.

Discover The Potential of AVERAGEIF function

In this article, we delved deeply into the functionality of the AVERAGEIF function in both Excel and WPS Spreadsheet. While both applications offer the same implementation of the function, WPS Spreadsheets takes the lead due to its superior user-friendliness and intuitive interface. Download WPS Spreadsheet today to experience seamless data manipulation and analysis.



15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.