How can I create a square 2-dimensional dynamic array in Excel: e.g. 5x5 top row all 1s, next row one 0 and four 1s, next row two 0s and three 1s etc

Question

I want to use and Excel formula, not VBA, to create an array with the following values: enter image description here The formula must allow the range to grow dynamically as a copy it down horizontally and vertically.

There are a number of solutions using VBA, but I wonder if it can be achieved using hstack etc.

Akhilpramod · Accepted Answer · 2024-04-01 10:13:02Z

0

1.First, in cell A1, enter the following formula:

=IF(COLUMN()-1<=ROW()-1,1,0)

2.Drag this formula across 5 columns (A to E) and down 5 rows. You can do this by selecting cell E1, then dragging the fill handle (the small square at the bottom-right corner of the cell) down to cell E5

answered Apr 1, 2024 at 10:13

Akhilpramod

231 silver badge5 bronze badges

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

Collectives™ on Stack Overflow

How can I create a square 2-dimensional dynamic array in Excel: e.g. 5x5 top row all 1s, next row one 0 and four 1s, next row two 0s and three 1s etc

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related