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.

@Hans Vogelaar 


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


What exactly do you want to do?

@Hans Vogelaar

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.

@Hans Vogelaar
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.

@Hans Vogelaar
Okay thank you for trying, I appreciate the effort.