# 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

##### Villager
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

##### Legend
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

##### < BLAH HA Ha ha >
It sounds like you need a "VLOOKUP", "HLOOKUP", and "GETPIVOTDATA" functions of excel.

The "GETPIVOTDATA" function is used to get the item from a master data table.

It does not create a random number, but it uses it to find same item.

And depending on how big/complex your data is, will determine how many values you will need in the formula.

It will take some time to get used to using the table function(s) of excel, so don't get upset if the first few times you can't get the results you wanted.

It took me some 40+hours to play with/test how they work before I got it to where I could use it for that I required. But once you do, you will be amazed with the results.

#### 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.

#### pemerton

##### Legend
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.

#### Legatus_Legionis

##### < BLAH HA Ha ha >
How simple/complex are you looking at for your personalized Random Treasure Generator?

I can send you a simple EXCEL file showing how the LOOKUP function would work on a Random Treasure Generator.

From there, seeing how it works, you can expand on it to increasing the size of a table, adding more tables, and adding more return values.

If you get the handle on LOOKUP, you can then try your hand at the more complex VLOOKUP & HLOOKUP.

They do best looking at one roll of the dice at a time

GETPIVOTDATA I would save till your are comfortable using tables and the various functions using them, as it is like looking at all your rolls and giving you the FINAL result.

If the attachment worked:

J2 should have the formula:
=LOOKUP(C2,F15:F16,J15:J16)

K2 should have the formula:
=LOOKUP(C2,F15:F16,K15:K16)

J3 should have the formula:
=IF(K2="II",(LOOKUP(C3,F24:F27,J24:J27)),LOOKUP(C3,F35:F46,J35:J46))

K3 should have the formula:
=IF(K2="II",(LOOKUP(C3,F24:F27,K24:K27)),LOOKUP(C3,F35:F46,K35:K45))

Personally, I would make the main sheet your enter ROLLS and display results page.
I would have a separate sheet for each table and sub-table

#### tomBitonti

##### Explorer
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

#### Legatus_Legionis

##### < BLAH HA Ha ha >
Yes, and I am getting there.

I am going to go step by step.

First, showing how a TABLE and LOOKUP function works.

Second, showing how a TABLE and VLOOKUP/HLOOKUP function works.

Then, because the most complex is determining the value of art objects (tapestries, paintings, jewelry, etc.) based on location (Capital City, City, Town, Village, Caravan, Lair, Dungeon, etc.) and how a GETPIVOTDATA works best for it.

Each location would have a different odds of finding as well range of worth.

So by entering location, we get different results for the same potential art objects.

Knowing how a PIVOT TABLE works, one must know how the VLOOKUP and HLOOKUP works on a regular table, and one must know how LOOKUP and a regular table works.

When I finish work today, I'll show an example of the VLOOKUP and HLOOKUP, before doing the biggie!

Last edited:

#### Legatus_Legionis

##### < BLAH HA Ha ha >
Using LOOKUP, we are limited to getting results from ONE line, and we have to HARDCODE which column results to displace.

Using VLOOKUP, we have greater flexibility of getting results from a whole table.

F4 is where one enters the "NUMBER OF DICE" one needs to be rolled.
F5 is where one enters the "DICE TYPE" one needs to enter.

In the "RANDOM DICE ROLLER" table E13 thru to N17, we have the RANDBETWEEN FORMULA.

For example;
in E13 should have the formula:
=RANDBETWEEN(D13,D13*E12)

It will generate a random number between the value of "number of dice" (D13) min number one can roll and the max value one can roll (number of dice (D13) times dice type (E12) ).

To should how the formula changes on each cell in the table, and each cell is slightly different;
in G14 should have the formula:
=RANDBETWEEN(D14,D14*G12)

It will generate a random number between the value of "number of dice" (D14) min number one can roll (in this case 2 is the lowest it can generate) and the max value one can roll (number of dice (D14) times dice type (G12) (in this case 6 is the largest it can generate 2x3)).

The last cell that one must know, is in F6, where the final result of the "RANDOM ROLLED" answer comes.

Here the formula in a combination of the VLOOKUP and the LOOKUP Function. I'll explain.

in F6 should have the formula:
=VLOOKUP(F4,E13:N17, (LOOKUP(F5,E12:N12,E10:N10)),FALSE)

Starting to look complicated, but the explanation will makes sense of it all.

The VLOOKUP function needs to look up based on how many columns are in the table, and not the value in the table heading.

The RED numbers in row A10 is there to show number of columns in the table.

The (LOOKUP(F5,E12:N12,E10:N10) in the formula takes the "DICE TYPE" we asked for in F5, and converts in to which column VLOOKUP needs to find.

To explain, VLOOKUP starts with getting the value from F4 "NUMBER OF DICE". In our example we put in the number 2. So it will look for a value in the second roll of out table.

E13:E17 is our table. In our example, VLOOKUP will look at the values in the second row, or E14 thru to N14.

Next, it needs to know what column it needs to find the result to. This is where we needed to use the LOOKUP function. In our example, in F5 we asked for the Dice Type 6, or d6, and that d6 is the 5th column in the table, the RED 5 I have showing in the image.

So the second row and the fifth column, we got a random roll of 5.

If we had entered Number of Dice as 3 with Dice Type 12, we would have gotten a result of 25.

And the what RANDBETWEEN works, each time a new value is entered, it recalculates everything. One can also recalculate by CALCULATE NOW (F9 key).

Last edited:

#### Legatus_Legionis

##### < BLAH HA Ha ha >
One can ask, why include 1d1, 2d1... 2d1, it is because when we do the FULL GETPIVOTDATA, it is best to have them.

Since, using TABLE 7-4: Treasure from DMG 3E, page 170, when we look at the rolls, for example, ITEMS we start at 1st level 1 mundane and 1 minor as possible results. Later as we hit higher levels, we are asked to roll 1d3, 1d4, 1d6, etc.

So those 1 only is the same as 1d1. We can now easily have those in our table.

Also, the functions for the TABLE 7-4 is only the rolls. The multipliers are part of the PIVOT Table.

Example, 1st level if one finds copper pieces, it knows it is 1d6 x 1,000. The roll table does the 1d6. The PIVOT Table can determine base on 1st level, the random number needs to be multiplied by 1,000. At 9th level, if we find gold pieces, it knows it needs a random roll of 5d4, and that it needs to be multiplied by 100.

The PIVOT table can take several input data, LEVEL, percentile for COINs, and get the right dice and multipliers, and the same for GOODS and ITEMS.

That is how well PIVOTData function works.

Creating that table, requires the MASTER Data Table, which we use to create a PIVOTData table. It is that table we use to look up the results.

That process and function formulas are more complex, but once it is done, updating and expanding the Data Tables are easy. The formula stays the same.

I will have a small MASTER Data Table, PIVOTData table, and an example of the formula when I have the time.

#### Legatus_Legionis

##### < BLAH HA Ha ha >
For your treasures' table for Art/Jewelry/Tapestries/etc., do you know approximately how many items we are looking at?

What sort of details does this table have?

Item type (Art, painting, jewelry, tapestry, etc.)
Item value (\$x,xxx GP)
Item size (10' x 10', 5' x 8', etc.)
Quality (poor, good, very good, exceptional, masterpiece, etc.)
Description of Item (portrait-individual, portrait-family, map-kingdom, map-county, map-city, scenery, etc.)

I'll have more questions once I see the file.

#### Legatus_Legionis

##### < BLAH HA Ha ha >
First Impression:
These are just my thoughts on what I think we need to do

Oh man.

Now I see why you wanted help on this one. Your listing of Rarities is very thorough.

You really need sub-tables and sub-sub-tables for this.

Looking at your "not yet finished" "RARITIES" tab, with over 15,000 entrees and those entrees end with the "statue" catagory.

Don't know how many more you are thinking of adding, but we can easily see what we can do.

Some of them we can easily simplify.

For example, you have many items (bust, cube, cylinder, etc.) that all use the same made out of description of like 45+ materials.

So a sub-table of: "Item Made From" would work perfectly for this.

The same goes for the art-work.

Many have the same type of art depictions: Battle scene, boat scene, castle scene, etc.

Since the scene does not affect the value of the art object, making the art "scene description" its own sub-table would work for this as well.

That too is very easy for me to do for you.

*** *** ***

So then once we do that, we can see how many Item base Catagories there are, and make the odds of rolling each.

#### Legatus_Legionis

##### < BLAH HA Ha ha >
Second Impression:
These are just my thoughts on what I think we need to do

Looking at your "New Treasure Tables", I like how you started it but...

I do have some suggestions.

It is great that you are using the "GROUP" function of Excel to hide/display tables, I highly suggest you us the "header/title" row between them as well. For once a table is condensed, we do not know what it is unless we open each and every "group" to find the table we are looking for.

By using the header/title, we can condense the table and find it easily.

Just think of it this way, on your generator chart, if you left all the tabs along the bottom with the default "sheet 1", "sheet 2", you would never find anything in a quick and timely manner. You renamed them. That is exactly what we also need when we use the "group" function.

As someone who has used EXCEL for a long time (not a PRO - not my full time job), I am not criticizing what you have done. I only see, from personal experience using EXCEL, what tweeks we can do to make it more user friendly.

There are some things I would do different, with the layout of your tables, especially if you want to automate it in the future. But those are more personal preference and has nothing to do with usability of the tables.

As a side project I can show you sample(s) of what I would suggest for this.

#### Legatus_Legionis

##### < BLAH HA Ha ha >
If we break down the items, and combined a few, we still get 171 catagories.

The following I would put together;

Armband/Armbands
Bracer/Bracers
Candlestick/Candlesticks
Gauntlet/Gauntlets
Glove/Gloves
Statuette/Statuettes (pair)

Was thinking to put Skull/Dragonskull together also.

Some have only one entry, others 50-100.

But I think we should do all as if we plan on adding more to them (in the future). So each would get its own sub-table.

So for the main table, I would suggest they all have approximately the same odds of rolling except something like;
Altar
Arcane Library
Dragonskull (if left in its own catagory)
Full Plate Armour
Pyramid
Throne
Tree

They would only get a "1-percentage chance" out of d1,000.

As for the sub-tables, the only thing I can see to get odds is to base it off the cost of the item.

Say if item X is 5 GP and item Y is 10 GP, you are twice as likely to find item X over item Y.

#### Legatus_Legionis

##### < BLAH HA Ha ha >
We can break down the items even further:

We can make a category called:

It would combine the following;
amulet
anklet
armband
bracelet
bracers
broach
circlet
earrings
earrings & nose rings (would need expanding)
hairpin
hat pin
locket
necklace
pendant
periapt
phylactery
pin
ring
scepter
vase.

Each of these has almost the exact same number of entries base on the same think - What is it made of. They range from 47 to 64.

I would suggest trying to make them as similar as possible.

For example, while most have one entry for the item made from silver, others have two or three entrees.

Simplifying it to one "silver" entry per item, etc.

#### Legatus_Legionis

##### < BLAH HA Ha ha >
We can break down the items even further:

We can make a category called:

"WORKS OF ART"

It would combine the following;
bust
cube
cylinder
dodecahedron
pyramid
statue
statuette

Each of these has almost the exact same number of entries base on the same thing - What is it made of/image of. They range from 1175 to 1195.

Paintings we can leave separate as it is more unique items than generic items. If we expand them to meet the above criteria, the "what is it made of" would be its frame, and then the image would be the same generic as the above would be.

Tapestry category is TOO big/TOO many variations (4086) that it would still have to be its own table.

#### Legatus_Legionis

##### < BLAH HA Ha ha >
An example of the chart for "ASSESSORIES AND ADOREMENTS"

Dice roll for Amulet, Anklet, etc. will be added.

So after we roll for what item, we then roll for material. Look on the table to see what it is worth.

So we have a column for each type of accessory and adorement.

Same type of table would be create/used for "WORKS OF ART".

Last edited: