Do the random values in RANDARRAY change every time I edit the sheet?

Yes. RANDARRAY recalculates whenever the workbook recalculates (usually on every change). To preserve results, copy the array and paste as values. In Google Sheets, copy the results into a new column with Paste Special > Values only.

Can I use RANDARRAY to generate a range of test budgets for campaign spending scenarios?

Yes. Use =RANDARRAY(3,2,1000,10000,TRUE) to create random budgets by channel and period. Each recalculation produces new scenarios, useful for stress-testing ROI forecasts or validating budget allocation logic.

What's the difference between RANDARRAY and RANDBETWEEN?

RANDBETWEEN generates a single random number in one cell; RANDARRAY fills an entire array of cells with random numbers at once. Use RANDBETWEEN for simple one-off values and RANDARRAY when you need bulk simulation across many rows or columns.

Why does RANDARRAY show #SPILL! when I use large row and column counts?

The resulting array is too large to fit in the blank cells adjacent to the formula. Move to an empty sheet section, reduce dimensions, or split into multiple smaller arrays. For instance, =RANDARRAY(100,100) may fail in a crowded area; try =RANDARRAY(50,50).

RANDARRAY function

RANDARRAY returns a spilled array of random numbers with optional dimensions, bounds, and whole-number control.

=RANDARRAY([rows], [columns], [min], [max], [whole_number])

Generate a RANDARRAY formula

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

Arguments

How RANDARRAY reads its arguments

Argument	Required	Description
rows	Optional	Optional; number of rows in the returned array. Defaults to 1 if omitted. Must be a positive integer.
columns	Optional	Optional; number of columns in the returned array. Defaults to 1 if omitted. Must be a positive integer.
min	Optional	Optional; lower bound for random values, inclusive. Defaults to 0. Must be numeric.
max	Optional	Optional; upper bound for random values, exclusive. Defaults to 1. Must be numeric and greater than min.
whole_number	Optional	Optional; if TRUE, returns integers only; if FALSE (default), returns decimals. Accepts boolean values.

Returns

An array of random numeric values, sized [rows] by [columns].

Availability

Excel: 365 / 2021+ · Google Sheets: Supported

Worked examples

1. Generate random test click counts for campaign performance baseline

Campaign	Channel	Spend	Clicks	Conversions
Email Blast Q4	Email	$5000	450	45
Social Ads	Instagram	$8000	1200	85
Webinar Series	Online Event	$3000	280	22

=RANDARRAY(3,1,200,1500,TRUE)

Result: [[687],[1234],[892]]

Creates a 3-row array of random integers between 200 and 1500 to simulate click counts for each campaign channel. This baseline can be compared against actual clicks (450, 1200, 280) to test forecasting accuracy. whole_number=TRUE ensures counts are integers, not fractional clicks.

2. Generate random conversion rate estimates by channel

Campaign	Channel	Spend	Clicks	Conversions
Email Blast Q4	Email	$5000	450	45
Social Ads	Instagram	$8000	1200	85
Webinar Series	Online Event	$3000	280	22

=RANDARRAY(3,1,0.8,9.5,FALSE)

Result: [[3.47],[6.23],[2.91]]

Generates 3 random decimal rates (0.8% to 9.5%) to forecast conversion rates. FALSE produces realistic percentage decimals. Multiplying these by historical clicks estimates expected conversions for each channel—Email at 3.47% of 450 clicks ≈ 16 conversions.

3. Create random budget allocation matrix across channels and quarters

Campaign	Channel	Spend	Clicks	Conversions
Email Blast Q4	Email	$5000	450	45
Social Ads	Instagram	$8000	1200	85
Webinar Series	Online Event	$3000	280	22

=RANDARRAY(3,2,1500,8000,TRUE)

Result: [[5342,6891],[3567,7120],[6234,4589]]

Produces a 3×2 matrix of random budgets (dollars) representing three channels across two planning quarters. Rows correspond to Email, Instagram, and Online Event; columns to Q1 and Q2 budgets. whole_number=TRUE ensures whole dollar amounts for actual spend modeling.

Common errors

Which RANDARRAY error are you seeing?

Error	Why it happens	How to fix it
#NUM!	The min argument is greater than or equal to max. RANDARRAY cannot generate a range when the lower bound exceeds the upper bound.	Swap the bounds: use =RANDARRAY(3,1,100,1500) instead of =RANDARRAY(3,1,1500,100).
#VALUE!	One of the numeric arguments (rows, columns, min, max) is non-numeric, such as text or a cell containing an error. whole_number must also be boolean.	Ensure all arguments are valid types: =RANDARRAY(3,1,100,1500,TRUE) not =RANDARRAY('three',1,100,1500,TRUE).
#SPILL!	The result array is too large to spill into available blank cells, or existing data blocks the spill range. A 500×500 array may exceed available grid space.	Reduce array dimensions or move the formula to an empty region. For example, use =RANDARRAY(50,50) in a fresh sheet rather than near existing data.

Tips and when to use something else

RANDARRAY recalculates on every sheet change; copy results and paste as values to lock them for reporting.
Use whole_number=TRUE for counts and budgets; use FALSE for rates and percentages.
Pair RANDARRAY with INDEX or XLOOKUP to assign random values back to campaign or channel rows for structured scenario modeling.
When RANDARRAY alone is insufficient, consider SEQUENCE() for predictable incremental numbers or RAND() for a single cell value.

Related functions

SEQUENCESEQUENCE generates arrays of sequential numbers based on row/column counts, starting value, and step increment.RANDRAND returns a random decimal number between 0 and 1, useful for generating random samples, simulations, and unpredictable variations in forecasts.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.TRANSPOSETRANSPOSE flips a row-or-column array, turning rows into columns and columns into rows, returning a spilled array of the same size swapped.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.

Frequently asked questions

Do the random values in RANDARRAY change every time I edit the sheet?: Yes. RANDARRAY recalculates whenever the workbook recalculates (usually on every change). To preserve results, copy the array and paste as values. In Google Sheets, copy the results into a new column with Paste Special > Values only.
Can I use RANDARRAY to generate a range of test budgets for campaign spending scenarios?: Yes. Use =RANDARRAY(3,2,1000,10000,TRUE) to create random budgets by channel and period. Each recalculation produces new scenarios, useful for stress-testing ROI forecasts or validating budget allocation logic.
What's the difference between RANDARRAY and RANDBETWEEN?: RANDBETWEEN generates a single random number in one cell; RANDARRAY fills an entire array of cells with random numbers at once. Use RANDBETWEEN for simple one-off values and RANDARRAY when you need bulk simulation across many rows or columns.
Why does RANDARRAY show #SPILL! when I use large row and column counts?: The resulting array is too large to fit in the blank cells adjacent to the formula. Move to an empty sheet section, reduce dimensions, or split into multiple smaller arrays. For instance, =RANDARRAY(100,100) may fail in a crowded area; try =RANDARRAY(50,50).

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