AVERAGEIFS function, calculating average with multiple conditions

By Laura

You have to calculate average score of subjects or numbers with given conditions. The consideration of conditions will be time-consuming without special tools in Excel. Let’s get to know how to use AVERAGEIFS to simplify this task.

AVERAGEIFS is the function for calculating average with multiple given conditions. This is an advanced function with more preeminent features compared to the normal AVERAGE function. In this article, betdownload.com will introduce to you the formula and usage of this function through some examples.

INSTRUCTIONS TO USE AVERAGEIFS FUNCTION IN EXCEL - EXAMPLE

Syntax: AVERAGEIFS (average_range, criteria_range1, criteria1, criteria_range2, criteria2…)

In which:

- Average_Range: Cells needing to calculate average value.

- Criteria_range1, criteria_range2, … : Areas for determining respective conditions.

- Criteria1, criteria2, ...: Given conditions with each criteria_range above.

Notes:

- If Average_range is a blank cell or a cell containing text, the function will return #DIV0! Error. If these cells containing True value, the function will default as 1 and containing False value, the function will default as 0.

- Blank cells in Criteria_range will be default as 0.

- If there is no value satisfying all conditions, the function also returns #DIV0! error.

In the below example, we can see the usage of AVERAGEIFS function in calculating average value with multiple given conditions.

Example 1: Calculate average salary of staff with salary higher than 3,000,000, but lower than 7,000,000.

- We input the formula as follows: =AVERAGEIFS(G5:G13,G5:G13,">3000000",G5:G13,"<7000000")

- Average salary returned by AVERAGEIFS function is 4,500,000.

Example 2: Calculate average salary of male staffs with Allowance of 500,000

- We input the formula as follows: =AVERAGEIFS(G5:G13,H5:H13,"500000",D5:D13,"male")

- The average salary returned by AVERAGEIFS function is 3,666,667.

Example 3: Calculate average salary of staff with salary higher than 9,000,000.

- We input the formula as follows: =AVERAGEIFS(G5:G13,G5:G13,">9000000”)

- The average salary returned by AVERAGEIFS function is #DIV/0!

Thus, in the above article, we have introduced to you the syntax as well as usage of AVERAGEIFS function so that you can easily apply it in calculating average with multiple given conditions. AVERAGEIFS function is developed and inherits from AVERAGEIF function – the function for calculating average with conditions.

Related keywords: averageifs function in excel, functions for calculating average with multiple conditions, averageifs function in excel 2003.

Related Articles:
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 - PRODUCT function for multiplying
Excel – HLOOKUP function - a search function in Excel

Comments

Comments

Tags:

AVERAGEIFS The AVERAGEIFS function in excel

,

calculating the mean function with multiple conditions

,

the function AVERAGEIFS The AVERAGEIFS Excel 2003

, Excel 2007 AVERAGEIFS The AVERAGEIFS jaw jaw AVERAGEIFS The AVERAGEIFS Excel 2010 Excel 2013 function AVERAGEIFS The AVERAGEIFS


Related software
  • ScreenCamera

    ScreenCamera is a compact application that is designed with outstanding features for user to split the webcam to transmit images to different applications as well as display your screenshot to your friends.

  • Multi Yahoo - Chat by various yahoo messenger nicks
  • YMulti Messenger - Log in and chat by lots of Yahoo nicks
  • Instant Messenger - Chat with friends on multi chat software at the same time.

Latest update

Copyright BETDOWNLOAD.COM © 2014 - All rights reserved