Request for Excel Formula: Populate Grid with 0s and 1s, Ensuring Column Sums within Given Range

[yaswanth92]

Hello everyone! I'm looking for an Excel formula to generate a grid with 744 rows and 100 columns, where each cell contains either 0 or 1. The sum of values in each column should always be within a specified range (between floor and ceiling values as input). We can consider floor value as 200 and ceiling value as 280. Thanks!

 

[p45cal]

A formula for one column:

=SORTBY(EXPAND(SEQUENCE(RANDBETWEEN(200,280),,1,0),744,,0),RANDARRAY(744))

Copy across 100 cells.

 

[yaswanth92]

Love it. You're always awesome. Thanks very much mate.

 

[Monty]

Hello everyone! I'm looking for an Excel formula to generate a grid with 744 rows and 100 columns, where each cell contains either 0 or 1. The sum of values in each column should always be within a specified range (between floor and ceiling values as input). We can consider floor value as 200 and ceiling value as 280.

Hello...
You can use the following Excel formula to generate a grid with the specified conditions:

1. In cell A1, enter the formula:

=IF(RAND() < (280-SUM($A$1:A1))/(744-ROW(A1)+1), 1, 0)

2. Copy this formula across 100 columns (from B1 to CV1).

3. Copy the entire row down to row 744.

This formula utilizes the `RAND()` function to generate random values between 0 and 1. Based on the specified floor and ceiling values, it assigns 1 or 0 to each cell in the grid, ensuring the sum of values in each column falls within the given range.