Excel: Use AVERAGEIFS with OR Condition

By /

Excel is a powerful and versatile tool used in business, finance, and data analysis. It can be used to quickly and easily manage, analyze, and visualize data. One of Excel’s most powerful features is AVERAGEIFS with OR Condition, which allows users to quickly and accurately calculate an average based on multiple criteria. This function is extremely useful for tracking and analyzing data over time and for comparing different sets of data. In this article, we will discuss how to use AVERAGEIFS with OR Condition in Excel and provide examples of how it can be used.

By default, the AVERAGEIFS function in Excel uses AND logic to calculate the average value of cells that meet multiple conditions.

If you instead would like to use the AVERAGEIFS function with OR logic to calculate the average value of cells that meet at least one of multiple conditions, you can use the following syntax:

=AVERAGE(IF((A2:A11="Guard")+(A2:A11="Forward"),B2:B11))

This particular formula finds the average of the values in the ranges B2:B11 where the corresponding value in the range A2:A11 is equal to Guard or Forward.

The following example shows how to use this formula in practice.

Example: How to Use AVERAGEIFS with OR Condition in Excel

Suppose we have the following dataset in Excel that shows the position and points scored by various basketball players:

Now suppose we would like to calculate the average value in the Points column for all players who have a value of either “Guard” or “Forward” in the Position column.

We can use the following formula to do so: 

=AVERAGE(IF((A2:A11="Guard")+(A2:A11="Forward"),B2:B11))

The following screenshot shows how to use this formula in practice:

Excel AVERAGEIFS with OR condition

We can see that the average points scored by players with a position of Guard or Forward is 20.2857.

We can verify this is correct by manually calculating the average of all points values for players who are Guards or Forwards:

Average Points: (22 + 14 + 17 + 35 + 11 + 19 + 24) / 7 = 20.2857

This matches the value that we calculated using our formula.

x

Related terms:

  1. VBA: Write AVERAGEIF and AVERAGEIFS Functions ## rewrite this title as a student question
  2. How to use AVERAGEIFS in Google Sheets (3 Examples)
  3. What is the Excel IF function with a greater than or equal to condition?
  4. How to use Pandas to replace values in column based on condition
  5. How to Select Rows by Condition in R (With Examples)
  6. How do I Sum Columns Based on a Condition in R
  7. How to Fix in R: the condition has length > 1 and only the first element will be used
  8. What is the 10% Condition in Statistics: Definition & Example
  9. Spark: How to Sum Column Based on a Condition
  10. How do I update column values based on condition in PySpark?