Results 1 to 2 of 2

Math Help - Help w/ Excel spreadsheet & probability

  1. #1
    Junior Member
    Joined
    Apr 2010
    Posts
    57

    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?
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Senior Member
    Joined
    Oct 2009
    Posts
    295
    Thanks
    9
    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

    pr(X=0)=0.2054*0.0778*0.3\approx 0.0048 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:

    pr(X=1)=(0.4282*0.0778*0.3)+(0.2054*0.2592*0.3) +(0.2054*0.0778*0.7)\approx 0.0371 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.
    Last edited by downthesun01; May 20th 2010 at 01:04 AM.
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Excel Spreadsheet Problem
    Posted in the Math Topics Forum
    Replies: 1
    Last Post: June 16th 2011, 08:14 AM
  2. Spreadsheet equations
    Posted in the Advanced Algebra Forum
    Replies: 1
    Last Post: March 2nd 2010, 08:30 AM
  3. Need Blackjack Spreadsheet
    Posted in the Advanced Statistics Forum
    Replies: 0
    Last Post: January 27th 2010, 03:49 PM
  4. Any good probability (in excel) learning resources?
    Posted in the Advanced Statistics Forum
    Replies: 0
    Last Post: August 7th 2009, 03:53 PM
  5. Spreadsheet help
    Posted in the Pre-Calculus Forum
    Replies: 0
    Last Post: October 28th 2008, 04:37 PM

Search Tags


/mathhelpforum @mathhelpforum