Q: transpose columns and rows in numbers

Tried that. Still returning "The formula contains a number outside the valid range." Returns the same message even when I change the row to refer to one that I entered in manually, rather than being populated with the "average" formula.

How about a screenshot showing both tables and relevant row numbers and the formula?  (I can't see the row numbers of the first table in the last screenshot, and the formula definitely does not include the $).

SG

Let me know what else! Thanks!

You're hiding rows 2-5 of the table!  That makes it very hard to see what is going on.

Also, from your screenshot I can only see nine values in row 7 of the last table (the data input table), yet you have more than 9 values in column H of the first table.  When the formula "runs out" of values in row 7 it complains you are outside the valid range.

SG

A couple things:

First, I figured it out! My problem was that I was not making the target match the original (i.e., to make this formula work, if the data input table is 18 columns wide, the dataframe has to be 18 rows long).

Second, in the picture above, I had entered in the first set of values manually (rows 2–19) and was trying out the formula on a new set (Intermedium), which started down low enough that I had to scroll down a bit to show it. The first two pictures are of the same table, just scrolled differently.

What I did to actually make this work, just to move the values that I've already collected into the dataframe, was create a temporary table on the same sheet with a matching number of rows as there are columns in the original. However, this workaround doesn't solve my issue with wanting to create a master sheet that I can use to collect data later. I'm still unable to enter a particular formula into a cell, then drag that formula down to apply to multiple rows in a column, and have those formulae refer back to a row's worth of columns in a different table. Any thoughts?

Thanks again! This will at least get me through the next few days!

andy563 wrote:

 

still unable to enter a particular formula into a cell, then drag that formula down to apply to multiple rows in a column, and have those formulae refer back to a row's worth of columns in a different table. Any thoughts?

 

Isn't that *exactly* what my example upthread does?

SG

Yes, but with the workaround, it still requires making a custom-sized table to populate with the formulae, rather than having the formulae be integrated into the larger dataframe. Unless I'm completely missing something, which is entirely possible.

Not sure what you mean by "custom-sized table" and "larger dataframe."   In any case if your input is horizontal (in a row) and your output (in the "data frame" column) is vertical, then you can use TRANSPOSE in the output table as demonstrated.

SG

I mean that, in order to get the formula to work, I have to create a temporary table of the inverted proportions (e.g., 15x1) of the original input (e.g., 1x15) that I can copy and paste the values from into the master list (i.e., the data frame), in order to export in a format that is readable by the statistics package, R. I think I may have figured out an additional workaround to get this to work. I'll try and create a new sheet and fill it with the temporary tables (i.e., the 15x1 I mentioned above), then tell the master dataframe to go look for them there, instead of in the input table. I'll let you know if it works.

Still don't understand why you think you need "temporary tables."  If you've set things up properly, the horizontally arranged values in your input table are placed automatically by INDEX(TRANSPOSE) in vertically arranged cells in your output ("master dataframe"??) table.  It's very hard to tell from your screenshots what your actual setup is. But in general,  time spent setting things up properly so you don't need workarounds and temporary tables and such is time well spent.

SG

Ok, let me describe my setup. I've got several sheets within a single file, for the different regions of the hand (wrist bones, palm bones, finger bones), with a right and a left sheet for each region. These are arranged for taking data, i.e., with a single bone occupying three rows: two for entering measurements, and a third with a formula for finding the average of the two scores above. Then, I have a separate master sheet, in a data frame format, rather than a table. Instead of each bone having its own row with multiple measurements, each measurement gets its own row, with identifying information preceding it (i.e., genus and species name, left or right, bone name, measurement taken, etc.). This is the format that it needs to be set up in for R to read, so that I can export as a CSV and actually use the data in analyses.

I've tried pasting the INDEX(TRANSPOSE) formula into the master sheet, but I haven't figured out how to not get a "data outside of valid range" type message, like in the photos above. The only way I've managed to make this work, without getting the red triangle error, is to put the formula into a temporary table with the same (inverted) dimensions of the input row. So, unless I'm missing something (which is still possible), I'll need to create a new sheet, full of temporary tables of the proper dimensions that I can put the index formula in, then tell the master sheet to look at the temporary tables for the data, so that I'll be able to drag down the yellow circle, since the transposed data will be in column format to begin with.

This does seem cumbersome, but I just couldn't figure out how to get the formula to work in the master sheet. I could still be missing something.

It's a little hard to follow your description, partly because of nomenclature issues. Numbers has "documents", and within documents it has "sheets", and on each sheet (marked by tabs at the top) it has "tables."  Formulas and values can be in tables (not sheets).   You should be able to have one formula in your output table that places in one column values that are horizontal in the input table. 

A picture is worth a thousand words.

SG

Hi Andy,

Numbers is well suited to creating a table that is similar to a dataframe in R. Column headings in row 1 and data for each attribute down each column. I am not sure that R will recognise the file (document) format of Numbers. Also, your raw data tables and intermediate calculations (transpose and  average) make for a complex document. Aim for a simple final table.

When your final table is ready, select the whole table and Copy.

Open a new blank document in TextEdit and Paste. Save.

That will create a tab-delimited plain text file that R will recognise.

Regards,

Ian.

"Posting feedback to Apple is a waste of time Wayne."

I would have to disagree on this point. Many things we as users asked for have shown up, they do read the suggestions and implement many. We didnt have Hide columns on ipad forever, now we do as one obvious example i wanted forever. It just takes enough of us to go there and make the suggestion. Its just like voting, if you dont vote/suggest you cant really complain. Yours could be the one vote/suggestion that put that above another in the queue of things to include in the next updates.

Jason

I too often need a transpose function to rearrange data supplied by outside vendors, e.g. Gas & Electric Company.  Darned if I didn't find it in Numbers!!!

Using Numbers version 3.5, select your dataset, then go to: Table>Transpose Rows & Columns.

This turned my whole "vertical" table into a "horizontal" table, and I then had to cut/paste the columns I wanted to stack to get my final result, but this was far, far easier for my simple brain than using INDEX and TRANSPOSE functions.