RandArray

[Sgeffert22]

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? 

[#2]

@Sgeffert22 

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.

[#3]

@HansVogelaar 

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

[#4]

@Sgeffert22 

What exactly do you want to do?

[#5]

@HansVogelaar

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

[#6]

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

[#7]

@Sgeffert22 

That would be

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

[#8]

@payablesmedwayllccom 

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

[#9]

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

[#10]

@Sgeffert22 

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.

[#11]

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