Conditional Calculation of Average Values

The AVERAGEIFS function is utilized in both Microsoft Excel and Google Sheets to compute the average of ranges based on one or more specified criteria. It specifically calculates the average of cells that meet certain conditions, making it highly beneficial for data analysis, especially when you need to determine averages of data that meet specific standards.

Function Syntax and Usage

The general syntax of the AVERAGEIFS function is as follows:

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])

• average_range: The range of cells for which the average is calculated.
• criteria_range1: The range of cells that the first condition applies to.
• criteria1: The first condition that must be met.
• criteria_range2, criteria2, …: Optional additional ranges and their associated conditions.

For example, consider a scenario where a teacher wants to calculate the average physics grades of students in a class, but only for grades above 50 and for students who are in grade 10:

=AVERAGEIFS(C2:C100, B2:B100, ">50", A2:A100, "10")

This formula calculates the average of the physics grades in the range C2:C100, but only includes those grades where B2:B100 indicates the grade is above 50 and A2:A100 indicates the student is in grade 10.

Practical Usage Scenarios

Scenario 1: Sales Data Analysis

In a corporate setting, calculating the average sales quantities of various products based on a specified price range might be required. For instance, to compute the average sales of products priced between 100 and 300 Turkish Lira:

=AVERAGEIFS(C2:C100, B2:B100, ">=100", B2:B100, "<=300")

This formula computes the average of sales quantities in the range C2:C100, considering only those sales where the prices in B2:B100 are between 100 TL and 300 TL.

Scenario 2: Student Performance Evaluation

In an educational environment, there might be a need to calculate the average final scores for a mathematics course, but only for students who scored above the passing grade of 60:

=AVERAGEIFS(C2:C100, C2:C100, ">60")

This formula calculates the average of the mathematics scores found in C2:C100, but only includes scores that are above 60.

Each scenario can be customized for different situations, where criteria are detailed to suit different data sets. The AVERAGEIFS function remains a crucial tool for advanced Excel users, rendering data analyses more meaningful and tailored.

Daha fazla bilgi: https://support.microsoft.com/tr-tr/office/çokeğerortalama-işlevi-48910c45-1fc0-4389-a028-f7c5c3001690