Creating a merged probability table from a granular probability table

[misterhanky]

Say you have a table (5x5, for argument's sake)-- along the top is
demand from customer A, on the bottom is demand for customer B. Say the
legend for both the rows and the columns is (0,1,2,3,4), and in the
table itself is the corresponding probability for each pair.

In other words, if you want to see the probability that demand from
both customers was zero, you'd look in the upper left corner of the
array. If you wanted to see the probability that customer A demanded 2,
and customer B demanded 3, you'd go down 3, and over 4 (remember that
0,0 is upper left, not 1,1).

Without using a macro, or manually writing out sum commands, is there a
way to create a total probability distribution table from the table I
have given you? In other words, we know that minimum demand is 0, and
that sum is given by the upper left cell, but the probability that the
total demand is 3 is the sum of cells (0,3), (1,2), (2,1), and (3,0).
The table in this example would be 9 wide, with the probability of 0 on
the left, and 8 on the right.

Perhaps there's a way to match a VLOOKUP, and HLOOKUP, and a few IFs,
maybe, but if anyone can suggest a more automated way or a clean
syntax, I'd be grateful.

Thanks!

 

[Herbert Seidenberg]

Tools > Options > General > R1C1 Reference Style
Name your table of probabilities ArrayP
Name a 9 row vector Bin and enter 0 thru 8
Insert > Name > Define the following:
Row_A ={0;1;2;3;4}
Col_B ={0,1,2,3,4}
ArrayS =Row_A+Col_B
Adjacent to vector Bin enter the formula
=SUMPRODUCT((ArrayS=Bin R)*ArrayP)
and copy down