0

I am trying to select a random cell from an array AND then fill down, fixing the column but letting the row remain relative. For example, I am trying to select the contents of a random cell from A1:E500 for use in a formula in F500 (I can do that). Then I wish for F501, F502, F503, etc. to retrieve the value from the cell in the succeeding rows (but same columns) as the randomly selected cell. Is there a way to do this in a single formula, without having to create additional worksheet columns? I ask because I will be repeating this operation across many arrays. I guess what I am asking is whether there is a way to select a random cell from an array but encode it in a formula in such a way that when I fill down, the randomly selected cell in the first case serves as the anchor when I perform a fill operation. Thanks in advance for any assistance or guidance!

PeteU
  • 3

1 Answers1

0

Assuming you have Excel 365 / 2021 you can use the LET function with spilling to return your required length of data from the array, say 3 rows, all at once:

=LET(R, RANDBETWEEN(1,ROWS(data)-2), C, RANDBETWEEN(1,COLUMNS(data)),INDEX(data,R,C):INDEX(data,R+2,C))

Where data is the defined named range for e.g. A1:E500. The +-2 offset is to ensure that you don't pick a row in the last row, and then have no data to return below it - INDEX would return an error for trying to index beyond the range.

If you don't have LET, or need to be able to copy and paste the formula with relative references, you can dedicate two separate cells for picking the random index R and C, say in F500 and F501, then in G500 put your first formula:

=INDEX(data,$F$500-ROW($G$500)+ROW(),$F$501-COLUMN($G$500)+COLUMN())

You could also use OFFSET or INDIRECT, but both those formulae are volatile functions that will recalculate all the time, slowing down your workbook.

A thing of beauty

That being said, if you don't mind the CPU overhead of using OFFSET, you can combine relative named ranges and OFFSET to create a true random cell that acts just like a relative reference would.

  1. Click Formulas ribbon > Define name
  2. Define data as =$A$1:$E$500
  3. Set cell F1 as =RANDBETWEEN(1,ROWS(data))-1 and name the cell RR
  4. Set cell G1 as =RANDBETWEEN(1,COLUMNS(data))-1 and name the cell CC
  5. Place cursor in F500 (super important for step 6)
  6. Define randomcell as =OFFSET(A1,RR,CC) ...not $A$1
  7. Now type =randomcell in F500
  8. Fill down or right, it will update just as a relative reference A1 should, even when used in formulae
  9. If you want to lock the random reference to say a column e.g. $A1 style, define randomcellC as =OFFSET($A1,RR,CC) with the cursor in F500
ExcelEverything
  • 3,194