Terry Kirk
United Kingdom Barnsley South Yorkshire

Hi all. You are all really helpful to me in my last thread, and I really appreciated your time.
I now have another spreadsheet I need help with.
I need to output 4 random numbers which add up to 12. Including numbers 0 to 12.
For example 3 3 3 3 0 7 3 2 0 9 0 3 12 0 0 0
Just like last time i am not sure where to start.

Justin
United States Springfield Pennsylvania

kirkatronics wrote: Hi all. You are all really helpful to me in my last thread, and I really appreciated your time.
I now have another spreadsheet I need help with.
I need to output 4 random numbers which add up to 12. Including numbers 0 to 12.
For example 3 3 3 3 0 7 3 2 0 9 0 3 12 0 0 0
Just like last time i am not sure where to start.
Do you want to get an equal distribution of the possible results? Does ordering matter for your output? The answers to these questions seem required to get you to the best solution.

Jon
United States Edmond Oklahoma
Convention Committee
BGG.CON! BGG.CON SPRING! BGG@SEA!

A1: =RANDBETWEEN(0,12)
A2: =RANDBETWEEN(0,12A1) A3: =RANDBETWEEN(0,12SUM(A1:A2)) A4: =RANDBETWEEN(0,12SUM(A1:A3))
(edit: missing ")"s)

Terry Kirk
United Kingdom Barnsley South Yorkshire

WyantJM wrote: kirkatronics wrote: Hi all. You are all really helpful to me in my last thread, and I really appreciated your time.
I now have another spreadsheet I need help with.
I need to output 4 random numbers which add up to 12. Including numbers 0 to 12.
For example 3 3 3 3 0 7 3 2 0 9 0 3 12 0 0 0
Just like last time i am not sure where to start. Do you want to get an equal distribution of the possible results? Does ordering matter for your output? The answers to these questions seem required to get you to the best solution.
I want the numbers to be as random as possible, including the order, for testing a game.
Does that answer your question?
Thanks.

Terry Kirk
United Kingdom Barnsley South Yorkshire

AnakinOU wrote: A1: =RANDBETWEEN(0,12) A2: =RANDBETWEEN(0,12A1) A3: =RANDBETWEEN(0,12SUM(A1:A2)) A4: =RANDBETWEEN(0,12SUM(A1:A3))
(edit: missing ")"s) Thanks. Would this make larger numbers early on more probable?

Justin
United States Springfield Pennsylvania

kirkatronics wrote: WyantJM wrote: kirkatronics wrote: Hi all. You are all really helpful to me in my last thread, and I really appreciated your time.
I now have another spreadsheet I need help with.
I need to output 4 random numbers which add up to 12. Including numbers 0 to 12.
For example 3 3 3 3 0 7 3 2 0 9 0 3 12 0 0 0
Just like last time i am not sure where to start. Do you want to get an equal distribution of the possible results? Does ordering matter for your output? The answers to these questions seem required to get you to the best solution. I want the numbers to be as random as possible, including the order, for testing a game. Does that answer your question? Thanks.
No, not really. Are you saying you want 0, 0, 0, 12 to be equally likely as 0, 12, 0, 0 and 3, 3, 3, 3 and etc for every combination?
If so, then don't use the answer in the post above.

Byron S
United States Ventura California
I don't remember what I ate last night
but I can spout off obscure rules to all sorts of game like nobody's business!

AnakinOU wrote: A1: =RANDBETWEEN(0,12) A2: =RANDBETWEEN(0,12A1) A3: =RANDBETWEEN(0,12SUM(A1:A2)) A4: =RANDBETWEEN(0,12SUM(A1:A3))
(edit: missing ")"s) If they need to add up to 12 exactly, the last number can't be random, it should be just A4: =12SUM(A1:A3)

Terry Kirk
United Kingdom Barnsley South Yorkshire

runtsta wrote: AnakinOU wrote: A1: =RANDBETWEEN(0,12) A2: =RANDBETWEEN(0,12A1) A3: =RANDBETWEEN(0,12SUM(A1:A2)) A4: =RANDBETWEEN(0,12SUM(A1:A3))
(edit: missing ")"s) If they need to add up to 12 exactly, the last number can't be random, it should be just A4: =12SUM(A1:A3) That is a very good point. Thinking about it, this solution will fit what i need. Thank you.

Walt
United States Orange County California
In memorium. Bob Hoover died 25 Oct 2016 at 94. In WWII he was shot down in a Spitfire and stole an FW190 to escape. He spent decades at air shows flying Ole Yeller, shown
Please contact me about board gaming in Orange County.

kirkatronics wrote: AnakinOU wrote: A1: =RANDBETWEEN(0,12) A2: =RANDBETWEEN(0,12A1) A3: =RANDBETWEEN(0,12SUM(A1:A2)) A4: =RANDBETWEEN(0,12SUM(A1:A3))
(edit: missing ")"s) Thanks. Would this make larger numbers early on more probable? Yes. A 12 in the first position is 1/13 chance (13 because of the zero). In the last position, A1 through A3 must be zero, so 1 in 13^3, 2197.
Off hand, I would pick the numbers as above, then randomize the order. There are 4! (24) possible orders.
Another idea is to put twelve ones randomly in four columns, then add the columns, like: 3: 0 0 1 0 3: 0 0 1 0 2: 0 1 0 0 3: 0 0 1 0 2: 0 1 0 0 2: 0 1 0 0 4: 0 0 0 1 2: 0 1 0 0 2: 0 1 0 0 2: 0 1 0 0 2: 0 1 0 0 1: 1 0 0 0 ∑: 1 7 3 1
This I'm sure will give a correct result. This can be done more compactly in a spreadsheet. You can also roll 12 d4s.

David Jones
United States Wilsonville Oregon

Just to reinforce what Justin is saying, there is a commonly known problem that gets introduced in many 300 level probability classes where you have to randomly divide a line segment into three parts and then determine the odds that the resulting triangle you can make from these segments is acute or obtuse. The "trick" to the problem is that there are multiple ways of randomly trisecting a line segment and class members will usually produce different answers, all of which can be correct. The point of the exercise is to show that "random" is often a poorly defined term.
The question you are asking is directly analogous to the triangle problem above. You would need to be more specific about what you mean by random and/or what the purpose of generating the numbers will be. Depending on the need, its possible that generating the numbers you want will require some rigorous if/then statements and may require programming that is beyond Excel's capabilities. Anakin's solution is fine if this is for a trivial use or demonstrative exercise. If you need to use this for a repetitive simulation or some kind of scientific use, then you really need to get into specifics.

Andrew Simpson
United Kingdom Leeds West Yorkshire

Here's a quick way to do it.
A1 =RAND() A2 =RAND() A3 =RAND() A4 =RAND() A5 =SUM(A1:A4) A6 =12 (or the number you want to get to) A7 =ROUND(A1/A5*A6,0) A8 =ROUND(A2/A5*A6,0) A9 =ROUND(A3/A5*A6,0) A10 =A6SUM(A7:A9)
Basically it turns each of the 4 random numbers into a fraction of the total you are trying to get to. RAND() gives you a number between 0 and 1. [Edit: A7A10 are the 4 numbers]


