• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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!
 
A formula for one column:
Code:
=SORTBY(EXPAND(SEQUENCE(RANDBETWEEN(200,280),,1,0),744,,0),RANDARRAY(744))
Copy across 100 cells.
 
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.
 
Back
Top