Robert Sell
United States
Stevens Point
Wisconsin
flag msg tools
Avatar
mbmbmb
I have an idea I'm working on and would like a little assistance getting it to work.

I need x columns in excel to be randomized (contain random numbers in a range), but then sum to y(a value set by me). I'm working through a prototype and was thinking this was a decent way to get a smattering of random values to test against each other, but start in a balanced state.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Craig C
United States
Wichita
Kansas
flag msg tools
Avatar
mbmbmbmbmb
The RAND and RANDBETWEEN functions will do that. The downside is, unless there's a way to turn this feature off, every time you hit the Enter key, the numbers change.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Harm van der Schans
Netherlands
Voorburg
flag msg tools
mbmbmbmbmb
Is x a fixed number? If so, this would be like this:

1. In every column you want filled, write the formula =randbetween(0,100)
2. In one cell, make a sum of all the numbers you made (of row 1 basically)
3. In another cell, put your value Y.
4. Below the cells you filled before in step 1, make a formula that dividides the numbers in step 1 by the sum (step 2) then multiplies by your value Y.

Keep in mind that every time you make a change to this excel, the numbers will change (even if you press 'delete' on an empty cell or something)

 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
/|\ Roland /|\
United States
Texas
flag msg tools
Avatar
mbmbmbmbmb
There has always been an option in Excel to turn off auto recalculation. I don't have it in front of me (stupid iPad), but if you search Excel help it should guide you to it.

It used to be under Tools>Options>Calculation I think, but it's been moved in new toolbars.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Craig C
United States
Wichita
Kansas
flag msg tools
Avatar
mbmbmbmbmb
ath3ist wrote:
There has always been an option in Excel to turn off auto recalculation. I don't have it in front of me (stupid iPad), but if you search Excel help it should guide you to it.

It used to be under Tools>Options>Calculation I think, but it's been moved in new toolbars.


I'll look for that, because I'd really like my numbers to quit changing.

Like most things in Excel, it's probably solved by clicking on one thing. The challenge is finding that one thing.

EDIT: changing the Calculations setting from Auto to Manual will prevent the random cells from recalculating, and it appears to still calculate basic functions (if you type +6*8 in a cell and hit Enter, it'll display "48"), but I don't know if the Manual setting prevents other calculations from happening. Something to experiment with, I guess.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Jeremy Lennert
United States
California
flag msg tools
designer
Avatar
mbmbmbmbmb
If you want to generate random numbers once and then never have them change a gain, you should copy the range, then go to Edit -> Paste Special and select "Paste Values". This like a regular copy/paste operation except that you just get the current values of the cells instead of the formulas that generated those values.

Though it might be prudent to paste into a new area (or new file), so that all the formulas for generating your random values are still around in case you ever want to generate a new random data set.


It is also possible to turn off automatic recalculation of formulas--I couldn't say off hand where to do it in the menus, but I know I've done it using macros. It can get really confusing if you turn calculations off and then forget what you did, though (and then can't figure out why your new formula won't work), so I don't recommend doing that if the above option works for you.


The standard way of generating X random numbers that sum to Y is to generate (X-1) random numbers and then set the last number to Y - (sum of other numbers), but if you do that there's no guarantee that the last number will fall in the same range as the other ones. Soulflame's method will guarantee that all the numbers are in a comparable range, but will probably give you fractions.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
peter jackson
United States
flag msg tools
designer
Avatar
mbmbmbmb
I usually just rely on random.org to supply lists of random numbers. You can use the random sequence generator to list a randomized list of all numbers between 1 & x, or you can use their list randomizer to randomly array a number of objects you can list in a text box. Unless re-generating random lists is going to be a central theme of your game, I'd just pull the randomized sets I need from there; =RAND() and =RANDBETWEEN() are useful, but limited.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Jay K
United Kingdom
West Malling
Kent
flag msg tools
designer
Nothing to see here. Please move on
badge
It's about time!
Avatar
mbmbmbmbmb
Write it in Visual Basic for Excel as you can create random numbers without the issues of the Rand formula. PM me and provided it is a small job I will happily do it for you.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Brian Fong
United States
Los Angeles
California
flag msg tools
Avatar
mbmbmbmbmb
There are ways to stop the autocalculate, but then, you're using Excel.

Spreadsheets are designed to automatically update. That's why it is used by accountants.

VB is a much better medium, unless you use a multisheet cross index. then it's just a pain.

 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Erik Dewey
United States
Broken Arrow
Oklahoma
flag msg tools
designer
publisher
badge
Avatar
mbmbmbmbmb
When you turn off Autocalculate, you can manually force recalculation by pressing F9 (or F10, I can't remember off the top of my head).
 
 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.