Why does RANDBETWEEN return a different number every time I look at it?

RANDBETWEEN is volatile—it recalculates every time the spreadsheet updates or you press F9, generating a new random number. This is intentional for simulations and testing. To freeze a value, copy the cell and paste as values only.

How do I use RANDBETWEEN to randomly select a customer name or value from a list?

Pair it with INDEX: =INDEX(names_range, RANDBETWEEN(1, ROWS(names_range))). This picks a random row number, then returns the customer name or value at that position in your subscription table.

Can I use RANDBETWEEN to generate random decimal numbers or percentages?

No. RANDBETWEEN only returns whole integers. For decimals, use =RAND()*(upper-lower)+lower. For percentages, use =RANDBETWEEN(5,20)/100 to convert to decimal format, or keep integers if your currency allows.

What's the difference between RANDBETWEEN and RAND()?

RANDBETWEEN returns random integers in a range you specify (e.g., 1–100); RAND() returns a random decimal between 0–1. Use RANDBETWEEN for discrete values like customer IDs or month numbers, and RAND() for continuous scaling like discounts or probabilities.

RANDBETWEEN function

Returns a random integer between two values, ideal for test data generation, simulations, random sampling, and variability testing scenarios.

=RANDBETWEEN(bottom, top)

Generate a RANDBETWEEN formula

Describe what you need. The generator will reach for RANDBETWEEN where RANDBETWEEN is the right tool, and tell you when it is not.

Arguments

How RANDBETWEEN reads its arguments

Argument	Required	Description
bottom	Required	The smallest integer the function can return; if this value exceeds top, a #NUM! error occurs.
top	Required	The largest integer the function can return; both bottom and top must be whole numbers with no decimals.

Returns

A random whole number (integer) between the bottom and top values, inclusive.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Select a random customer for A/B testing

Customer	Plan	MRR	Signup Date	Churn Date
Alice	Pro	99	2024-01-15
Bob	Basic	29	2024-02-20	2025-09-01
Carol	Enterprise	499	2023-06-10
David	Pro	99	2024-03-05
Eve	Basic	29	2024-01-22	2025-06-15

=RANDBETWEEN(1,5)

Result: 3

RANDBETWEEN returns a random integer from 1 to 5. The result of 3 means Carol's row was randomly selected for the test. Each time the sheet recalculates, a different row number (1–5) may be chosen for fair cohort assignment.

2. Simulate random churn timing for retention forecasting

Customer	Plan	MRR	Signup Date	Churn Date
Alice	Pro	99	2024-01-15
Bob	Basic	29	2024-02-20	2025-09-01
Carol	Enterprise	499	2023-06-10
David	Pro	99	2024-03-05
Eve	Basic	29	2024-01-22	2025-06-15

=RANDBETWEEN(1,36)

Result: 14

For retention modeling, this simulates which month (1–36 after signup) a customer might churn. A result of 14 means the forecast assumes churn in month 14. Apply this across all subscribers to model expected churn distribution and calculate survival cohort curves.

3. Generate random discount percentage for pricing experiment

Customer	Plan	MRR	Signup Date	Churn Date
Alice	Pro	99	2024-01-15
Bob	Basic	29	2024-02-20	2025-09-01
Carol	Enterprise	499	2023-06-10
David	Pro	99	2024-03-05
Eve	Basic	29	2024-01-22	2025-06-15

=RANDBETWEEN(5,20)

Result: 12

Tests how different discount levels (5–20%) affect conversion or retention. A result of 12 assigns a 12% discount to this customer segment. Random assignment eliminates bias and ensures each discount tier is tested across comparable customer groups.

Common errors

Which RANDBETWEEN error are you seeing?

Error	Why it happens	How to fix it
#NUM!	The bottom argument is greater than the top argument. RANDBETWEEN requires that bottom ≤ top to generate a valid range.	Swap the arguments: use =RANDBETWEEN(5,20) instead of =RANDBETWEEN(20,5).
#NUM!	One or both arguments contain decimal values. RANDBETWEEN only accepts whole numbers and cannot interpolate between fractional values.	Use INT() to round first: =RANDBETWEEN(INT(1.5), INT(10.9)) or replace decimals with integers such as =RANDBETWEEN(2,11).
#VALUE!	One or both arguments are text strings instead of numbers, such as =RANDBETWEEN("low","high") or cell references containing text.	Replace text with numeric values: =RANDBETWEEN(1,100) or use VALUE() if text contains a number: =RANDBETWEEN(VALUE(A1), VALUE(B1)).

Tips and when to use something else

RANDBETWEEN recalculates every time the sheet updates. To lock a result, copy the cell, then Paste Special > Values to replace the formula with its current value.
Combine RANDBETWEEN with INDEX to pick random rows: =INDEX(customer_list, RANDBETWEEN(1, ROWS(customer_list))) selects a random customer from the table.
For random decimals instead of integers, use =RAND()*(top-bottom)+bottom, which gives you continuous values instead of discrete whole numbers.
If you need predictable random behavior or non-uniform distribution (e.g., weighted toward higher discount tiers), consider RAND() with conditional logic or SUMPRODUCT instead.

Related functions

INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.RANDRAND returns a random decimal number between 0 and 1, useful for generating random samples, simulations, and unpredictable variations in forecasts.SUMPRODUCTSUMPRODUCT multiplies arrays element-by-element and sums the results, useful for weighted totals and multi-criteria counting.

Frequently asked questions

Why does RANDBETWEEN return a different number every time I look at it?: RANDBETWEEN is volatile—it recalculates every time the spreadsheet updates or you press F9, generating a new random number. This is intentional for simulations and testing. To freeze a value, copy the cell and paste as values only.
How do I use RANDBETWEEN to randomly select a customer name or value from a list?: Pair it with INDEX: =INDEX(names_range, RANDBETWEEN(1, ROWS(names_range))). This picks a random row number, then returns the customer name or value at that position in your subscription table.
Can I use RANDBETWEEN to generate random decimal numbers or percentages?: No. RANDBETWEEN only returns whole integers. For decimals, use =RAND()*(upper-lower)+lower. For percentages, use =RANDBETWEEN(5,20)/100 to convert to decimal format, or keep integers if your currency allows.
What's the difference between RANDBETWEEN and RAND()?: RANDBETWEEN returns random integers in a range you specify (e.g., 1–100); RAND() returns a random decimal between 0–1. Use RANDBETWEEN for discrete values like customer IDs or month numbers, and RAND() for continuous scaling like discounts or probabilities.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17