Is there a way to construct a formula that specifies the number of RANDBETWEEN functions based upon a number in an adjacent cell? The spreadsheet has more than 300 rows, making it a laborious task to do manually,
Here’s an example of what I am trying to achieve:
| COL A | COL B |
|---|---|
| 1 | =RANDBETWEEN(1,8) |
| 2 | =RANDBETWEEN(1,8)+RANDBETWEEN(1,8) |
| 3 | =RANDBETWEEN(1,8)+RANDBETWEEN(1,8)+RANDBETWEEN(1,8) |
Thanks for any assistance!
Effectively, row 3 is just RANDBETWEEN(3 x Lower parameter; 3 x Upper parameter) so =RANDBETWEEN(A1*1;A1*8) should give the same effective results.
Nope - your suggested formula is one iteration of RANDBETWEEN(1,8), perhaps you meant RANDBETWEEN(A1*1,A3*8) but that still doesn’t work exactly the same.
The original formula RANDBETWEEN(1,8)+RANDBETWEEN(1,8)+RANDBETWEEN(1,8) would result in a possible output of between 3-24
Your suggested formula (corrected) RANDBETWEEN(A1*1,A3*8) would result in a possible output of between 1-24
If it is at Row 3, then the formula would read =RANDBETWEEN(A3*1;A3*8), i.e. between 3 and 24
In B2 enter =RANDBETWEEN(1,$A2*8) if you want the final number to be between 1 and 24 (for row 3).
or
If you want your final number in B3 to be between 3-24 which is what your formulas do. In B2 enter: =RANDBETWEEN($A2,$A2*8)
If you after something normal distributed ?
put for Example into A1 and B1:
=RANDBETWEEN(1;8) | =SUM($A$1:$A1)
fill down until you reach row 300
if you need free choice about how many RANDBETWEENS ?, dependent on eg D1:
=SUM($A$1 : INDEX($A$1:$A$300; D1 )