How to Return a Random Non-Zero Value from an Excel Range
Question details
The user needs to extract a random non-zero value from a designated Excel range using a dynamic-array formula.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Selecting a random entry from a dataset while completely ignoring or excluding any cells that contain a zero value.
- Observed behavior
- The goal is to dynamically filter out zeros and return one random row's value from the remaining data, or return blank if all cells are zero.
Ensure you are using a version of Excel or WPS Spreadsheet that supports dynamic array functions such as FILTER, SORTBY, SEQUENCE, and RANDARRAY.
Use FILTER, SORTBY, and RANDARRAY Functions
Combine dynamic array formulas to filter out zero values and randomly select one of the remaining values.
This dynamic array formula nests several functions together. It isolates non-zero values, counts how many exist, generates a random sequence for sorting, and then indexes the first randomized result.
Click on the cell where you want the random non-zero value to be displayed.
Type the following formula: =IFERROR(INDEX(FILTER(B1:B15,B1:B15<>0),SORTBY(SEQUENCE(COUNTIF(B1:B15,"<>0")),RANDARRAY(COUNTIF(B1:B15,"<>0")))),"")
Press Enter. The formula will immediately filter out the zero values in range B1:B15 and display one of the remaining numbers at random.
Easily Manage Dynamic Array Formulas in WPS Spreadsheet
WPS Office fully supports advanced dynamic array functions like FILTER, SORTBY, and RANDARRAY, allowing you to solve complex data randomization and filtering tasks seamlessly.
- 1. Open your Dataset: Launch WPS Spreadsheet and open the workbook containing the data you want to filter.
- 2. Apply the Formula: Select an empty cell and paste the dynamic array formula utilizing FILTER and RANDARRAY.
- 3. Retrieve Results: Press Enter to instantly execute the calculation and view your random, non-zero result.
Frequently Asked Questions
Why does the formula return a blank cell?
The formula returns a blank cell if every single value within your specified range (e.g., B1:B15) is zero or empty. This is handled gracefully by the IFERROR function at the beginning of the formula.
Can I apply this formula to a horizontal data range?
Yes. If your data is arranged horizontally (for example, in range A1:Z1), you can replace 'B1:B15' with 'A1:Z1' in the formula. Make sure to update every instance of the range within the formula.
Are dynamic array formulas available in all spreadsheet versions?
No, dynamic array functions such as FILTER, SORTBY, and RANDARRAY are available in newer spreadsheet software like Microsoft 365, Excel 2021, and modern versions of WPS Office. Older software versions may return a #NAME? error.
