How to Calculate the Average of Visible Excel Cells Within Two Standard Deviations
Question details
The user needs to calculate the average of a dataset by only including visible (unfiltered) rows that fall strictly within two standard deviations of the mean.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing a filtered dataset where extreme outliers (values beyond two standard deviations from the mean) must be excluded from the calculated average.
- Observed behavior
- Standard functions like AVERAGEIFS do not automatically ignore hidden or filtered rows, requiring a more advanced array formula combination.
Ensure you have already calculated the mean and standard deviation of your dataset in separate reference cells, and verify that your target data range contains properly formatted numerical values.
Use an Array Formula with SUBTOTAL and OFFSET
Combine AVERAGE, IF, SUBTOTAL, and OFFSET to evaluate only visible rows while applying the standard deviation upper and lower bounds.
The standard AVERAGEIFS function cannot easily ignore hidden rows. By using SUBTOTAL with function number 9 (SUM), you can evaluate the visibility of each row dynamically.
In this formula structure, OFFSET processes the range row by row. We then apply conditional logic to ensure the value is both greater than the mean minus two standard deviations, and less than the mean plus two standard deviations.
Calculate the mean of your data in a specific cell (e.g., DI3) and the standard deviation in another cell (e.g., DI4).
Select the cell where you want the result to appear. Type the following formula: =AVERAGE(IF(SUBTOTAL(9,OFFSET(DI9,ROW(DI10:DI1420)-ROW(DI9),0,1))=DI10:DI1420,IF((DI10:DI1420>DI3-2*DI4)*(DI10:DI1420<DI3+2*DI4),DI10:DI1420)))
Modify DI9 to point to your column header, and DI10:DI1420 to point to your actual data range. Make sure the mean (DI3) and standard deviation (DI4) references match your worksheet.
If you are using Microsoft 365 or Excel 2021, simply press Enter. For older versions of Excel, you must press Ctrl+Shift+Enter simultaneously to confirm it as an array formula.
Use WPS Spreadsheet for Advanced Array Formulas
WPS Spreadsheet fully supports complex array logic, SUBTOTAL combinations, and dynamic filtering. You can seamlessly calculate statistical averages with multiple criteria without worrying about syntax or compatibility errors.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the target dataset.
- 2. Filter your dataset: Click the 'Data' tab on the top ribbon and select 'Filter' to hide the rows you do not want to include in your analysis.
- 3. Apply the statistical formula: Paste the provided array formula into a blank cell and press Enter (or Ctrl+Shift+Enter) to instantly output the average of the visible cells within the specific standard deviation boundaries.
Frequently Asked Questions
Why doesn't the AVERAGEIFS function ignore hidden rows automatically?
The AVERAGEIFS function evaluates all cells within the specified range, regardless of their visibility status. To specifically target only visible cells, you must use functions designed to detect visibility, such as SUBTOTAL or AGGREGATE.
What does the '9' in the SUBTOTAL function do?
The '9' tells the SUBTOTAL function to calculate the SUM of a range while ignoring rows that have been hidden by a filter. If you want to ignore rows that are manually hidden as well as filtered, use '109' instead.
How do I calculate the standard deviation for the formula criteria?
You can use the STDEV.P or STDEV.S functions depending on whether your data represents an entire population or just a sample. Place this formula in a separate reference cell so your main array formula can easily point to it.
Does this array formula work if my data contains blank cells or text strings?
Blanks or text strings can cause #VALUE! errors in array logic when Excel attempts to perform mathematical operations (like checking if a string is greater than the standard deviation). Ensure your targeted data range only contains numeric values, or wrap your cell evaluations in an IFERROR function.
