$30.00
$5.00
$15.00
$20.00
Recommend
2 
 Thumb up
 Hide
11 Posts

BoardGameGeek» Forums » Everything Else » Chit Chat

Subject: Hello with ANOTHER spreadsheet rss

Your Tags: Add tags
Popular Tags: [View All]
Terry Kirk
United Kingdom
Barnsley
South Yorkshire
flag msg tools
mbmbmbmbmb
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.
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Justin
United States
Springfield
Pennsylvania
flag msg tools
mbmbmbmbmb
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.
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Jon
United States
Edmond
Oklahoma
flag msg tools
admin
Convention Committee
badge
BGG.CON! BGG.CON SPRING! BGG@SEA!
mbmbmbmbmb
A1: =RANDBETWEEN(0,12)
A2: =RANDBETWEEN(0,12-A1)
A3: =RANDBETWEEN(0,12-SUM(A1:A2))
A4: =RANDBETWEEN(0,12-SUM(A1:A3))


(edit: missing ")"s)
5 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Terry Kirk
United Kingdom
Barnsley
South Yorkshire
flag msg tools
mbmbmbmbmb
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.
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Terry Kirk
United Kingdom
Barnsley
South Yorkshire
flag msg tools
mbmbmbmbmb
AnakinOU wrote:
A1: =RANDBETWEEN(0,12)
A2: =RANDBETWEEN(0,12-A1)
A3: =RANDBETWEEN(0,12-SUM(A1:A2))
A4: =RANDBETWEEN(0,12-SUM(A1:A3))


(edit: missing ")"s)

Thanks. Would this make larger numbers early on more probable?
3 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Justin
United States
Springfield
Pennsylvania
flag msg tools
mbmbmbmbmb
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.
3 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Byron S
United States
Ventura
California
flag msg tools
I don't remember what I ate last night
badge
but I can spout off obscure rules to all sorts of game like nobody's business!
mbmbmbmbmb
AnakinOU wrote:
A1: =RANDBETWEEN(0,12)
A2: =RANDBETWEEN(0,12-A1)
A3: =RANDBETWEEN(0,12-SUM(A1:A2))
A4: =RANDBETWEEN(0,12-SUM(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: =12-SUM(A1:A3)
4 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Terry Kirk
United Kingdom
Barnsley
South Yorkshire
flag msg tools
mbmbmbmbmb
runtsta wrote:
AnakinOU wrote:
A1: =RANDBETWEEN(0,12)
A2: =RANDBETWEEN(0,12-A1)
A3: =RANDBETWEEN(0,12-SUM(A1:A2))
A4: =RANDBETWEEN(0,12-SUM(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: =12-SUM(A1:A3)

That is a very good point.
Thinking about it, this solution will fit what i need.
Thank you.
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Walt
United States
Orange County
California
flag msg tools
In memorium. Bob Hoover died 25 Oct 2016 at 94. In WWII he was shot down in a Spitfire and stole an FW-190 to escape. He spent decades at air shows flying Ole Yeller, shown
badge
Please contact me about board gaming in Orange County.
mbmbmbmbmb
kirkatronics wrote:
AnakinOU wrote:
A1: =RANDBETWEEN(0,12)
A2: =RANDBETWEEN(0,12-A1)
A3: =RANDBETWEEN(0,12-SUM(A1:A2))
A4: =RANDBETWEEN(0,12-SUM(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.
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
David Jones
United States
Wilsonville
Oregon
flag msg tools
mbmbmbmbmb
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.
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Andrew Simpson
United Kingdom
Leeds
West Yorkshire
flag msg tools
mbmbmbmbmb
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 =A6-SUM(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: A7-A10 are the 4 numbers]
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Front Page | Welcome | Contact | Privacy Policy | Terms of Service | Advertise | Support BGG | Feeds RSS
Geekdo, BoardGameGeek, the Geekdo logo, and the BoardGameGeek logo are trademarks of BoardGameGeek, LLC.