Excel Formula for Consecutive Status Progress Percentages in WPS Spreadsheets
Question details
The user needs a WPS Spreadsheets formula that returns a progress percentage from the sequence 10%, 20%, 30%, 40%, 50%, 65%, 90%, and 100%, based on the number of consecutive completed status cells in columns D through K.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Tracking row-level progress when status cells in D:K are completed in order.
- Observed behavior
- The result cell should show the matching percentage from the sequence as each consecutive status cell is completed.
Confirm that your status cells are in a contiguous range such as D3:K3 and that completed cells are filled from left to right without blanks between them. This helps the COUNTA-based formula return the intended progress level.
Use CHOOSE with COUNTA to Map Consecutive Completed Status Cells to the Progress Sequence
Best for a compact formula when every populated status cell in D3:K3 is consecutive from left to right.
COUNTA counts the non-empty cells in D3:K3, and CHOOSE returns the corresponding value from the percentage sequence 10%, 20%, 30%, 40%, 50%, 65%, 90%, and 100%.
Click the cell where you want the progress percentage, for example L3.
Type =CHOOSE(COUNTA(D3:K3),10%,20%,30%,40%,50%,65%,90%,100%) and press Enter.
Drag the fill handle from the bottom-right corner of the result cell down through the remaining rows to apply the formula.
Calculate Consecutive Status Progress Percentages in WPS Spreadsheets
Use WPS Spreadsheets to build a live Excel-compatible progress formula that maps consecutive completed status cells to 10%, 20%, 30%, 40%, 50%, 65%, 90%, and 100%, then copy it down for every row.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open your Excel workbook, and go to the worksheet that contains the status cells.
- 2. Select the result cell: Click L3 or another empty cell where you want the progress percentage to appear.
- 3. Enter the progress formula: Type =CHOOSE(COUNTA(D3:K3),10%,20%,30%,40%,50%,65%,90%,100%) and press Enter.
- 4. Apply the formula to additional rows: Drag the fill handle from the bottom-right corner of L3 down through the rows you want to calculate.
- 5. Format the result as a percentage if needed: Select the result cells, right-click and choose Format Cells, then select Percentage and set the preferred decimal places.
Frequently Asked Questions
How do I calculate progress from consecutive status cells in WPS Spreadsheets?
Click the result cell and enter =CHOOSE(COUNTA(D3:K3),10%,20%,30%,40%,50%,65%,90%,100%). It returns the matching percentage based on how many cells in D3:K3 are non-empty.
What happens if I skip a status cell or leave a blank between completed cells?
COUNTA still counts every non-empty cell, so the returned percentage may not match your intended progress. This formula works best when completed status cells are consecutive from left to right.
How do I apply the same progress formula to multiple rows?
After entering the formula in the first row, drag the fill handle from the bottom-right corner of the result cell down through the remaining rows.
Can I use this formula in Microsoft Excel too?
Yes. CHOOSE and COUNTA work in both WPS Spreadsheets and Microsoft Excel, so the formula is portable between .xlsx workbooks.
How do I display the result as a percentage instead of a decimal?
Select the result cells, right-click and choose Format Cells, select Percentage, and set the desired number of decimal places.
