You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Using the new Transpose function

It's great to see the new Transpose function, as well as many other improvements in Numbers 09.

I am trying to transpose a 3 x 3 array. Perhaps I am thinking too Excel, but I put the transpose formula in a destination cell or even a range of cells, and it seems to do nothing. In Excel, the result is a 3x3 array, but I'm not at all getting how use this functino in Numbers. Must I also apply an index function along with it in each cell? Seems odd if this is so, but any insight is greatly appreciated.

MacBook Pro, Mac OS X (10.5.6)

Posted on Jan 12, 2009 5:11 PM

Top-ranking reply

KOENIG Yvan

Level 8

41,819 points

Posted on Jan 13, 2009 1:26 AM

It seems that you failed to read the Formulas and Functions User Guide which describes perfectly the way to use this function (pages 209 & 210 in English version).

TRANSPOSE
+The transpose function returns a vertical range of cells as a horizontal range of cells, or vice versa.+
TRANSPOSE(range-array)
+• range-array: The collection containing the values to be transposed. range-array is a collection containing any type of value.+
+Usage Notes+
+• TRANSPOSE returns an array containing the transposed values. This array will+
+contain a number of rows equal to the number of columns in the original range and a number of columns equal to the number of rows in the original range. _The values in this array can be determined (“read”) using the INDEX function._ +

Examples
+Given the following table:+
+row/column A B C D E+
+1 5 15 10 9 7+
+2 11 96 29 11 23+
+3 37 56 23 1 12+

+=INDEX(TRANSPOSE($A$1:$E$3),1,1) returns 5, the value in row 1. column 1 of the transposed range+
+(was row 1, column A, of the original array).+
+=INDEX(TRANSPOSE($A$1:$E$3),1,2) returns 11, the value in row 1, column 2 of the transposed range+
+(was row 2, column A, of the original range).+
+=INDEX(TRANSPOSE($A$1:$E$3),1,3) returns 37, the value in row 1, column 3 of the transposed range+
+(was row 3, column A, of the original range).+
+=INDEX(TRANSPOSE($A$1:$E$3),2,1 returns 15, the value in row 2, column 1 of the transposed range+
+(was row 1, column 2, of the original range).+
+=INDEX(TRANSPOSE($A$1:$E$3),3,2) returns 29, the value in row 3, column 2 of the transposed range+
+(was row 2, column C, of the original range).+
+=INDEX(TRANSPOSE($A$1:$E$3),4,3) returns 1, the value in row 4, column 3 of the transposed range+
+(was row 3, column D, of the original range).+

Yvan KOENIG (from FRANCE mardi 13 janvier 2009 10:26:36)

View in context

17 replies

Feb 24, 2009 9:03 PM in response to KOENIG Yvan

Mr. Koenig:
You are a tireless helper with a lot of patience for the rest of us. Thank you for your investment in helping us use Numbers. I just started using Numbers today, being a big Appleworks fan since ClarisWorks3, with a working knowledge of Excel. The transpose function has sent me into this forum, and I have read many of your posts in my research.

Maybe this explanation might help someone who is having trouble with the syntax in the guide of TRANSPOSE. What was in the guide was really scary for me.

As to the TRANSPOSE function, it seems like it does not do what we all were hoping. I shortened up your shortcut a little, and simply used the INDEX function, without the TRANSPOSE function. Let's say you have a 6 row by 2 column series, A1:F2, and your target is the series C11:D16. If you put a reference index series in a A11 to A16 of integers 1-6, then in the row B10 to C10 of 1-2, then C11 =INDEX($A$1:$F$2,B$10,$A11). Fill that down and right in all the cells, and you have a transposed array.

I didn't follow the row()-row(1) that you had, which is probably even easier, but this one works and it is pretty easy, flexible and independent of array size (providing you adjust the indices, of course).
RC<

KOENIG Yvan

Level 8

41,819 points

Feb 25, 2009 5:46 AM in response to rcarbaugh

If you are able to build the formulas by yourself, it's perfect.
The script was written for those which aren't or which have no time to spent with that.
When you write the formulas by yourself, you may work with references like $B12.
When writing a script, it is much more efficient to use formulas which doesn't use this kind of reference because it requires that the script calculates every reference one by one.
With my formula, it builds one formula and apply it to every cells in the range.

Let me add that formulas using references of the kind B12 are not robust enough to resist to a sort while mine is.

But I repeat, nobody is forced to use available tools.
The number of questions asked here when the response is easily available in the Help is a perfect prooh of this statement.

Yvan KOENIG (from FRANCE mercredi 25 février 2009 14:46:31)

Using the new Transpose function