I have a messy Excel formula that I’m trying to figure out. I have data with 2 or more columns, each associated with the probability (labeled p1, p2, p3, etc.) of a particular whole number outcome (marked by the column labeled “row#”). To find the final probability, the probability of the sum of the whole numbers coming up when all the scenarios are combined, I need the sum of the products of all of the probabilities where any combinations of the row# add up to a target value.

For example, in the image below, to find the probability of pFinal being 3, I need to multiply every combination of p1, p2 and p3 where their corresponding row# adds up to 3.

So where p1 is 3, p2 and p3 would both be 0. So I would multiply the value of p1 at 3, p2 at 0 and p3 at 0. But where p1 is 2, p2 could be 1 and p3 0, or p2 could be 0 and p3 could be 1. So I would need to add in the product of those two trios as well. To get the final value of pFinal at 3, I would add up there products:

pFinal =
p1-3 * p2-0 * p3-0  +
p1-2 * p2-1 * p3-0  +
p1-2 * p2-0 * p3-1  +
p1-1 * p2-2 * p3-0  +
p1-1 * p2-1 * p3-1  +
p1-1 * p2-0 * p3-2  +
p1-0 * p2-3 * p3-0  +
p1-0 * p2-2 * p3-1  +
p1-0 * p2-1 * p3-2  +
p1-0 * p2-0 * p3-3

row# 4 and higher wouldn’t come into it for calculating pFinal at 3, since 4 > 3, but would be needed to calculate pFinal at 4.

I know excel has a bunch of functions for handling probabilities and combinations, not to mention SUMPRODUCT, but I’m not sure if there is a set of formulae that can handle this dynamically.

enter image description here

Thanks for any help on this!

( I did figure out how to do this with only p1 and p2, using INDIRECT, ADDRESS and SORTBY to manipulate the arrays/ranges, but as soon as I add in a p3, that solution goes out the window.)