How to Use AVERAGEIF Across Multiple Ranges Ignoring Zero and N/A
Question details
The user needs to calculate the average of ratings located in non-contiguous cells or multiple ranges, specifically excluding any zero (0) values and 'N/A' text entries.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Calculating an accurate average rating from scattered data points while filtering out invalid or zero-value entries.
- Observed behavior
- Standard AVERAGEIF or AVERAGEIFS functions struggle with non-contiguous multiple ranges, requiring a workaround to combine ranges and filter out 0 and 'N/A'.
Ensure your spreadsheet software supports dynamic array functions like FILTER, TOCOL, and VSTACK (available in Microsoft 365 and newer spreadsheet versions) before using the advanced formula method.
Use FILTER and VSTACK to Average Multiple Ranges
Combine multiple ranges into a single array and filter out unwanted values before calculating the average.
Standard AVERAGEIF functions do not natively support multiple non-contiguous ranges. By using dynamic array functions like VSTACK (to combine ranges) and FILTER (to exclude 0 and N/A), you can easily calculate the correct average.
Use the VSTACK function to stack your multiple ranges vertically. For example: =VSTACK(A1:A5, C1:C5).
Wrap the stacked array in a FILTER function with conditions to exclude 0 and 'N/A'. Example: FILTER(VSTACK(A1:A5, C1:C5), (VSTACK(A1:A5, C1:C5)<>0) * (VSTACK(A1:A5, C1:C5)<>"N/A")).
Finally, wrap the entire FILTER formula inside the AVERAGE function: =AVERAGE(FILTER(VSTACK(A1:A5, C1:C5), (VSTACK(A1:A5, C1:C5)<>0) * (VSTACK(A1:A5, C1:C5)<>"N/A"))). Press Enter to get the result.
Use SUM and COUNT for Older Versions
If you are using an older version of Excel that lacks dynamic arrays, use a combination of SUM and COUNT functions.
Calculate Complex Averages Easily with WPS Spreadsheet
WPS Office provides a powerful, free Spreadsheet tool that supports advanced formulas, making it easy to calculate averages across multiple ranges while filtering out zeros and errors.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the data ranges.
- 2. Select the target cell: Click on the cell where you want the final average to appear.
- 3. Enter the formula: Type your combined AVERAGE, FILTER, and VSTACK formula, then press Enter to instantly calculate the result.
Frequently Asked Questions
Why does AVERAGEIF return a #VALUE! error with multiple ranges?
The AVERAGEIF and AVERAGEIFS functions are designed to work with a single contiguous range. If you input multiple non-contiguous ranges separated by commas, the function will return an error. You must combine the ranges first using VSTACK/TOCOL or use a SUM/COUNT workaround.
Does the AVERAGE function automatically ignore text like 'N/A'?
Yes, the standard AVERAGE function automatically ignores text strings and empty cells. However, it does include zero (0) values in its calculation. To ignore zeros as well, you must explicitly filter them out before averaging.
What is the TOCOL function used for in this scenario?
The TOCOL function transforms an array or range into a single column. It is useful for flattening a 2D range into a 1D list, making it much easier to filter out zeros and 'N/A' values before passing the data to the AVERAGE function.
