Creating array (no VBA) that is NxN with '1's top right to bottom left, rest zero

[Alan]

Hi All,

I need to create an array (matrix) that is N x N elements (square),
containing '1's from the top right hand corner to the bottom left hand
corner only. The rest of the array would be zeros.

I would like to find a way of doing this with a worksheet function (or
functions) only - no VBA to be involved.

Obviously I can manually create this for any given value of N (say, N =
4):

={0,0,0,1;0,0,1,0;0,1,0,0;1,0,0,0}

But how can I set this up so that I can enter N in a cell, and get that
array returned for use in another formula?

Thanks in advance for any help you can give.

If you need clarification, just ask.

Alan.

 

[J.E. McGimpsey]

I'm sure there's a better way, but

--(ROW(INDIRECT("1:" & A1))+COLUMN(INDIRECT("1:" & A1))=(A1+1))

(array-entered) seems to work

 

[Alan]

Hi,

Thanks for your reply.

*I'm sure there's a better way, but

--(ROW(INDIRECT("1:" & A1))+COLUMN(INDIRECT("1:" & A1))=(A1+1))

(array-entered) seems to work

*

I tried this, but the COLUMN section seems to produce a different
result to what I need.

It returns the column number of each column in the range 1:2 (in other
words each of the columns A:IV or {1,2,...,255,256})

However, I made a slight change as follows and it now seems to work
fine:

{=(ROW(INDIRECT("1:"&A1))+TRANSPOSE(ROW(INDIRECT("1:"&A1)))=(A1+1))*1}

Thank you very much for pointing me in the right direction.

Alan.