AVERAGEIF Formula in Excel
The AVERAGEIF Formula is used to return the average (arithmetic mean) of all the cells in a range which you reference that meet given criteria.
=AVERAGEIF (range, criteria, average_range)
Below are the formula syntax for the averageif formula
Range (required) — The range of cells that contain the criteria which you want to analyze. This range can include numbers or names, arrays, or references that contain numbers.
Criteria (required) — The criteria are used to lookup in the range and determine what is to be averaged. It can be in the form of a number, expression, cell reference, or text.
Average_range (optional) — The actual set of cells to average.
Example Using the AVERAGEIF Formula:
The General Manager of a second-hand car dealership wants to know what is the average selling price of a car in the California branch for the current year.
The Financial Analyst generates from its Sales Journal the raw data of sales for the current year which contains car dealer branch location and sales figures.
The Financial Analyst can use the AVERAGEIF function to achieve this number.
*Note this table is only a sample of an example with 5 data entries while the rest of the data has been cut out for display purposes.
In our example, below is how the formula syntax was used:
- The range formula was used for referencing the branch location column.
- Criteria formula was used for qualifying a certain branch location (e.g. California)
- Average_range formula was used for calculating the average car sale for the company.
This could have also been applied to other conditions like averaging the Sedan sales or Convertible sales, depending on product codes, or products that met a certain $ amount specifications.
I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!
