When working with spreadsheets, you may often come across situations where you need to calculate the average of a range of values. One useful function for this purpose is AVERAGEIF, which allows you to calculate the average of a range based on certain criteria. However, when using AVERAGEIF with two ranges, you might encounter issues when there are blank or zero values. In this article, we will learn how to ignore these blank or zero values in AVERAGEIF with two ranges.
First, let's understand the basic syntax of the AVERAGEIF function. The syntax is as follows:
=AVERAGEIF(range, criteria, [average_range])
The range parameter specifies the range of cells to be evaluated based on the given criteria. The criteria parameter defines the condition that must be met for a cell to be included in the average calculation. Finally, the optional average_range parameter indicates the range of cells to be averaged.
Now, let's consider a scenario where you have two ranges: one for the criteria and another for the values to be averaged. You want to calculate the average of the values that meet a specific condition in the criteria range. However, if there are blank or zero values in the average range, you want to exclude them from the calculation.
To achieve this, we can use the combination of AVERAGEIF and IF functions. Here's how:
- Start by selecting an empty cell where you want the average to be displayed.
- Enter the following formula:
=AVERAGEIF(range, criteria, IF(average_range<>0, average_range))
In this formula, range and criteria should be replaced with the actual ranges you are working with. The average_range parameter is wrapped inside the IF function. This checks if the value in the average_range is not equal to zero. If it is not zero, the value is included in the average calculation. If it is zero, the value is ignored.
Let's take an example to understand this better. Suppose you have a spreadsheet with two ranges: A1:A6 contains the criteria, and B1:B6 contains the values to be averaged. You want to calculate the average of the values that are greater than zero in range B1:B6. Here's how you can do it:
- Select an empty cell, let's say C1.
- Enter the following formula:
=AVERAGEIF(A1:A6, ">0", IF(B1:B6<>0, B1:B6))
After entering the formula, press Enter. The cell C1 will now display the average of the values that meet the specified condition and ignore any blank or zero values in range B1:B6.
It is important to note that this method works for ignoring both blank and zero values. If you only want to ignore one of them, you can modify the formula accordingly. For example, if you want to ignore only the zero values, you can use the following formula:
=AVERAGEIF(A1:A6, ">0", IF(B1:B6<>0, B1:B6))
Similarly, if you want to ignore only the blank values, you can use:
=AVERAGEIF(A1:A6, ">0", IF(B1:B6<>"", B1:B6))
By using the combination of AVERAGEIF and IF functions, you can easily ignore blank or zero values when calculating the average of a range based on certain criteria.
Conclusion
In this article, we learned how to ignore blank or zero values in AVERAGEIF with two ranges. By using the IF function in combination with AVERAGEIF, you can exclude these values from the average calculation. This can be particularly useful when working with spreadsheets that contain empty or zero values. Remember to adjust the formula based on your specific requirements, whether you want to ignore blank values, zero values, or both.
References
| Source | Link |
|---|---|
| Microsoft Support | https://support.microsoft.com/en-us/office/averageif-function-047bac88-d466-426c-a32b-8f33eb960cf6 |
| Google Sheets Help | https://support.google.com/docs/answer/3093583?hl=en |