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 ....

[danilang · Accepted solution]

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.   

[danilang]

Hi @jeanilieski 

The macro itself seems to work fine.   What you need to do is call the macro from an external program.  If you run just the macro from designer, it will only run once and then stop.  If you add the macro to a main program it will loop until the criteria (8,7, 0-3, etc) are met.

Note: I did change the field names in the macro to ones more relevant to your situation and added in the ability to specify the rand ranges (0 to MaxRand) for each group.

Dan

[jeanilieski]

Thank you Dan! You were right about the macro, the main problem was that it was not iterating. Also thank you for refining it further.

[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 

2

7

1

5

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