Leading the way in Microsoft Office Development
 Home|Excel|Word|PowerPoint|Consultancy|Feedback|Contact 
 Microsoft Excel > Functions User Defined > AVERAGEIF< Previous | Next > 
 

AVERAGEIF(rgeCriteria, sCriteria, rgeAverageRange)

 
 

Returns the average of a range of values that match a certain condition.

 

 
rgeCriteriaThe range of cells containing the criteria you want to check.
sCriteriaThe criteria value you want to match.
rgeAverageRangeThe range of corresponding values you want to average.
 

 

REMARKS

 
 

  • There is an Array Formula equivalent if you do not want to use the user defined function.

    		 
     

  • This user defined function is similar to the built-in SUMIF() function.

    		 

     
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    Option Explicit

    Public Function AVERAGEIF(ByVal rgeCriteria As Range, _
                              ByVal sCriteria As String, _
                              ByVal rgeAverageRange As Range) As Single
              
    Dim iconditioncolno As Integer
    Dim inumberscolno As Integer
    Dim lrowno As Long
    Dim lmatch As Long
    Dim sngaverage As Single
    Dim vcellvalue As Variant
              
       Call Application.Volatile(True)
              
       iconditioncolno = rgeCriteria.Column
       inumberscolno = rgeMaxRange.Column
        
       For lrowno = 1 To rgeCriteria.Rows.Count
          vcellvalue = rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno - 1, inumberscolno).Value
          If IsNumeric(vcellvalue) = True Then
             If (rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno - 1, iconditioncolno).Value = sCriteria) Then
                lmatch = lmatch + 1
                sngaverage = sngaverage + rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno - 1, inumberscolno).Value
             End If
          End If
          
          If sngaverage <> 0 And IsEmpty(vcellvalue) = True Then Exit For
       Next lrowno
        
       AVERAGEIF = sngaverage / lmatch
    End Function
    		   

     

    Example

    		 
       

     

    Array Formula

    		 
     

    To find out more about Array Formulas please refer to the Array Formulas section.

    		 
     

    To find out more about the individual functions please refer to the AVERAGE() function and IF() function pages.

    		 
     

    This example illustrates how to use an array formula to obtain the average value in a column based on a condition.

    		 
       
     

    The formula in row 16 returns the average in the "Sales" department.

    		 
     

    The formula in row 17 returns the average in the "IT" department.

    		 
     

    The formula [C3:C14="Sales"] returns an array of 1's and 0's indicating if the value in column "C" satisfies the condition.

    		 
     

    Every number 1 in this array represents the value True in the IF statement and therefore has its corresponding value returned from column "D".

    		 
     

    Every number 0 in this array represents the value False in the IF statement and therefore an empty string is returned in this case. The MAX function will ignore any string values.

    		 
     

    The use of the empty string is to ensure that the formula works with both positive and negative numbers.

    		 
     

    This array of numbers is then passed to the AVERAGE() function to obtain the largest number.

    		 
     

    Remember that you must press (Ctrl + Shift + Enter) to enter the formulas.

    		 

     

    Things to Remember

    		 
     

  • You cannot use column references (such as "A:A" or "D:D") in your array formulas.

    		 
     

  • Any cell ranges passed to an array formula must have the same number of rows. This is to ensure that all the temporary arrays used are the same length.

    		 

     Copyright © 2004-2007 Better Solutions Limited. All Rights Reserved.< Previous | Top | Next >