random numbers which should sum up to pre-given sum

jeanilieski

Hi Community,

I have a simple question. Let's assume I have few pairs of numbers:

8 and 7, (range 0-3)

32 and 14, (range 0-3)

38 and 14 (range 0-5)

and I want to produce random numbers that will sum up to the first number in the row, would that be possible?

Example for the first row: yield 7 numbers in range 0-3 that will sum up to 8 (e.g. 0,0,3,1,2,0,2)

Example for the third row: yield 14 numbers in range 0-5 that will sum up to 38. (e.g. 5,3,5,5,0,5,0,1,2,0,2,5,1,4)

I found one solution to this in the Community chat, but it uses macro which most of the time produces the desired outcome, but still sometimes does not. The formula should be able to absolutely always give randome numbers that will sum up to the first number, each time the workflow will be ran.

Thank you very much for the help, hints ....

Workflow

Output

Tips and Tricks

Preparation

Optimization

Accepted answers

danilang

Hi @jeanilieski

What was the problem with the macro? Did it...

A. Sometimes produce numbers that did not add up to the total?

B. Sometimes produce no numbers at all?

Edit: this is also an interesting definition of "random".

The standard definition of random is based on 2 criteria

(1) the values are uniformly distributed over a defined interval or set, and

(2) it is impossible to predict future values based on past or present ones.

Your criteria that the sum of the values equal a specific total means that you can predict the last number by summing up the preceding ones, thereby violating rule 2.

This also suggests the way to build a set.

1. Generate the first N-1 values

2a. If the sum of these is greater than your target, start over

2b. If the sum is less than or equal, then set the last value to Target-Sum(X1...Xn-1)

Dan

All comments

danilang

Hi @jeanilieski

What was the problem with the macro? Did it...

A. Sometimes produce numbers that did not add up to the total?

B. Sometimes produce no numbers at all?

Edit: this is also an interesting definition of "random".

The standard definition of random is based on 2 criteria

(1) the values are uniformly distributed over a defined interval or set, and

(2) it is impossible to predict future values based on past or present ones.

Your criteria that the sum of the values equal a specific total means that you can predict the last number by summing up the preceding ones, thereby violating rule 2.

This also suggests the way to build a set.

1. Generate the first N-1 values

2a. If the sum of these is greater than your target, start over

2b. If the sum is less than or equal, then set the last value to Target-Sum(X1...Xn-1)

Dan

jeanilieski

Hi Dan,

thank you for the promt answer. The Problem with the macro I am using falls under: A. Sometimes produce numbers that did not add up to the total? Please see the attached macro.

Actually this is the most important part of my questuion. Even if I do not insist on certian range, (e.g. random numbers betwen 0-3 in some case and 0-5 in another), is it possible to at least have the pre-given sum always achieved after single runing of the macro? This is related to scheduling the workflow, where once a day only after single runing of the workflow the random numbers will sum up to the desired value.

P.C. I understand the concept you suggest as alternative, but I think it is agian based on a iterative macro and several runings of the same, but as mentioned before, I need the result only after single runing of the workflow.

Thank you once again.

random sum.yxmc

Ridaely1995

Hello. I still have a question regarding the sum of numbers. 1 or more numbers should sum up to a value (please see the workflow attached). Could someone help me please?

Eg. Value to find = 10

Values in dataset

So, 2, 7 and 1 should be filtered out.

Random Sum of amounts.yxwz

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703