Generate Formula

[ruthls]

Formula for calculating value of each selling coin of 5 shirts which average selling is 17coin the values have to be between 1 to 3
I tried this =Randarray(5,1,1,3)/sum(randarray(5,1,1,3)*17)

 

[vletm]

ruthls​

You choose Excel Challenges for Your posting - it is okay, if ... as written ...
Post an Excel related Challenge for others to Solve (You must have a solution) Don't post questions here!
Do Your try work?
... or is this basic question?

 

[kemeron Yeng]

Formula for calculating value of each selling coin of 5 shirts which average selling is 17coin the values have to be between 1 to 3
I tried this =Randarray(5,1,1,3)/sum(randarray(5,1,1,3)*17)

If you want 5 shirt values:

each value based on a random number between 1 and 3
and the total equals 17 coins

then your formula is very close. The issue is that you generated two different RANDARRAYs, so the normalization breaks.

Use this instead in Excel:

=LET(x,RANDARRAY(5,1,1,3,TRUE), x/SUM(x)*17)

What it does:

Generates 5 random integers between 1 and 3
Stores them in x
Divides each by the total sum
Scales them so all 5 values add up to 17

Example output:

2.83
4.25
2.83
4.25
2.83

Total = 17

If you want:

whole numbers only,
exactly 2 decimal places,
or values constrained differently,

I can adjust the formula.

 

[vletm]

ruthls​

... have been here Jan 14, 2025.
This thread is closed now.