How to Find the First Percentage Reaching 50% and Return Following Values in WPS Spreadsheets
Question details
The user needs to automatically locate the first weekly percentage in a row that is at least 50% and return that value and all later values on another worksheet without manual references such as =BU14.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a row of weekly percentages and needs a dynamic way to start extracting values from the first week that reaches the 50% threshold.
- Observed behavior
- Manual references such as =BU14 return one fixed cell and do not adapt when the first qualifying percentage changes.
Make sure the weekly percentages are entered as real numbers, not text, and clear the cells to the right of the output cell if you plan to use a dynamic array formula that spills results.
Use a LET and DROP dynamic array formula
Best for WPS Spreadsheets versions that support dynamic arrays; one formula finds the first qualifying percentage and returns all following values automatically.
The LET function stores the row range and MATCH position, then DROP removes the cells before the first 50% value. This avoids hard-coded references and updates automatically when the source data changes.
Open the workbook in WPS Office, go to the other worksheet where you want the output, and click the first cell that should receive the result.
In the formula bar, type =LET(r,B14:ZZ14,p,MATCH(TRUE,r>=50%,0),DROP(r,,p-1)) and press Enter. Replace B14:ZZ14 with the actual range of your weekly percentages.
Confirm that the first value at or above 50% and all values to its right appear in the row. If you see #SPILL!, clear any cells that overlap the intended output range.
Use INDEX and MATCH with a helper position for older versions
Use when dynamic array functions are not available; find the starting position once, then copy INDEX formulas across the destination row.
Return the First 50% Weekly Percentage and Following Values in WPS Spreadsheets
Use a WPS Spreadsheets dynamic array formula to automatically find the first weekly percentage at or above 50% and return it together with all following values on another worksheet.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains the weekly percentage row, and switch to the destination worksheet.
- 2. Select the output start cell: Click the cell where you want the extracted row to begin and make sure there is enough space to the right for spilled results.
- 3. Enter the dynamic array formula: Type =LET(r,B14:ZZ14,p,MATCH(TRUE,r>=50%,0),DROP(r,,p-1)) in the formula bar, then press Enter.
- 4. Adjust the source range if needed: Replace B14:ZZ14 with your actual percentage range or worksheet reference, such as 'Weekly Data'!B14:ZZ14, if the data is on another sheet.
- 5. Review the returned values: Confirm that the first value at least 50% and all subsequent values appear in the output area.
Frequently Asked Questions
Can I use the formula when the weekly percentages are on a different worksheet?
Yes. Replace B14:ZZ14 with a sheet-qualified range such as 'Weekly Data'!B14:ZZ14 in the LET formula or in the INDEX/MATCH formulas, and WPS Spreadsheets will read the values from that sheet.
Why does the formula return a #SPILL! error in WPS Spreadsheets?
A #SPILL! error means the output range is not empty. Clear the cells to the right of the formula cell or move the formula to a location with enough blank columns for all returned values.
What happens if none of the percentages reaches 50%?
The MATCH function returns #N/A because no value satisfies the condition. Wrap the formula with IFERROR if you want a custom message instead.
Can I avoid dynamic array formulas if I use an older version of WPS Spreadsheets?
Yes. Use MATCH to find the first qualifying position in a helper cell, then use INDEX formulas copied across the row to return each following value manually.
