Randomly dividing and assigning numbers

**tapmonkey** · 11-29-2011, 03:51 PM

Hello All,

I am making a small video game and am using Excel to define the attributes of the items in the game. The attribute are numerical

What I am trying to do is this.

Lets say the item has 14 total numerical attributes.

And there are three categories for these 14 points to fall into.
ATK
INT
DEF

is there a way to set lets say ATK to recieve 50% of the 14 points and INT and DEF to recieve the other 50 but in a random assignment. Maybe 25% and 25% or 30% and 20%.

I want to be able to just plug in the original number the 14, or what have you. And then have excel fill in the rest for me, and generate the attributes of the items.

Thanks!

**tigeravatar** · 11-29-2011, 04:27 PM

tapmonkey,

Welcome to the forum!
Attached is an example workbook based on the criteria you provided. Is something like that what you're looking for?

**tapmonkey** · 11-29-2011, 04:34 PM

tigeravatar thanks so much for a reply.

This looks like exactly what I need, but when I downloaded it, my comp tells me formulas are too complicated for my excel to understand! hahah.

Can you maybe write em out? thanks!!!!

**tigeravatar** · 11-29-2011, 04:43 PM

I made a slight change to the attachment in my earlier post, try it again. I realized I had used RandBetween() which I don't think is in excel 2003 so I changed it to RoundUp(Rand()). Hopefully that will work for you. Otherwise you can try putting the formulas in manually.

In the attachment are the following formulas -

In cell C5:
```
Please Login or Register  to view this content.
```

In cell C6:
```
Please Login or Register  to view this content.
```

In cell C7:
```
Please Login or Register  to view this content.
```

**shg** · 11-29-2011, 04:55 PM

I had used RandBetween() which I don't think is in excel 2003

Part of the ATP in Excel 2003-

**tigeravatar** · 11-29-2011, 05:00 PM

Ahh, ok. Thanks shg, I had forgotten where that function came from.

@tapmonkey: ATP = Analyis ToolPak. Rather than try to explain it myself, here's the microsoft office article on how to load it: http://office.microsoft.com/en-us/ex...001127724.aspx

**tapmonkey** · 12-06-2011, 04:24 PM

Hey tigeravatar,

Thank you again for help with those formulas they work great in their current configuration. My spreadsheet is set up a little differently as it is all along one line. I am having trouble re-working the equations to fit in this line format. I still want the same thing. I have attached a sample of the sheet I am using. I want to have the "totalstatbonus" column number divided among the 4 remaining columns to the left of it. with 50% going to the key "stat" and the remaining value being randomly divided among the other three.
If you could help me re-work the equation that would be amazing!

Thanks!!Untitled spreadsheet.xls

**tigeravatar** · 12-06-2011, 05:15 PM

Unfortunately, links to attachments aren't working. You'll have to attach the file to the post. To attach a file, use the "Go Advanced" and push the button with the paperclip, browse to the required file, and then push the Upload button.

**tapmonkey** · 12-06-2011, 05:31 PM

Untitled spreadsheet.xls

is this correct?

**tapmonkey** · 12-06-2011, 05:32 PM

how about this

**tigeravatar** · 12-06-2011, 05:35 PM

Second one worked, looking into it

**tigeravatar** · 12-06-2011, 05:59 PM

Attached is a modified version of your sample workbook. I added a new column for "keystat" which contains a drop-down list of the different stats. The keystat gets 50% of the totalstatbonus, and the remaining points are randomly assigned to the other stats.

Just in case, I used the roudup(rand()*#,0) version instead of randbetween(). Here are the formulas for the different cells:

E2:
```
Please Login or Register  to view this content.
```
F2:
```
Please Login or Register  to view this content.
```
G2:
```
Please Login or Register  to view this content.
```
H2:
```
Please Login or Register  to view this content.
```

H2 is shorter because its the last stat and so instead of having a random value, it simply gets what's leftover (unless its the keystat)
G2 is longer because it has to check if column H is the keystat, in which case G2 gets the remainder, otherwise it gets a random value (unless its the keystat).

You can then just copy the formulas down as needed. Does that work for you?

**tapmonkey** · 12-06-2011, 06:31 PM

Hey Tiger,

once again thanks, this is awesome.

2 questions though. How does the drop down menu work? Is there code in there which related to the other fields when one keystat is selected?

Also why does H2 get whats left over instead of being mixed into the random value as well? Doesnt that meant that stat would always have the smallest value unless it was the "keystat" ?

**tigeravatar** · 12-06-2011, 06:55 PM

The drop-down menu is a data-validation list, and yes, the formulas reference what it has selected.
The random numbers generated for the first two stats may not be very big, so its entirely possible that the last stat could be the 2nd largest value and not the smallest.

**tapmonkey** · 12-06-2011, 06:58 PM

Ah I see, so since the first two stats are random, the remainder could be quite large still. Makes sense.

Could you please provide the equation for the drop down menu?

Thanks!

**tigeravatar** · 12-06-2011, 07:09 PM

The drop-down menu is a data-validation list with source: =$E$1:$H$1
How its referenced is in the formulas posted earlier.

Here's some links to information on data validation if you'd like to learn more about it:
http://office.microsoft.com/en-us/ex...010072600.aspx
http://office.microsoft.com/en-us/ex...001087228.aspx
http://www.contextures.com/xldataval01.html

Randomly dividing and assigning numbers

LinkBack

Thread Tools

Rate This Thread

Display

Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Re: Randomly dividing and assigning numbers

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions