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

If you want to find the average of numbers in a column based on asingle condition,you can use the AVERAGEIF, AVERAGE & IF functions to retrieve the output.

AVERAGEIF: Finds average (arithmetic mean) for the cells specified by a given condition or criteria.

Syntax: =AVERAGEIF(range,criteria,average_range)

range: Therange ofcells from which you want the criteria to be evaluated.

criteria: The criteria define which cells to average.

average_range: The range of cells that you want 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 the condition is met then it returns the preset value, and if the condition isnot met 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 & their corresponding numbers in column B.There is a criteria in cell D2.We need a formula that will retrieve the output in cell E2 based on the condition set in cell D2.

img1

  • In cell E2, the formula would be
  • =AVERAGEIF(A2:A11,D2,B2:B11)

img2

  • The output in cell E2 has found the highlighted values in column B 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 D2 from CC to AA then the output will be 783.33.

img3

  • In the above snapshot, the output is 783.33((900+800+650)/3=783.33)

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

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

img4

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

Leave a Reply

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

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