PROB

Returns a result from a list of probabilities.

Syntax:

PROB(values, probabilities, start, end)

values is a range or array of numbers (possibly unordered).

probabilities is a range or array of numbers of the same size as values, indicating the corresponding probability (>0 and <=1) that each value in values will occur. The numbers in probabilities must sum to 1 exactly.

PROB finds all values which are between start and end inclusive and returns the sum of their corresponding probabilities.

end may be omitted, in which case PROB returns the probability corresponding to start (or 0 if start is not present in values).

Advanced topic: PROB evaluates the values and probabilities parameters as array formulas - see the examples.

Example:

PROB({3, 4, 5, 6}, {0.2, 0.4, 0.3, 0.1}, 4, 6)

returns 0.8, the sum of the probabilities for 4, 5 and 6.

PROB({2.2, 5, 1}, {0.5, 0.3, 0.2}, 0, 3)

returns 0.7, the sum of the probabilities for 1 and 2.2.

PROB({3, 4, 5, 6}, {0.2, 0.4, 0.3, 0.1}, 4)

returns 0.4, the probability for 4.

Evaluation as an * array formula:

PROB(A1:A2*2, B1:B2, 2)

where cells A1, A2 contain 1, 4 and cells B1, B2 contain 0.3, 0.7, returns 0.3. PROB forces evaluation of values as an array formula, so that A1:A2*2 yields the array {2 | 8}. Thus PROB({2 | 8}, B1:B2, 2) is actually evaluated to give the result.

PROB(A1:A2, B1:B2/100, 4)

where cells A1, A2 contain 1, 4 and cells B1, B2 contain 30, 70, returns 0.7. PROB forces evaluation of probabilities as an array formula, so that B1:B2/100 yields the array {0.3 | 0.7}. Cells B1 and B2 effectively contain percentages here.

You want to find the probability that a randomly selected light bulb has a lifespan between 1000 and 1500 hours (inclusive).

You would use the PROB function as follows:

PROB(A1:A6, B1:B6, 1000, 1500)

• A1:A6 is the values range, which contains the lifespan in hours.
• B1:B6 is the probabilities range, which contains the corresponding probabilities for each lifespan value.
• 1000 is the lower_limit (start), representing the minimum lifespan you're interested in.
• 1500 is the upper_limit (end), representing the maximum lifespan.

The function will return the sum of the probabilities for the values that fall within the specified range (1000, 1250, and 1500). In this case, the result would be:

0.35 (for 1000 hours) + 0.25 (for 1250 hours) + 0.15 (for 1500 hours) = 0.75

This means there is a 75% probability that a randomly selected light bulb will have a lifespan between 1000 and 1500 hours.