How to Find Average Based on Multiple Criteria in Microsoft Excel 2010

If you want to find the average of numbers in a column based on multiple conditions,you can use the “AVERAGEIFS”, “AVERAGE” & “IF” functions to retrieve the output in Microsoft Excel 2010.

AVERAGEIFS: Finds average (arithmetic mean) for the cells specified by a given set of conditions or criteria.

Syntax: =AVERAGEIFS(average_range,criteria_range,criteria,…)

average_range: The range of cells for that you want to derive an average.

criteria_range: Therange ofcells from which you want criteria to evaluate.

criteria: The criteria defines which cells to average.

 

AVERAGE function can be used to find the average value or arithmetic mean of values in a selected range of cells.

Syntax: =AVERAGE(number1,number2,…)

Arguments

number1, number2,……number n are numeric values they can be numbers or names, arrays, of references that contain numbers.

 

IF:-This function checks if a condition you specify is TRUE or FALSE. If the condition returns TRUE then it returns preset value, and if the condition returns FALSE then it returns another preset value.

Syntax = IF(logical_test,value_if_true,value_if_false)

 

logical_test: Logical test will test the condition or criteria.If condition meets then it returns the preset value, and if the condition does not meet then it returnsanother preset value.

value_if_true: The value that you want to be returned if this argument returns TRUE.

value_if_false: The value that you want to be returned if this argument returns FALSE.

 

Let us take an example:

We have some Codes in column A & B & their corresponding numbers in column C.There are two criteria in cells E2 & F2 from column A & column B respectively.We need a formula that will retrieve the output in cell G2 based on the conditions set in cells E2 & F2.

img1

  • In cell G2, the formula would be
  • =AVERAGEIFS(C2:C11,A2:A11,E2,B2:B11,F2)

img2

  • The output in cell G2 has found the highlighted values in column C i.e. ((1000+800)/2=900)
  • If we change the criteriathen the result would automatically change & update the output accordingly.
  • For example, if we change the criteria in cell E2 from CC to AA then the output will be 800.

img3

  • In the above shown snapshot, the output is 800 because there is only one condition found that is matching both the criteria.

We can achieve the same output by applying the AVERAGE & IF functions together.

  • In cell H2 the formula would be
  • =AVERAGE(IF(A2:A11=E2,IF(B2:B11=F2,C2:C11)))
  • This is an array formula which requires the formula to be enclosed with curly brackets by pressing CTRL + SHIFT + ENTERon your keyboard.
  • {=AVERAGE(IF(A2:A11=E2,IF(B2:B11=F2,C2:C11)))}

img4
Note:- If we will not use CTRL + SHIFT + ENTER then the result would be wrong.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube