I am building an Excel optimization spreadsheet which calculates the probability that two or more basketball players will combine to score X amount of points or greater on any given night. The historical data I have produces a normal curve for each player and I can calculate the mean and standard deviation for each player's population of historical scores.
I then create a list of potential scores between 1 and 50 (neither of the players has ever scored more than 50 points) and calculate a z-score for each potential point total for each player.
I then calculate the probability for each score (e.g. 1, 2, 3, 4, etc... through 50) for each player using the 1- normsdist Excel function.
This provides me with two lists of probabilities, one for Player A and one for Player B. Each list starts at near 100% probability for each player for the scores of 1, 2 3, etc... The mean of each player's scoring is listed at a 50% probability and numbers above the mean become progressively less probable as they become greater and greater.
My question is how do you mathematically calculate the probability of achieving a combined score of X (e.g. 1, 2, 3, 4 etc... through 100) for every point total possible? I initially thought this could be done by multiplying the probabilities for each score but quickly realized that the problem was much more complex as there are multiple ways to score each point total that must be accounted for. For example, if I want to know the probability of Player A and Player combining to score 10 points or more this could be achieved through Player A scoring 10 points and Player B scoring 0 or Player A scoring 9 and B scoring 1 etc...
Any input would be appreciated. Thanks in advance!