How to Use Cell References and Export RANDBETWEEN Results in WPS Office Excel
Question details
The user needs to learn how to use relative and absolute cell references with RANDBETWEEN in WPS Office Spreadsheets and how to collect repeated macro results for counting or analysis.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with RANDBETWEEN formulas and wants to control how cell references behave when copied, and to store repeated macro-generated values for later counting or analysis.
- Observed behavior
- RANDBETWEEN results recalculate dynamically, so the user needs a reliable way to export or store repeated results before counting or analysis.
Open WPS Office Spreadsheets and select the workbook containing your RANDBETWEEN formulas. If you plan to run VBA macros, make sure the Developer tab is enabled and your macro security settings allow the project to run.
Use Relative and Absolute Cell References with RANDBETWEEN
Use this solution when you need to control whether the lower and upper limits adjust or stay fixed when RANDBETWEEN formulas are copied to other cells.
RANDBETWEEN accepts both relative and absolute references. Relative references change when copied, while absolute references keep the same lower and upper limits.
Open WPS Office Spreadsheets and click the worksheet cell where you want the RANDBETWEEN result to appear.
Type =RANDBETWEEN(A1,B1) and press Enter. When copied to other cells, the A1 and B1 references adjust relative to the new location.
Type =RANDBETWEEN($A$1,$B$1) and press Enter. The $A$1 and $B$1 references stay fixed when the formula is copied.
Collect Repeated Macro Results for Counting or Analysis
Use this solution when you run a macro many times and need to store each RANDBETWEEN result before counting or analyzing the output.
Use RANDBETWEEN Cell References and Export Macro Results in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets and use RANDBETWEEN with relative or absolute references to control random ranges. Then store repeated macro results in a worksheet range or VBA array so you can count and analyze them accurately.
- 1. Open your workbook: Launch WPS Office and open the workbook in WPS Office Spreadsheets.
- 2. Enter the RANDBETWEEN formula: Type =RANDBETWEEN(A1,B1) for relative references or =RANDBETWEEN($A$1,$B$1) for absolute references in the target cell.
- 3. Copy the formula: Press Enter and copy the formula to other cells as needed to generate the desired random results.
- 4. Create a macro to store results: Open the Developer tab and create or run a macro that writes each RANDBETWEEN result to a worksheet range or VBA array.
- 5. Count or analyze the results: Use =COUNT(range) on the stored values or loop through the VBA array to complete the analysis.
Frequently Asked Questions
Does RANDBETWEEN work with relative and absolute references in WPS Office Spreadsheets?
Yes. Use =RANDBETWEEN(A1,B1) for relative references that adjust when copied, or =RANDBETWEEN($A$1,$B$1) to keep the lower and upper limits fixed.
How do I collect 100 RANDBETWEEN macro results for counting?
In WPS Office Spreadsheets, write each result from the macro loop to a worksheet range or an in-memory VBA array. Then use =COUNT(range) or loop through the array to count the numeric results.
Why does RANDBETWEEN change each time the worksheet recalculates?
RANDBETWEEN is a volatile function and recalculates when the workbook calculates. To keep values stable, copy the results and paste them as values before counting or analysis.
Can I use RANDBETWEEN with absolute references in VBA code?
Yes. In VBA, you can use WorksheetFunction.RandBetween with values from fixed cells such as Range("$A$1").Value and Range("$B$1").Value, or refer to absolute cell addresses directly.
