Odd request for help: I need a mathematician!

[Oryan77]

Hi, I have been working with another gamer to create an Excel based Random Treasure Generator. This is a tool created by a couple of D&D guys that want to use it for their own personal game and provide it for free to the public when we are finished. I hope that by being an Excel file it would make it pretty easy for someone to customize the item index for their own game/system/edition. If they don't need to customize it, it can certainly be used as a generic treasure generator that consists of over 100,000+ items with sourcebook page references (using 3.5e sources as a current default). It really is turning out to be a great generator.

What I really need though is some help with determining appropriate chart percentages for random dice rolls based on the frequency or commonality of certain items. I have a lot of data for the item index and I would like a mathematician to crunch my numbers and determine what our dice rolling charts should look like.

For example, if a result was for an item from the "Art Object" category, the generator would next roll to determine the value of the item and select an item from a list of items within that gold piece range. The problem is that within that gp range, I may have 500 different types of "Tapestries" worth 100 gp and only 1 "Painting" worth 100 gp. So a user would probably never receive a painting as a result. I assume I would need to limit the tapestries down to fewer results and if the generator rolls for one, then it would roll again for determining which of the 500 tapestries is the final result.

I just don't know how to determine fair percentages for our charts based on the number of items we have available and how often a particular item should appear as treasure over another item.

I need someone good with numbers to look over my data and check whether or not certain items (like tapestries) are going to be problematic and then write out an appropriate range for percentages on each chart so a user has a proper chance of receiving particular items as treasure.

I hope that makes sense. I'm having a difficult time trying to explain this.

Thanks!

 

[Jhaelen]

That doesn't sound like a math problem to me. Math doesn't care whether your PCs never find a painting or not.

If you don't want tapestries to come up all the time, you need another intermediate table that selects a subtype of your art objects, subtypes being tapestries and paintings.

 

[pemerton]

I think that [MENTION=46713]Jhaelen[/MENTION] is right.

I assume I would need to limit the tapestries down to fewer results and if the generator rolls for one, then it would roll again for determining which of the 500 tapestries is the final result.

That is the "intermediate table" that [MENTION=46713]Jhaelen[/MENTION] refers to.

Deciding the numbers for that intermediate table isn't a maths problem, it's just deciding how often do you want tapestries vs paintings.

 

[Oryan77]

That doesn't sound like a math problem to me. Math doesn't care whether your PCs never find a painting or not.

Math does care what your PCs find, otherwise math wouldn't insert itself as percentages on charts when rolling random treasure.

If you don't want tapestries to come up all the time, you need another intermediate table that selects a subtype of your art objects, subtypes being tapestries and paintings.

Yeah, I will need a subtype for the charts (which I mentioned). That's one of the things that I need help with. I need someone to crunch the numbers and get an average for how often multiples of items appear in the index, then make a decision on which of those items should have a subtype chart and which items are fine having without a subtype chart.

Looking at averages and determining percentages for charts (and creating subtype charts) is what I need help with. Someone that is good at looking at data, sorting it, and analyzing it for this purpose would be helpful.

I'm realizing though that with the amount of views this thread has and the responses I got; people would rather tell me what I don't need instead of offering to help out. In any case, I could use some help regardless of what the complexity might appear to be for this task. If it seems like a piece of cake and you think I'm an idiot for needing help, awesome, I would love your help! I suppose nobody is interested in helping though, but I figured I'd give it a shot. Thanks.

 

[Legatus Legionis]

.

 

[Oryan77]

It sounds like you need a "VLOOKUP", "HLOOKUP", and "GETPIVOTDATA" functions of excel.

That's exactly what I started to do when first going into this. Excel formulas are not my strong point, but I did start watching tutorials in an attempt at making some sort of pie graph so that I could see this kind of data and make appropriate charts based on it. As you pointed out though, it's hard to figure out and get right. That's about when I had the idea of coming here and asking for help.

Maybe my approach should be more towards asking people fluent with Excel for help. Mr. Excel is a great place for that, so I should probably post on their forums. I just thought there would be some smart gamers here that would like to lend a couple of guys a hand. I figured it would be beneficial to get help from someone that is familiar with the game.

Well, thanks for the advice.

 

[pemerton]

I'm realizing though that with the amount of views this thread has and the responses I got; people would rather tell me what I don't need instead of offering to help out.

In my case, I don't really understand what the question is - as the maths you're referring to just looks like counting. Ie how many tapestry entries are there, how many painting entries, etc.

Once you count how many of each, you can then work out (1) how many categories there are, (2) how frequent you want each of the categories to be relative to the other, (3) what the spread of numbers should be for each category, then (4) what the ratio is of that spread to the number in each category. If that ratio is roughly 1:1, you don't need an intermediate table. If it is not, you do.

If that's not what you're talking about, then I've completely misunderstood the nature of your problem.

 

[GMMichael]

Looks like you're in good Excel hands...check out some ENWorld treasure generators to evaluate your results: http://www.enworld.org/forum/dnd_view_block.php?id=1438

 

[Legatus Legionis]

.

 

[tomBitonti]

Hi,

It sounds like you are preferring to set the item value, then generate a type, then do a lookup within that type with selection limited to the preset value.

An alternative would be to let the tables have items of multiple values. I presume your method was chosen because you want firm control over the reward value — which I find reasonable.

But, I don’t see much to figure out. The selection mechanism maps closely to the data organization.

Maybe you are looking for something like this:

Art [0..9] v = 10
Art [10..29] v = 50
Art [30..49] v = 100
And so on. Here [x..y] v=z means the items x through y all have the value z.

To do a lookup based on a value or on a range of value, an intermediate table would be needed, with the low and the number plus 1:

10 : 0 - 10
50 : 10 - 30
100 : 30 - 50

Plus 1 because the difference gives the size of the range. It makes the figuring simpler.

Then a lookup of an item of value 50 to 100 would start with the start number of the lower value and end with the end number of the high value. Then, there would be a random number generated in that range for the selection:

Art [ Random( Start[1] .. End[2] ) ]
Art [ Random( 10 .. 50 ) ]

The random number math is just picking a number in a range (assuming all choices are to be equally likely). The figuring is more about structuring the tables, and importantly, what to put in the intermediate tables.

Hope that helps!
TomB