CODEWITHSUNDEEP
arraysgoogle-sheetsrandomgoogle-sheets-formulaarray-formulas
snowboi
snowboi

Reputation: 101

Create list of random numbers between x and y using formula in Google Sheets

I'm trying to create a list of 50 random numbers let's say between 100 and 500 with one formula in Gsheets. Is there any formula like 'apply this to x cells'?

What I tried so far is (and doesn't work). I hoped randarray function will 'force' randbetween function to create 2D array (randarray creates a list of numbers between 0 and 1).

={
RANDARRAY(50,1), ARRAY_CONSTRAIN(RANDBETWEEN(100,500),50,1)
}

Error Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 50. Actual: 1.

So this error indicates that array_constrain didn't help either.

Upvotes: 1

Views: 2501

Answers (2)

user2242989
user2242989

Reputation: 11

In generic terms, if you need N random numbers, between X and Y, you would combine the following formulas:

RandBetween(X, Y)
Row(cell_ref)
Indirect(string_cell_ref)
ArrayFormula(array_formula)

Details

When combining a Row(cell_ref) with an ArrayFormula, you can specify a cell range or simply a number range:

ArrayFormula(Row(1:50))

The above example generates a one dimensional array (column) with the numbers 1 through 50. In order to programmatically change the number, we use the Indirect function to specify the upper bound of the range, N:

ArrayFormula(Row(Indirect("1:"&N)))

N can be a named range, hard coded, or a cell reference containing a number greater than 0. Because you want each row to contain a random number between X and Y, you need to eliminate the sequential number in each array position by multiplying the number generated by the above formula by zero:

ArrayFormula(Row(Indirect("1:"&N))*0)

which generates a on dimensional array (column) of N zeros. Now you can combine this as follows to generate a one dimensional array (column) of N random numbers between X and Y:

Solution

ArrayFormula(RandBetween(Row(Indirect("1:"&N))*0+X, Y))

You could use named ranges for N, X, and Y; hard code them eg. 50, 100, 500; or use simple cell references as in the example below:

ArrayFormula(RandBetween(row(indirect("1:"&B1))*0+B2, B3))

GSheet Example

Upvotes: 1

player0
player0

Reputation: 1

try like this:

=ARRAYFORMULA(RANDBETWEEN(ROW(A100:A149), 500))

0

Upvotes: 4

Related Questions