creating a diagonal Matrix

[karraj]

Is there a easy way to create a diagonal matrix from single column of data I want!
for example, cell a1 to cell a1
a2 to b2
a3 to c3
etc etc....

 

[Mark W.]

For {1;2;3;4;5} in cells A1:A5 enter the formula, =IF(ROW()=COLUMN(),INDIRECT("A"&COLUMN()),""), into cell B1, Fill Right to E1, and Fill Down to E5.

Or, select cells B2:E5 and enter the array formula solution...

{=IF(A2:A5=TRANSPOSE(A2:A5),A2:A5,"")}

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.
This message was edited by Mark W. on 2002-03-14 14:50