How to Calculate an Average from Noncontiguous Excel Cells Ignoring Zeros
Question details
The user needs to calculate the cumulative average of semester grades located in noncontiguous cells, ensuring that cells containing zeros (indicating incomplete semesters) are completely ignored in the calculation.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Averaging isolated summary cells across a complex grading spreadsheet without factoring in zeros from ongoing courses that lower the true average.
- Observed behavior
- Standard AVERAGE and AVERAGEIF functions require a contiguous range of cells and cannot be directly applied to scattered, non-adjacent cells without returning an error or skewing the result.
Identify the exact cell references containing your semester averages and ensure that the zero values truly represent incomplete grades rather than actual zero scores.
Use a Custom Division Formula for Scattered Cells
Calculate the average manually by adding the target cells together and dividing by the boolean count of cells that have a value greater than zero.
Since standard functions like AVERAGEIF do not support noncontiguous cell references, you can construct a mathematical formula that adds your specific cells and divides that sum by the number of cells that contain a non-zero value.
Click on the cell where you want the final cumulative average to be displayed.
Type the formula that sums your specific cells and divides by the logical count. For example: =(H54+H91+H128+H150)/((H54>0)+(H91>0)+(H128>0)+(H150>0))
Press Enter. The first half adds all values, while the second half evaluates each cell >0 to TRUE (which acts as 1) or FALSE (which acts as 0), dividing the total only by the completed semesters.
Create a Contiguous Summary Range for AVERAGEIF
Reorganize the isolated data points into a single continuous column or row, allowing you to use the standard AVERAGEIF function effortlessly.
Use a Helper Column with AVERAGEIFS
Add a hidden column with unique identifiers so you can conditionally average specific rows using AVERAGEIFS.
Easily Manage Complex Formulas with WPS Spreadsheets
WPS Spreadsheets provides comprehensive support for logical functions, array formulas, and standard statistical calculations. It perfectly handles noncontiguous ranges and complex division, making grading sheets and financial reports effortless to manage.
- 1. Open your workbook: Launch WPS Spreadsheets and open your grading or financial workbook.
- 2. Select the target cell: Click on the destination cell where you want the final average displayed.
- 3. Input the custom formula: Type the formula to combine your scattered cells, such as =(H54+H91+H128+H150)/((H54>0)+(H91>0)+(H128>0)+(H150>0)).
- 4. Calculate and format: Press Enter to execute the formula, then use the Number format menu on the Home tab to adjust the decimal places as needed.
Frequently Asked Questions
Why can't I just use AVERAGEIF with noncontiguous cells separated by commas?
The AVERAGEIF and AVERAGEIFS functions strictly require a continuous, contiguous range of cells (like A1:A10) for their range argument. They do not support multiple, disconnected cell references grouped by commas.
Will the custom Boolean formula ignore blank cells as well as zeros?
Yes. In the logical test (H54>0), an empty cell evaluates as 0, meaning it will return FALSE and will not be counted in the denominator. If your cells contain negative numbers that should be included, you would need to adjust the logic to <>0 instead.
Is there a way to visually highlight which cells are currently being included in the average?
You can use Conditional Formatting. Select your isolated cells, create a new formatting rule using the formula =H54>0, and set a specific fill color. This visually confirms which cells meet the criteria and are factored into your cumulative average.
