Copper Contributor

I'm trying to make the total of my RandArray set equal a pre-determined total. Is there a way that this can even be done? 

10 Replies


Let's say you want an array of 20 elements in a column with sum 500.


=LET(a, RANDARRAY(20), s, SUM(a), a/s*500)


Of course, you can use cell references instead of the fixed values 20 and 500.



Thank you so much! Would I be able to add a range to this set as well? 


What exactly do you want to do?


Create a random number generator with 125 values, a range of 1.1-2.1, where the sum of those numbers equal 245
Hi I am unable to find my worksheet data since yesterday I was working on that


That would be


=LET(a, RANDARRAY(125, , 1.1, 2.1), s, SUM(a), a/s*245)


This question has nothing to do with this discussion. Please start a new discussion and ask your question there.

Some of the values are over the max of 2.1, is there anyway to fix that?


Yeah, my idea was stupid. In the formula that I proposed, RANDARRAY generates numbers between 1.1 and 2.1 but then the multiplication to get the sum to 245 messes it up.

I can't think of a way to do what you want at the moment.

Okay thank you for trying, I appreciate the effort.