How to Average the First 12 Row Values At Least 10 in Excel
Question details
The user needs to calculate the average of the first 12 values in each row that are greater than or equal to 10 using dynamic array formulas.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Performing conditional data analysis across multiple rows where only specific qualifying values should be included in the calculation.
- Observed behavior
- To successfully extract up to 12 qualifying values per row and return their average seamlessly without relying on manual cell selection.
Ensure you are using a modern version of Excel (such as Excel 365) that supports dynamic array functions including BYROW, LAMBDA, FILTER, and TAKE.
Use BYROW, FILTER, and TAKE with Error Handling
Combine these dynamic array formulas to filter values, extract the first 12 matches, calculate their average, and prevent errors if no values meet the criteria.
This method uses the LET function to define variables within the formula, making it cleaner and easier to read while handling potential errors smoothly.
Click on the empty cell in the row where you want the average result to be displayed.
Type the formula: =BYROW(scenarios,LAMBDA(row,LET(values,FILTER(row,row>=10),IFERROR(AVERAGE(TAKE(values,,12)),""))))
Replace the word 'scenarios' in the formula with your actual data range, for example, A2:Z20.
Press Enter. The formula will calculate the average and automatically spill down to fill the results for the rest of the selected rows.

Use a Simplified Nested Formula
Apply a concise version of the dynamic array formula if your dataset is guaranteed to have qualifying values and explicit error handling is not required.
Calculate Dynamic Conditional Averages in WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced array functions and everyday formulas, allowing you to process large datasets and execute complex conditional calculations efficiently.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data.
- 2. Select an output column: Click on an empty cell adjacent to your first row of data.
- 3. Enter the array formula: Input your combination formula (using functions like FILTER or AVERAGE) to isolate and calculate your target numbers.
- 4. Apply across rows: Press Enter and drag the fill handle down to instantly apply the conditional average to all necessary rows.

Frequently Asked Questions
What happens if there are fewer than 12 values that meet the condition?
If a row has fewer than 12 values that are at least 10, the TAKE function will simply return all available qualifying values, and the AVERAGE function will correctly calculate the mean based on that smaller count without causing an error.
Why am I getting a #NAME? error when using BYROW or TAKE?
The #NAME? error typically occurs if your version of Excel does not support dynamic array formulas. BYROW, LAMBDA, and TAKE are only available in newer versions like Microsoft 365. You will need to upgrade or use an alternative software that supports these functions.
Can I dynamically reference a cell for the condition instead of hardcoding '10'?
Yes. Instead of typing 'row>=10', you can replace the number 10 with a cell reference, such as 'row>=Z1'. Make sure to use absolute references (like $Z$1) if the threshold should remain constant while applying the formula across different arrays.




