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!
1 Answer
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.
- Click Formulas ribbon > Define name
- Define data as =$A$1:$E$500
- Set cell F1 as =RANDBETWEEN(1,ROWS(data))-1 and name the cell RR
- Set cell G1 as =RANDBETWEEN(1,COLUMNS(data))-1 and name the cell CC
- Place cursor in F500 (super important for step 6)
- Define randomcell as =OFFSET(A1,RR,CC) ...not $A$1
- Now type =randomcell in F500
- Fill down or right, it will update just as a relative reference A1 should, even when used in formulae
- 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
-
Thank you so much for this detailed explanation. It's very close to what I need. However, in viewing your answer I realize I failed to fully capture my challenge. The random cell selected in F500 would actually be several random cells (for purposes of illustration, let's use A105, B200, and C478). The contents of these cells would form the basis of a calculation (again, for illustration and simplicity, let's assume we are simply adding-- A105+B200+C478) within cell F500. Then in F501, the same calculation/function would be applied to the contents of cells A106, B201, and C479, and so forth.– PeteUFeb 9, 2022 at 18:39
-
Also, the dataset runs several thousand rows, and the function would cascade to the final row. In effect, as I think about it, the initial random selection of cells should represent a column/row count relative to the current cell (e.g. F500), if that makes sense. In the example in my comment, it's not A1:E500 as a fixed table that's the relevant array, it's the five columns to the left of F500 (A-E) and the 500 rows equal to or lesser than F500 (rows 1-500).– PeteUFeb 9, 2022 at 18:52
-
1I've just discovered/explored the OFFSET function and believe, in light of your helpful initial suggestions, I've solved the problem. Thank you!!– PeteUFeb 9, 2022 at 19:01
-
Well I'm glad I could push you in the right direction! My "thing of beauty" is unfortunately not very scalable to multiple random cells but the essence as you have discovered, still works. Another possible solution is based on INDIRECT. Imagine you can spare one cell each for each random cell. I.e. in F500
=ADDRESS(RANDBETWEEN(1,ROWS(data)), RANDBETWEEN(1,COLUMNS(data)))
and to access it as a relative ref in a formula=OFFSET(INDIRECT($F$500),ROW(A1)-1,0)
. Note again this is a volatile implementation. Feb 9, 2022 at 20:04 -
Oh and @PeteU do checkmark my answer so others may know it has been solved, thanks! Feb 9, 2022 at 20:09