Excel – AVERAGEIF function, calculating average with conditions

By Laura

AVERAGEIF is used for calculating average of cells with given conditions. The function for calculating average with conditions is more sufficient and convenient than the normal function for calculating average.

In Excel, AVERAGEIF is an advanced counting function which inherits from AVERAGE function – function for calculating average. In the following article, we will introduce to you the usage and formula of AVERAGEIF function – the function for calculating average with conditions through some examples. We hope that this function will help you calculate average value more quickly and accurately.


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

In which:

- Range: Scope containing number, name, respective reference with the value needing to be calculated.

- Criteria: Expression or number describing the conditions needed for calculating average value.

- Average_range: Cells needing to be calculated with average value. If this part is skipped, the range will be used.


- Cells in range contaminating TRUE, FALSE or blank, AVERAGEIF will skip.

- Average_range is not necessary to have the same size like range. AVERAGEIF function will determine from the top left cell as the first cell and then match it will range.

Example 1: We need to calculated the average salary of staff with salary higher than 4,000,000

- We input the formula: =AVERAGEIF(G5:G13,">4000000")

Example 2: We need to calculate the average salary of staff with a salary higher than 7,000,000

- We input formula: =AVERAGEIF(G5:G13,">7000000")

- We have the result #DIV/0! because no one has that salary.

Example 3: We need to calculate the average salary of male staff.

- We input formula: =AVERAGEIF(D5:D13, "male", G5:G13)

Example 4: We need to calculate the average salary of directors.

- We input formula: =AVERAGEIF(E5:E13,"=*director",G5:G13)

Example 5: We need to calculate average salary of staff without Marry letter in Family Name.

- We input formula: =AVERAGEIF(B5:B13,"<>Marry",G5:G13)

Above, we have instructed you to understand more about the syntax as well as usage of AVERAGEIF function to calculate average with given conditions through examples so that you can apply and handle this function on excel worksheet effectively.

Related keywords: Averageif function in excel, function for calculating average with conditions, averageif function in excel 2003.

Related Articles:
Excel – AVEDEV function, calculating absolute deviation average
Excel – Examples and guidance for VALUE function- a function for converting string to number
Excel – FREQUENCY function, counting frequently-appeared values
Excel – MAXA function, returning largest value
Excel – LARGE function, returning largest value number k
Excel – Dsum function - calculate sum in Excel




averageif function in excel


calculating the mean function conditional averageif function Excel 2003


Excel 2007 averageif jaw

, jaw averageif Excel 2010 Excel 2013 function averageif

Related software
  • Excel Calendar Template

    Excel Calendar Template is a program that allows you to create calendar templates designed by MS Excel with different month-year. You can easily add events, birthdays, anniversaries, holidays, etc. on your calendar templ ...

  • HashCalc - Calculating checksum of data.
  • Excel to Image Converter - Convert Excel files to images
  • Excel Power Expander - Expand formulas and utilities for Excel

Latest update

Copyright BETDOWNLOAD.COM © 2014 - All rights reserved