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?
|