Randomized Array and sum of each row has to equal 0.83

Copper Contributor

I need to make a randomized array with 75 rows and four columns, and the randomized values should be decimals. I am able to achieve that with the formula RANDARRAY(75, 4, 0, 0.83). The issue is I need the sum of each row of randomized values to equal 0.83 as well. I have no idea how to incorporate the part where the sum of each row has to equal 0.83.

2 Replies

@BB1006 

I'd use helper columns (which you can hide if desired).

Let's say you want the data in B2:E76.

In I2:L76, enter the formula =RAND().

In B2, enter the formula =I2/SUM($I2:$L2)*0.83

Fill to the right to E2, then down to row 76.

You can use other columns as helper columns, of course.

S1070.png

alternatively
=LET(a,RANDARRAY(75,4,0,1),b,MMULT(a,SEQUENCE(4,1,1,0)),0.83*a/b)