creating a diagonal Matrix

karraj · Mar 14th, 2002, 10:45 AM

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. · Mar 14th, 2002, 10:56 AM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 10:45 AM	#1
karraj Board Regular Join Date: Mar 2002 Posts: 76	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....

Mar 14th, 2002, 10:56 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]