Excel Random Values with 2 Constraints

Question

I've been attempting to build a matrix in Excel with random values. However, these random values must follow two constraints, which are a limit in the column total and another in the line total.

Take this matrix as an example:

Column A Total = 180	Column B Total = 200	Column C Total = 185	Line Total
Random 1	Random 2	Random 3	250
Random 4	Random 5	Random 6	125
Random 7	Random 8	Random 9	60

I can build a formula that allows the columns or the lines to have random values and add to the required total, however I can't arrive to a formula that combines both constraints.

I used this link to define the formula that always adds to the required total.

To allow for a better explanation, see the example of the table above, where the random numbers are now replaced with actual numbers:

Column A Total = 180	Column B Total = 200	Column C Total = 185	Line Total
200	20	30	250
10	80	35	125
5	30	25	60

As you can see, the total in the lines are correct, however the totals of the first column are incorrect and add up to more than what is allowed (215 > 180).

Note that the line values must add up to the total, however the columns must be less or equal, here are the conditions to clarify:

Line Totals
Line1: SUM(Random1, Random2, Random3) = 250
Line2: SUM(Random4, Random5, Random6) = 125
Line3: SUM(Random7, Random8, Random9) = 60

Column Totals
Column1: SUM(Random1, Random4, Random7) <= 180
Column2: SUM(Random2, Random5, Random8) <= 200
Column3: SUM(Random3, Random6, Random9) <= 185

The objective is to achieve it in Excel. Is this possible using formulas only? Or is this an optimization problem for which solver is required?

Answer 1

Here/screenshots refer.

Caveat - your constraints are not entirely feasible - since row total must sum to column totals (by definition), one of the semi-inequalities (<=) must be set equal to the balance of the row totals ('line totals per your Q') over the other tow column totals.

Instead col 3 <=185 I use col 3 = 250+125+60 (row total sum) - col 1 (<=180) - col 2 (<=200); where the latter two (col 1, col2) are simply random numbers up to 180 and 200 resp.

I solve this in a similar way to the soln I provided a moment ago to the link you included in your Q see here.

---

Steps

Step 1

Key equations:

• Random numbers in table generated as follows: =LET(x_,RANDARRAY(3,3,1,1000000,1),x_)
• Random col totals (cols 1 & 2) as follows: =RANDBETWEEN(1,U) where U was upper boundary given for respective columns

(solving on this basis will ensure equality with the column and row totals, simultaneously, cols 1 and 2 are guarenteed to be no greater than the values required per the Q; alternatively you could use randbetween(L,1e6) to ensure these col totals are no smaller than the corresponding cell values for respective cols.. - albeit not the Q)

---

Step 2

• Finds % of cells in relation to overall values from step 1 using E5#/SUM(E5#)

• Rebalance the column and row totals so that they have the same % spread as required/given row/col tots (noting the total value of original random numbes remains unchanged, hence random nature preserved) using =E15:G15*E4:G4/SUM(E4:G4)/(E15:G15/SUM(E15:G15)) for column totals and =H18*H5:H7/SUM(H5:H7) for row totals (i.e. "lines")

Caveat - I'm not entirely sure this steps is even necessary, but it does help to explain the approach...

---

Step 3

• Take cross-product of row and column probabilities / %s to determine joint probabilities for respective cells

---

Step 4

Apply cell % (step 3) to desired col/row totals - Voila!

Using =D27#*G4 in this screenshot.

---