The AVERAGEIF function in Excel is used to calculate the average of a range of cells that meet a specified condition or criteria. Here’s a step-by-step guide on how to use the AVERAGEIF function:
Syntax
AVERAGEIF(range, criteria, [average_range])
- range: This is the range of cells that you want to apply the criteria to.
- criteria: This defines the condition that must be met. It can be a number, expression, cell reference, or text that defines which cells will be averaged.
- average_range (optional): These are the actual cells to be averaged. If omitted, Excel averages the cells in the ‘range’.
Steps to Use AVERAGEIF
- Select the Cell for Result:
- Click on the cell where you want the average result to appear.
- Enter the AVERAGEIF Formula:
- Start by typing `=AVERAGEIF(`.
- Specify the Range:
- Enter the range of cells you want to evaluate (e.g., `A1:A10`).
- Define the Criteria:
- Enter the criteria. For example, `”>=10″` will average cells in the range that are greater than or equal to 10. Use quotation marks for conditions or text (e.g., `”>5″`), but not for numbers (e.g., `5`).
- Specify the Average Range (if needed):
- If you want to average a different set of cells than those in the range, specify your average_range (e.g., `B1:B10`). If you want to average the cells in the original range, you can leave this argument out.
- Close the Function:
- Type `)` at the end of the function.
- Press Enter:
- Press the Enter key to get the result.
Example
To find the average of numbers in the range A1:A10 that are greater than 5, you could use:
=AVERAGEIF(A1:A10, ">5")
For averaging cells in B1:B10, where corresponding cells in A1:A10 are greater than 5:
=AVERAGEIF(A1:A10, ">5", B1:B10)
Tips
- Wildcards: Use `*` for any sequence of characters or `?` for a single character when working with text values.
- Logical operators: Use operators such as `>`, `<`, `>=`, `<=`, `=`, and `<>` to define the criteria.
By following these steps, you can efficiently use the AVERAGEIF function to calculate averages based on specific conditions.
Unlock Your Potential
Excel
Access
Power BI
