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?

I would set this up in the Solver. Check on here, I did answer one similar. — Solar Mike
– Solar Mike, Commented Nov 6, 2022 at 20:47
Can you post the link? I've been able to it in solver too, but it is not optimised, since I would need to run many solvers, each time I want to update the conditions. — Bruno
– Bruno, Commented Nov 6, 2022 at 23:29
It seems to be more generic than Excel Sudoku generator, but maybe you can use the same idea. The video implements a solution in VBA. — David Leal
– David Leal, Commented Nov 7, 2022 at 1:01

JB-007 · Accepted Answer · 2022-11-07 02:37:02Z

4

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.

edited Nov 7, 2022 at 2:37

answered Nov 7, 2022 at 2:30

JB-007

2,5891 gold badge9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bruno Over a year ago

This answer is amazing, sorry for the delay, I was hoping to test it out in my much bigger file, this week but couldn't find the time. I will do so next week and give you feedback. Anyway, it seems precisely what I was looking for, thank you for your help!

JB-007 Over a year ago

np - hope larger file works out for you! Br, J

Collectives™ on Stack Overflow

Excel Random Values with 2 Constraints

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related