How to Return the Last Numeric Value in WPS Spreadsheets When Cells Contain IF Formulas
Question details
The user needs a formula that returns the last actual numeric value from a range when some formulas return empty strings, because COUNTA and INDEX can return a blank result.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a column where IF formulas may output empty strings and needs to extract the last numeric result for further analysis or reporting.
- Observed behavior
- INDEX combined with COUNTA may return a blank cell because COUNTA counts formula cells even when they evaluate to an empty string.
Confirm that your WPS Spreadsheets version supports the functions used: INDEX and MATCH work in most versions, while TAKE and FILTER require current dynamic array support.
Use INDEX and MATCH to Return the Last Numeric Value in a Range
Use this method in WPS Spreadsheets or Excel when you need broad compatibility and want to ignore IF formula cells that return empty strings.
MATCH with 99^99 searches for a very large numeric value and returns the position of the last numeric cell in the range, so formula cells that evaluate to empty text are ignored.
Click the cell where you want the last numeric value to appear, for example D2.
Type =INDEX(C6:C301,MATCH(99^99,C6:C301)) and press Enter. Replace C6:C301 with your actual data range.
Check that the cell displays the last numeric value from the range instead of a blank formula result.
Use TAKE and FILTER to Return the Last Nonblank Result in Supported Versions
Use this shorter dynamic array formula when WPS Spreadsheets or Excel 365 supports TAKE and FILTER and you prefer to filter out empty-string formula cells.
Return the Last Numeric Value in WPS Spreadsheets Without Blank IF Results
Use the INDEX and MATCH formula directly in WPS Spreadsheets to locate the last numeric value in a range and ignore cells where IF formulas return empty strings. WPS Office keeps your Excel formulas working while offering a free, lightweight spreadsheet environment.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook that contains the column with IF formulas.
- 2. Select the result cell: Click the cell where you want the last numeric value to appear.
- 3. Enter the formula: Type =INDEX(C6:C301,MATCH(99^99,C6:C301)) and press Enter, replacing C6:C301 with your actual data range.
- 4. Verify the result: Confirm that the cell shows the last numeric value from the range instead of a blank formula result.
Frequently Asked Questions
Why does INDEX and COUNTA return a blank when cells contain IF formulas?
COUNTA counts cells that contain formulas, even if the formula returns an empty string (""). INDEX then selects that counted cell, so the result can be blank. Use MATCH(99^99,C6:C301) to search for the last numeric value instead.
Can I use these formulas in WPS Spreadsheets and Excel?
Yes. The INDEX and MATCH formula works in WPS Spreadsheets and traditional Excel versions. The TAKE and FILTER method requires Excel 365 or a WPS Spreadsheets version that supports dynamic arrays.
How do I change the formula range from C6:C301 to my own data?
Select the cell containing the formula, click in the formula bar, and replace C6:C301 with your actual range. Press Enter to recalculate and return the last numeric value in the new range.
What should I do if the TAKE and FILTER formula returns a #NAME? error?
Your version may not support dynamic array functions. Use the INDEX and MATCH formula instead, or update WPS Office to a version that supports TAKE and FILTER.
Does the formula include zero values in the result?
Yes, MATCH(99^99,C6:C301) treats zero as a numeric value and can return it as the last numeric result. If you need to ignore zeros, use a separate conditional formula.
