Probability is a statistical calculation that compares data in a manner that determines the likelihood a specific event sequence will occur or not occur. Probability is an important function in business, sports and even public safety. Learning how to calculate probability in Excel is simple because its built-in functionality runs the calculations automatically.
Building Initial Tables
Video of the Day
The process used to calculate probability begins with the spreadsheet row and column setup. It must have order to properly arrange the data in a manner that makes distribution modeling possible. The probability modeling begins with a single column that lists the events and a secondary column for probability of each individual event. The overall probability is calculated between the upper and lower limits you enter at the bottom of the column.
Video of the Day
In the primary column, begin by listing each event in successive rows. For example, a baseball team can begin with a one as the first row for probability of scoring a single run and successively build up to ten. The probability for each event is entered in the next column and the calculation will generate the probability between the values you enter at the bottom of the column for the lower and upper limit you're interested in.
This same process can apply to the number of units sold for a sales team, any number of sporting events or even the likelihood of rolling a specific number on dice in a craps game. The lower and upper limits are critical because the number of possibilities between these limits influences the probability outcome. After building the column of possibilities, entering the equation to generate the probability results is the next step.
Calculating Probability in Excel
The probability equation requires the range, probability range, lower limit and upper limit to calculate an exact result. The equation itself looks like this without the numerical values entered: PROB=(x_range, prob_range, lower limit, upper limit). The exact cell numbers are required to calculate and display the probability. Essentially, you tell Excel the range of possible outcomes (x_range) and their individual probabilities (prob_range) to calculate the total probability for a result between the lower and upper limit.
If you began the column of possibilities in A2, ended in A7, put their corresponding probabilities in B2 to B7, and want to display the upper limit in B8, lower limit in B9 and total probability in B10, you will write this equation in the B10 cell: PROB=(A2:A7, B2:B7, B8, B9). Press "Enter" after adding the equation to the B10 cell and the probability for a result between the limits will display in the B10 cell. The process is consistent regardless of the application or type of data entered in the primary column.
Normal Distribution Calculations
Calculating probability as a normal distribution is another common method used in Excel. The process requires the mean and standard deviation to calculate probability outcomes. The alternative is using a Z Table but Excel makes it much easier and quicker to calculate probability when the specific mean and standard deviation numbers are available.
To calculate on the normal distribution model, click an empty cell followed by Insert Formula to generate a box. Type Normdist and press enter to return a dialogue box for the calculation. Enter the mean number and standard deviation number in the correlating boxes. Lastly, type the number you wish to know the likelihood of occurring in the box labeled X and press enter to return the probability of X.
The number will generate the exact probability of a specific outcome occurring. You can play with the outcome number and even create a range for the probability of occurrence between two values. This is much easier than consulting a z-table to determine the z-score probability.