Tuesday, 19 March 2013

Excel AverageIF and AverageIFs Functions Syntax and Examples

Excel AverageIF and AverageIFs Functions
AVERAGEIF Function:
Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.

Syntax

=AVERAGEIF(range,criteria,average_range)

Range  is one or more cells to average, including numbers or names, arrays, or references that contain numbers.


Criteria  is the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 143, "143", ">123", "Car", "=*West","<>South", or B4. 


Average_range  is the actual set of cells to average. If omitted, range is used.


Remarks:

Cells in range that contain TRUE or FALSE are ignored. 
  • If a cell in average_range is an empty cell, AVERAGEIF ignores it.
  • If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
  • If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.
  • If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error 
Average_range does not have to be the same size and shape as range. The actual cells that are averaged are determined by using the top, left cell in average_range as the beginning cell, and then including cells that correspond in size and shape to range.


Examples:
Suppose we have the Sales Data as follows :

Prod
Sal_Region
Sal_Month
Net Sales
MotorCycle
East
Mar
521
Jeep
West
Jun
640
Car
North
Aug
386
MotorCycle
South
Mar
103
Jeep
East
Feb
327
Autocar
North
Jul
809
ByCycle
West
Sep
559
MotorCycle
East
Mar
870
Auto
South
Jan
769


AverageIf based on various Criteria as follows :
AverageIF Formula
Result
  =AVERAGEIF(A1:A10,"MotorCycle",D1:D10)
498.00
 =AVERAGEIF(A1:A10,"<>MotorCycle",D1:D10)
581.67
 =AVERAGEIF(A1:A10,"*Cycle",D1:D10)
513.25
 =AVERAGEIF(A1:A10,"Auto*",D1:D10)
789.00

---------------------------------------------------------------------------------------------------------------------
AVERAGEIFs Function:
Returns the average (arithmetic mean) of all cells that meet multiple criteria.

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

Average_range   is one or more cells to average, including numbers or names, arrays, or references that contain numbers.

Criteria_range1, criteria_range2, …   are 1 to 127 ranges in which to evaluate the associated criteria.

Criteria1, criteria2, …   are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged. For example, criteria can be expressed as 32, "32", ">32", "Car", or B4.

Remarks:
  • If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value.
  • If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
  • Cells in range that contain TRUE evaluate as 1; cells in range that contain FALSE evaluate as 0 (zero). 
  • Each cell in average_range is used in the average calculation only if all of the corresponding criteria specified are true for that cell.
  • Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same size and shape as sum_range. 
  • If cells in average_range cannot be translated into numbers, AVERAGEIFS returns the #DIV0! error value. 
  • If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.
  • You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Examples :
Suppose we have the Sales Data as follows :
Prod
Sal_Region
Sal_Month
Net Sales
MotorCycle
East
Mar
521
Jeep
West
Jun
640
Car
North
Aug
386
MotorCycle
South
Mar
103
Jeep
East
Feb
327
Autocar
North
Jul
809
ByCycle
West
Sep
559
MotorCycle
East
Mar
870
Auto
South
Jan
769

AverageIfs based on various Criteria as follows :
AverageIFs Formula
Result
 =AVERAGEIFS(D1:D10,A1:A10,"MotorCycle",C1:C10,"Mar")
695.50
 =AVERAGEIFS(D1:D10,A1:A10,"*Cycle",C1:C10,"<>Mar")
331.00


Thanks,
TAMATAM

1 comment:

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts