In trying to systematically enumerate the possibilities when rolling four identical but loaded four-sided dice, I came across some unusual excel behavior. Hoping someone can shed some light on what’s going on under the hood.

The following table illustrates the possible roles of a die:

1000 A

0100 B

0010 C

0001 D

each row is a possibility with a distinct probability.

In trying to display all possible combinations of four roles of such a die– where the fist combination might be A + A + A + A or 4000, the second might be 3 1 0 0, and so on–I decided that I wanted to systematically offset A by 0,1,2, or 3 rows for each of four roles then sum the results. Oddly, though, I get the following. (all formulas are array formulas keyed in with shift+ctrl+enter).

=TRANSPOSE( SUM( OFFSET( A, 4x1ArrayOfRowOffsets, 0)))

displays the correct sum. Likewise if =TRANSPOSE(…) is replaced by =INDEX(…,1,1). I take it because both functions natively support array arguments. However,

=SUM( OFFSET( A, 4x1ArrayOfRowOffsets, 0))

does not work–it seems that here the summation is conducted along the 4 rows returned by offset, each of which has value 1. Oddly,

=SUM( TRANSPOSE( OFFSET( A, 4x1ArrayOfRowOffsets, 0)))

does not work either–the transpose makes it so the summation is properly conducted along the columns returned by offset, but seems to throw out all but the first column. Interweaving INDEX calls does not fix the problem.

All that is to say, calling SUM(OFFSET(—)) with array arguments seems to produce varied output depending on what is doing the calling. Why is this? What is actually going on, here?