How to Calculate Average in Excel

Using the AVERAGE Function

One of the simplest ways to calculate the average of a range of values in Excel is by using the AVERAGE function. This function takes one or more arguments, which can be individual numbers, cell references, or ranges. To use the AVERAGE function, follow these steps:

1. Select the cell where you want to display the average result.
2. Type the formula “=AVERAGE(range)”, where “range” is the range of cells you want to include in the calculation.
3. Press Enter to apply the formula.

For example, if you want to find the average of a range of values in cells A1 to A5, you can use the formula “=AVERAGE(A1:A5)” in a different cell, such as B1. Excel will calculate the average of the selected range and display the result in the cell where you entered the formula.

Note that the AVERAGE function only includes cells that contain numbers or numeric values, and it ignores empty cells, text, or logical values. If you want to include all cells in the calculation, including those with non-numeric values, you can use the AVERAGEA function instead.

Including and Excluding Cells in the Average Calculation

Excel provides several options for including or excluding specific cells or ranges of cells in the average calculation. One of the most common methods is by using the AVERAGEIF and AVERAGEIFS functions, which allow you to specify criteria that determine which cells to include in the calculation. Here are some other ways to include or exclude cells in the average calculation:

1. To include only cells that meet certain criteria, you can use the AVERAGEIF function. This function takes two arguments: the range of cells to evaluate, and the criteria to apply. For example, you can use the formula “=AVERAGEIF(A1:A5,”>=60″)” to find the average of all cells in the range A1:A5 that are greater than or equal to 60.

2. To exclude specific cells from the average calculation, you can use the AVERAGE function in combination with the IF function. The IF function allows you to specify a condition, and returns a value if the condition is true and a different value if the condition is false. For example, you can use the formula “=AVERAGE(IF(A1:A5<>50,A1:A5))” to find the average of all cells in the range A1:A5 except for those that contain the value 50.

3. To exclude cells with errors from the average calculation, you can use the AVERAGEIF function in combination with the ISERROR function. The ISERROR function returns “TRUE” if a cell contains an error value, and “FALSE” otherwise. For example, you can use the formula “=AVERAGEIF(A1:A5,”<>#REF!”,A1:A5)” to find the average of all cells in the range A1:A5 except for those that contain the #REF! error value.

Finding the Weighted Average

In some cases, you may need to find a weighted average instead of a regular average. A weighted average is calculated by multiplying each value in the range by its corresponding weight, and then dividing the sum of the weighted values by the sum of the weights. Here’s how you can find the weighted average in Excel:

1. Multiply each value in the range by its corresponding weight, and enter the results in a separate column or range of cells. For example, if you have a range of values in A1:A5 and their corresponding weights in B1:B5, you can enter the formula “=A1*B1” in C1, and drag the formula down to apply it to the rest of the cells in the column.

2. Find the sum of the weighted values by using the SUM function. For example, you can use the formula “=SUM(C1:C5)” to find the sum of the weighted values.

3. Find the sum of the weights by using the SUM function on the range of weights. For example, you can use the formula “=SUM(B1:B5)” to find the sum of the weights.

4. Divide the sum of the weighted values by the sum of the weights to find the weighted average. For example, you can use the formula “=SUM(C1:C5)/SUM(B1:B5)” to find the weighted average.

Note that the weights should be entered in the same units as the values, and the sum of the weights should be equal to the total number of values in the range.

Using the AVERAGEIF and AVERAGEIFS Functions

The AVERAGEIF and AVERAGEIFS functions are useful when you want to find the average of a range of cells that meet specific criteria. The AVERAGEIF function calculates the average of cells that meet a single criterion, while the AVERAGEIFS function calculates the average of cells that meet multiple criteria. Here’s how to use these functions:

1. AVERAGEIF Function: The AVERAGEIF function takes three arguments: the range of cells to evaluate, the criteria to apply, and the range of cells to average. For example, you can use the formula “=AVERAGEIF(A1:A5,”>=60″,B1:B5)” to find the average of all cells in the range B1:B5 that are greater than or equal to 60.

2. AVERAGEIFS Function: The AVERAGEIFS function takes multiple arguments: the range of cells to evaluate, the first criteria to apply, the range of cells to apply the first criteria to, the second criteria to apply (if any), and the range of cells to apply the second criteria to (if any). For example, you can use the formula “=AVERAGEIFS(B1:B5,A1:A5,”>=60″,A1:A5,”<=80")" to find the average of all cells in the range B1:B5 that are between 60 and 80, inclusive.

Note that the criteria can be specified using various comparison operators, such as “>”, “<", ">=”, “<=", "=", and "<>“. You can also use wildcards, such as “*” and “?”, to match text patterns. The criteria can be a value, a cell reference, or a formula that returns a value or reference.

Tips for Formatting Average Results in Excel

Excel provides several formatting options for displaying the average results in a cell. Here are some tips for formatting average results in Excel:

1. Decimal Places: By default, Excel displays the average result with two decimal places. You can adjust the number of decimal places by using the “Increase Decimal” and “Decrease Decimal” buttons on the Home tab of the ribbon. You can also use the “Number” format to specify the number of decimal places, or use the “General” format to display the result without any decimal places.

2. Currency Symbol: If the average result represents a monetary value, you can use the “Currency” format to add a currency symbol, such as “$” or “€”, to the result. You can also use the “Accounting” format to align the currency symbols and decimal places.

3. Percentage: If the average result represents a percentage, you can use the “Percentage” format to display the result as a percentage with a “%” symbol.

4. Conditional Formatting: You can use conditional formatting to highlight cells that meet certain criteria based on the average result. For example, you can use the “Color Scales” or “Data Bars” options to apply a color gradient or bar chart to the cells based on the relative values of the average result.

5. Custom Formats: You can create custom formats to display the average result in a specific format, such as a fraction, a date, or a time. To create a custom format, use the “Custom” format option and specify the format code using a combination of symbols and placeholders.