First and foremost, THANK YOU in advance for helping me with my statistics project that I have been unable to solve on my own or through the help of my statistics book and google.
I am working on an excel spreadsheet for use with the game Eve Online. I will give you a bit of background on what this involves below:
A player owned starbase out in space allows its owner to place various defensive modules around its perimeter. These modules randomly target enemy ships on the battlefield, applying various detrimental effects to them.
INITIAL GOAL: To find average number of unique enemy ships targeted at any given time.
The spreadsheet will allow the user to input the number of enemy ships on the battlefield as well as the number of defensive modules present. It will need to use this information to fulfill my initial goal. Let me illustrate with an example:
3 Ships on battlefield 3 modules on battlefield
I have found a way I can find the total number of available combinations by the following formula: (ships on battlefield)^(modules on battlefield). My logic behind this is from viewpoint of the module: one module has t potential targets (3 in this case) and since there are m modules, you multiply each set of potential choices together (3 in this case) which is 3^3 = 27 total combinations (which agrees with my testing results of manually listing all of the combinations).
I need to find the average number of UNIQUE ships targeted on the battlefield. This means if module 1, 2 and 3 are all targeting ship 1, this only counts as 1 unique ship targeted. If module 1,2 and 3 are all targeting different ships I now have 3 unique ships targeted.
My aim (correct me if i'm wrong, or if this is a flawed approach) in finding the average number of unique ships targeted at any given time is by finding the 'expected value' of the discrete random variable. i.e., make the random variable x = ship number (in this case ship #1, ship #2 and ship#3, or more simply 1, 2 and 3). Then, multiply each possible value of x by its probability p(x), and then sum this product over all possible values of x.
So, we have 1*p(x) + 2*p(x) + 3*p(x) = E(x), or average unique ships targeted. For this illustration, I have manually found the probabilities by listing all combinations and counting the number of combinations that fulfill each state as follows (refer to screenshot for how I did this):
Total combinations available: 27
1 Unique ship targeted: 3 = 3/27 = 1/9
2 Unique ships targeted: 18 = 18/27 = 2/3
3 Unique ships targeted: 6 = 6/27 = 2/9
So, plugging these probabilities into the above equation, we have:
1*(1/9) + 2*(2/3) + 3*(2/9) = E(x) = 2.111 ships targeted on average.
This is great, however, I have yet to find (outside of manually listing all available combinations and counting the ones that fulfill my criteria) a way to find the probability for x ships targeted, of which the above formula requires to work properly. I know it will involve using combinatorial / permutation counting rules which use factorials but I have been unable to find one that fits my requirements.
I think the main difficulty of finding a suitable counting rule to isolate the number of possibilites which would result in x number of unique ships targeted arises from the modules' ability to target a ship which is already targeted by another module. For instance, if I just wanted to find all the different combinations of selecting 2 ships from the 3 available, I would use 3^2 which = 9 but as you can see above, it is actually 18 so this cant be right. Likewise, if I use N!/(N-n)! I get = 6, still not 18. I try N!/n!(N-n)! and get = 3... still not 18.
Most likely this will involve using multiple combination and / or permutation counting rules working together in a certain way to achieve my goal, however I am just not experienced enough to figure this out. Any help is much appreciated!
After we tackle this part of my goal, I will post the next goal for this project and perhaps we can take a stab at that as well