Example: I want to pull random data between B10:B15 and C5:C68 and return the value in a single cell.
What I have is:
=INDEX(Sheet1.B10:B15,RANDBETWEEN(1,5))
and that gets me a random value from column B between rows 10 and 15, however, I can’t add column C and it’s values in there without getting an error.
Is there any way to do this?
You have your source data organized in not optimal way. If you had it in one column, the task would be trivial (as you already do). If it were a rectangular region, you could use ADDRESS with two RANDBETWEENs. But in your case, when you randomly decided to put everything into arbitrary pieces of spreadsheet, you only can treat them as one contiguous column with index arithmetic. My advise: reconsider your layout.
Direct solution of the your task - a formula like this
=CHOOSE(RANDBETWEEN(1;2);INDEX(B10:B15;RANDBETWEEN(1;5);0);INDEX(C5:C68;RANDBETWEEN(1;63);0))
In this case, a randomly determined from which column the value is taken and then the value is randomly chosen from this column.
Just a note: this will bias to values from B column, so not that random
=IF(RANDBETWEEN(1;ROWS(B10:B15)+ROWS(C5:C68))<=ROWS(B10:B15);INDEX(B10:B15;RANDBETWEEN(1;ROWS(B10:B15)));INDEX(C5:C68;RANDBETWEEN(1;ROWS(C5:C68))))
(A wondrful nonsense formula.)
=IF(RANDBETWEEN(1;ROWS(B10:B15)+ROWS(C5:C68))<=ROWS(B10:B15);INDEX(B10:B15;RANDBETWEEN(1;ROWS(B10:B15)));INDEX(C5:C68;RANDBETWEEN(1;ROWS(C5:C68))))
(A wondrful nonsense formula.)
Yes, you are right. Also it can be with another condition IF(Rand()<ROWS(B10:B15)/(ROWS(B10:B15)+ROWS(C5:C68)) =IF(RAND()<ROWS(B10:B15)/(ROWS(B10:B15)+ROWS(C5:C68));INDEX(B10:B15;RANDBETWEEN(1;ROWS(B10:B15));0);INDEX(C5:C68;RANDBETWEEN(1;ROWS(C5:C68));0)) It seems to me that this is a lottery of 6 prizes among 64 employees at the New Year’s party. Previously, a drum with paper coupons was rotated for this purpose.
Great but “volatile”: Will recalculate “on any event” if ‘AutoCalculate’ is enabled.
(I didn’t actually understand the lottery idea.)
On the lottery - I’m not sure. But each year at this time there are the same questions about random choice from the list. And usually this is due to the distribution of a limited number of prizes (short list) to one of the participants (long list).
Lmao, yeah, this is the answer I was looking for, and thank you for all your work! Believe it or not, this isn’t for a lottery or Christmas gift selection or anything, I have a standing bet with a friend as to how much of a book can be randomly written in a spreadsheet, and having to randomly select character names from 2 columns (male and female) was difficult, but for obvious reasons I couldn’t just merge the two columns together as the other person that answered said I should.
@JohnSUN: If you are ready to take fun serious you may have a look into a solution based on my variants of textjoin and textsplit which is also capable of joining more than two ranges of data for random selection. See column F of the attached example. I assumed numeric data for the example.