Forum Discussion
Shaping Dynamic Arrays: Moving beyond Transpose
If you have a triangular table of a certain shape, can it be reshaped so that rows going across become columns going down but with padding at the beginning?
= LET(
extended, EXPAND(data,, 12,""),
DROP(WRAPCOLS(TOCOL(extended), 11),-5,-1)
)This lining up of a matrix reverse diagonal is characteristic of convolutions and depreciation calculation. Array shaping is not just decoration, it can be a key element of the calculation.
Nice one Peter! Very clever. ;) Naturally, it could be made dynamic by defining n, COLUMNS(data), then replacing 12 with n*2, 11 with n*2-1, and -5 with 1-n, but it's perfect for the described scenario, nonetheless. The only potential drawback I see is limitations... since it's expanding the width of the array, TOCOL will only handle 724x724 (expanded to 724x1448) as opposed to 1024x1024, but that's likely a non-issue. Cheers!