Thread: Help w/ Excel spreadsheet & probability

Hi. I am trying to make a spreadsheet to calculate the likelihood of certain items appearing in a game called Monster Hunter Tri for the Wii (hopefully some of you have played it)

Basically this is what happens:

When you kill a monster, you get to "carve" its body 3 times. There are certain items you can get from this, all of which have their own probabilities. Then you get 5 items for completing the quest, again, each item with its own probability. Then, if you succeeded in "breaking" a part of the monsters body, there is a chance for even MORE items. etc.

So I want to be able to calculate the total % chance of getting an item once, twice, three times, etc.

Here's what I've made so far, in excel. See if you think the current numbers are correct.



So to remind you.

Body carves you get 3
Quest Rewards = 5
Tail Carves = 1
Shiny Drop = 1
Destroy Body Part = 1 per body part

For example, if I beat this monster (lagiacrus) and I do EVERYTHING possible meaning I kill him, do the quest, get a shiny drop and break all of his body parts, what are the chances I get 1 Lagiacrus Hide, 2 Lagiacrus Hides, 3, etc., for each item?

How did you calculate exactly 1, 2, and 3 for the body carves? Your numbers look off.


Anyway, here are the calculations for Lagiacrus Hide.

X represents the number of Lagiacrus Hide dropped. As you can see the most pieces of Lagiacrus Hide that can be dropped for the Lagiacrus monster is 9 (3x from carving its body, 5x from quests drops, and 1x from destroying its chest). These probabilities are only accurate if you "do EVERYTHING possible meaning I kill him, do the quest, get a shiny drop and break all of his body parts."

pr(X=0) 0.48%
pr(X=1) 3.71%
Pr(X=2) 12.21%
pr(X=3) 22.69%
pr(X=4) 26.50%
pr(X=5) 20.28%
pr(X=6) 10.21%
pr(X=7) 3.27%
pr(X=8) 0.60%
pr(X=9) 0.05%
Total 100.00%

For each of the above you have to calculate the number of outcomes that will yield the desired result, calculate the probability of each outcome and add each of those probabilities together.

For example,

pr(X=0)

There is only one way in which you can receive 0 hides. 0 from carving its body three times, 0 from quest rewards, and 0 from destroying its chest (0,0,0)

so pr(X=0)=pr(0 hides from carving its body)*pr(0 hides from quest rewards)*pr(0 hides from breaking its chest)

from my numbers this comes out to be

or 0.48%

Another example,

pr(X=1)

The number of ways that you can receive exactly 1 Lagiacrus Hide are:

(1,0,0)
(0,1,0)
(0,0,1)

So you have to calculate the probability for each of those outcomes and then add the probabilities together.

pr(X=1)=[pr(1 hide from carving its body)*pr(0 hides from quests)*pr(0 hides from breaking its chest)] + [pr(0 hide from carving its body)*pr(1 hides from quests)*pr(0 hides from breaking its chest)] +[pr(0 hide from carving its body)*pr(0 hides from quests)*pr(1 hides from breaking its chest)]

From my numbers, this came out to be:

or 3.71%

And you just follow the same procedure for pr(X=3) through pr(X=9).

Good luck. It's going to take a long time to get this done. Maybe there's a faster way of doing this.. I don't know.