AVERAGEIF

Database Functions

(4.9/5)

Calculates the average of cells in a range that meet a single criterion. Essential for conditional averaging and data analysis.

Interactive Formula Tester

Criteria:

Formula:

=AVERAGEIF(">50, 10,20,30,40,50,60,70,80,90,100")

Complete Theory & Understanding

Master the fundamentals of Excel AVERAGEIF function

Core Concept

AVERAGEIF calculates the arithmetic mean of cells that meet a single condition. Unlike AVERAGE, it filters data before averaging, making it essential for conditional statistical analysis. When average_range is omitted, the function averages the values in the criteria range itself.

Why Use AVERAGEIF?

Average sales by condition
Average performance scores
Average defect rates
Average returns/costs

Key Characteristics

Single Criterion

Filters by one condition

AVERAGEIF(..., ">1000")

Multiple Criteria Types

Supports various operators and wildcards

>, <, =, <>, *, ?

Flexible Ranges

Optional separate average range

AVERAGEIF(region, "North", sales)

Text and Numeric

Works with both data types

AVERAGEIF(..., "Widget") or AVERAGEIF(..., ">1000")

Function Anatomy

=AVERAGEIF(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Sales Analysis

Average sales by condition

Performance Metrics

Average performance scores

Quality Control

Average defect rates

Financial Analysis

Average returns/costs

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=AVERAGEIF(range, criteria, average_range)

Required

range:

The range of cells to evaluate with criteria

Required

criteria:

Condition or criteria to apply to the range

Optional

average_range:

Range to average (if different from range)

Returns

Return Value:

Average of cells meeting the criteria

Description: Calculates average for cells in range that meet criteria

Interactive Examples

Basic Conditional Average

Average sales above threshold

Input Text:

"Sales > 1000"

Formula:

=AVERAGEIF(A1:A10, ">1000")

Result:

Average of values > 1000

Returns average of values exceeding 1000

VBA Implementation & Automation

Basic AVERAGEIF in VBA

Simple VBA implementation

' Basic AVERAGEIF in VBA
Sub AVERAGEIFExample()
    Dim result As Variant
    result = Application.WorksheetFunction.AverageIf(Range("A1:A10"), ">1000")
    MsgBox "Average: " & result
End Sub

' Dynamic criteria
Sub AverageByCriteria()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim criteria As String
    Dim result As Variant
    
    criteria = InputBox("Enter criteria (e.g., >1000)")
    result = Application.AverageIf(ws.Range("A1:A20"), criteria)
    ws.Range("C1").Value = result
End Sub

' Multiple calculations
Sub BuildAverageReport()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim criteriaList() As Variant
    Dim i As Integer
    
    criteriaList = Array(">0", ">100", ">500", ">1000")
    
    For i = 0 To UBound(criteriaList)
        ws.Cells(i + 1, 2).Value = criteriaList(i)
        ws.Cells(i + 1, 3).Value = _
            Application.AverageIf(ws.Range("A1:A100"), criteriaList(i))
    Next i
End Sub