# RandArray

Copper Contributor

# RandArray

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

# Re: RandArray

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.

# Re: RandArray

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

# Re: RandArray

What exactly do you want to do?

# Re: RandArray

@HansVogelaar

Create a random number generator with 125 values, a range of 1.1-2.1, where the sum of those numbers equal 245

# Re: RandArray

Hi I am unable to find my worksheet data since yesterday I was working on that

# Re: RandArray

That would be

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

# Re: RandArray

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

# Re: RandArray

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

# Re: RandArray

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.

# Re: RandArray

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