Fill cells randomly constraining/restricting row and column sum

**juansale** · 11-17-2011, 12:05 AM

I need to fill randomly a set of more than 20 rows and columns with numbers from 0 to 4. However, I need to set a limit to the sum of each line and each column. Lets say, each row has to sum up to a value equal or lower than 4 and each column to a value equal or lower than 6.
The remaining cells could be filled with zeros.

I don't know how to use VBA, macros, etc. I already tried with the Data Validation option but it only lets me set ONE restriction: either constrain the sum of each row or the sum of each column or to allow values from 0 to 4 only. But not the three of them which is what I need.

One example could be:

0 1 2 0 0 0 0 1
0 3 0 1 0 0 0 0
1 0 0 2 0 0 0 0
4 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 1 0 0

Is there a formula/command to make this possible? If so, would it be possible to also set the distribution of the data (i.e Normal)?

Another way of doing this I have thought of is to fill the cells myself, instead of letting Excel do it randmonly. But either way I've got the same issue. I'd want Excel to only let me use values form 0 to 4, so that if I fill the cells of the row and it sums up to 4 or less it'd either stop me or let me fill other cells but changing/adjusting automatically the values of the former ones, so that it will always sums 4 or less. And same thing with the columns.

An example:

0 1 1 0 0 1 0 4 --> The sum is greater than 4, so the former values would be automatically so that the condition of summing up to 4 would be met. This way, the modified row could be etiher:
0 1 1 0 0 1 0 1, or , 0 1 1 0 0 1 0 0

THANKS A LOT!

**NickyC** · 11-17-2011, 01:39 AM

This is easiest to do with a macro I think
does the attached sheet do what you want?

**juansale** · 11-17-2011, 04:15 PM

Hi NickyC. I really appreciate your help!

That's almost what I want. As I said before, I don't know how to use VBA. So even though I tried, I couldn't edit the macro to make it work the way I want.

I think I wasn't clear enough. Then, I'll attach a file showing you what is exactly what I want and hope you can kindly help me.

The main idea is in the Worksheet1, which I named "Basic".

- The cells in blue (B3:P10) must contain random numbers from 0 to 4.
- The cells in red must contain the sum of the values in each row. The total sum for each row cannot be greater than 4.
- The cells in verde deben must contain the sum of the values in each column. The total sum for each column cannot be greater than 28.

There is an example below of one of the sets I need to generate.

The final result I want to get is shown in the Worksheet2 (named "Ideal"). I mean, if it's possible to generate a macro or function to get all the sets filled, meeting all the conditions listed above, it would be great! In such a case the columns named "Total C-D" and "Total J-O" should not be included in the sum (Only the values in blue).

One more thing, just in case that I would want to change one of the values manually, would it be possible that the rest of the row/column would change/adjust the values automatically so that it will always meet the conditions?

Once again, thank you.

**NickyC** · 11-17-2011, 09:13 PM

The probability of one row of 15 random numbers between 0 and 4 summing to 4 or less is 0.0000000127; the probability of 8 rows doing so simultaneously is infinitesimal. Your macro is going to run forever!