Given a list of values and a corresponding list of probabilities, the Excel PROB function calculates the probability associated with a given range.
The format of the function is:
where the arguments are as follows:
|x_range||-||An array of values that have associated probabilities.|
|prob_range||-||x_range array and the values in prob_range must add up to 1).(This array must have the same length as the|
|lower_limit||-||The lower bound of the range of values for which you want to calculate the probability.|
|[upper_limit]||-||[upper_limit] is omitted, the function simply returns the probability associated with the value of the supplied lower_limit).(If the|
Columns A and B of the spreadsheet below contain a set of values and their associated probabilities. These values and probabilities are provided to the Excel Prob function, in cells C1-C3 of the spreadsheet.
|1||x||probability||=PROB( A2:A9, B2:B9, 3 )||- Returns the value 0.22|
|2||0||0.1||=PROB( A2:A9, B2:B9, 3, 5 )||- Returns the value 0.52|
|3||1||0.15||=PROB( A2:A9, B2:B9, 8, 10 )||- Returns the value 0|
Further details and examples of the Excel Prob function can be found on the Microsoft Office website.
If you get an error returned from the Excel Prob Function, this is likely to be one of the following:
|#N/A!||-||Occurs if the supplied x_range and prob_range arrays are of different lengths (i.e. contain different numbers of data points).|