View Single Post
  #10   Report Post  
DOR
 
Posts: n/a
Default Getting Excel to Calculate All Combinations of a Set of Data?

From what you have shown, it appears to me that what you want is this:

Given frequencies x, y, and z, generate all values pqr, where p can
vary from zero to p_max, q can vary from zero to q_max, and r can vary
from zero to r_max. Then generate the value p*x+q*y+r*z.

If this is true, try this; it may be what you need:

Put the values for x, y, and z, in A1, B1 and C1. (22, 50, and 33 in
your example)

Put the maximum values you want for p, q, and r in A2, B2, and C2.

Put zeros in A3, B3, and C3.

Put the following formulas in their respective cells:

A4: =IF(AND(B4=0,C4=0),A3+1,A3)
B4: =IF(C4<0,B3,IF(B3<$B$2,B3+1,0))
C4: =IF(C3=$C$2,0,C3+1)
D4: =A4&B4&C4
E4: =SUMPRODUCT($A$1:$C$1,A4:C4)

Now copy/drag these five formulas down until you have reached values
equal to p_max, q_max and r_max in each of columns A, B and C. If p,q
and r = 9 this will be about 1000 rows, if they each equal n, it is
about (n+1)^3 rows. That should produce the appropriate sums of the
three frequencies where the value in column D represents the mode,
although you may not need this column, since cols A, B, and C provide
the same information.

Is this close to what you want?