Fill down in numbers

[tony873004]

Hi. I imported data into numbers. The first column contains time and the second contains distance in meters. I inserted a new column in between them. I would like it to contain distance in centimeters. So I type =c2*100. Now I want to fill down so every row has this formula. I found I can drag the gold box to the bottom of the screen. But the bottom of the screen is a long way away. There is lots of data and I don't want it to take an hour. Is there a way I can auto fill the entire column? In Excel you just highlight the cell you want to fill down with and double click the little black box on the cells bottom right corner.

Thanks!

 

[dougrogers]

The last cell you drag to remembers the formula. Just drag and fill again from there. Won't that work?

 

[tony873004]

Yes, dragging works. But I can only drag to the last cell visible on the screen. My spreadsheet has over 1000 rows of data. So drag, scroll, highlight, drag, scroll, repeat 50 times... takes forever. Isn't there a simple way to make it go the last row for you automatically, like they have in Excel? It would be a HUGE time-saver.

 

[twerppoet]

Yes, dragging works. But I can only drag to the last cell visible on the screen. My spreadsheet has over 1000 rows of data. So drag, scroll, highlight, drag, scroll, repeat 50 times... takes forever. Isn't there a simple way to make it go the last row for you automatically, like they have in Excel? It would be a HUGE time-saver.

If you approach the edge of the screen slowly, at one point it will start scrolling down and continue the Fill. Stop your finger there and wait for it to get to the bottom of the table. The scrolling is easy to miss. Just keep an eye on movement, whether it looks like scrolling kind movement or not.

Might take a couple tries, but even if it doesn't work every time, I'm sure you can reduce the number of time you need to go through the motions.

 

[tony873004]

If you approach the edge of the screen slowly, at one point it will start scrolling down and continue the Fill. Stop your finger there and wait for it to get to the bottom of the table. The scrolling is easy to miss. Just keep an eye on movement, whether it looks like scrolling kind movement or not.

Might take a couple tries, but even if it doesn't work every time, I'm sure you can reduce the number of time you need to go through the motions.

Thanks for the reply!
I discovered that method. The problem is that it takes too long.
I teach high school Physics, and we take data from sensors, such a force sensors. These sensors can feed you thousands of rounds of data. Keeping your finger on the bottom of the screen while it scrolls at a maximum rate of about 4 rows per second can still take upwards of 20-30 minutes. I can't have half the lab period dedicated to holding your finger at the bottom of the iPad screen. Excel simply lets you double-click the bottom right corner of the highlighted box, and it fills down to the bottom for you, instantly. I was hoping Numbers had a similar feature, since my school heavily encourages using the iPad, which each student is required to own. But if Numbers doesn't have this basic feature, I'm going to have to find a plan B Thanks again for your reply!

 

[dougrogers]

Are you saying you're limited, bound, to inputting thousands of rows of data manually?

 

[twerppoet]

Thanks for the reply!
I discovered that method. The problem is that it takes too long.
I teach high school Physics, and we take data from sensors, such a force sensors. These sensors can feed you thousands of rounds of data. Keeping your finger on the bottom of the screen while it scrolls at a maximum rate of about 4 rows per second can still take upwards of 20-30 minutes. I can't have half the lab period dedicated to holding your finger at the bottom of the iPad screen. Excel simply lets you double-click the bottom right corner of the highlighted box, and it fills down to the bottom for you, instantly. I was hoping Numbers had a similar feature, since my school heavily encourages using the iPad, which each student is required to own. But if Numbers doesn't have this basic feature, I'm going to have to find a plan B Thanks again for your reply!

That's too bad. Numbers is a pretty good personal and even small business spreadsheet, but it's not the number cruncher to handle super sized tables; especially the kind that science experiments are likely produce. The hardware can even be a bit slow for this. My iPad 3 can get bogged down scrolling super sized tables. I imagine the iPad 4th gen is better.

 

[twerppoet]

Are you saying you're limited, bound, to inputting thousands of rows of data manually?

It's not Excel. It's not even the OS X version. If you need to enter and track that kind of information you need to get a tool designed to do the job. Numbers on the iPad is pretty good as a personal or even small business spreadsheet. It was never meant to take the place of corporate or science apps.

There are iPad solutions for this kind of thing out there, but they generally involve a central database and internet connectivity. And more money, of course. FileMaker Pro, for instance.

 

[dougrogers]

You're suggesting what I was approaching. Numbers, obviously, isn't the proper tool for the job. I thought there might be some other software solution, app, or input device. Maybe a laptop running Numbers? Or Excel? Obviously some other database, not a spreadsheet. Some solution capable of taking measurement data directly from some input device. Money of course. That's the answer.

 

[twerppoet]

The only way I can think of to avoid adding data to Numbers manually, is to import it. You can import .csv files. Actually, this works well for science applications. Most science instruments capture data as a text file with some kind of delimiter; coma being the most common (pun sort of intended).

So it's not impossible to enter large amounts of data into Numbers, as long as you are willing to import it as a new spreadsheet, or copy&paste if from a comma delimited text file. The table will expand to hold the data. That still does not address the iPad being underpowered for huge data sets, but it's hard to tell how much impact a simple calculation will have without trying it.

Which gives me an idea!

Off to test, and if it works, post to the OP.

 

[twerppoet]

Thanks for the reply!
I discovered that method. The problem is that it takes too long.

I found something a bit faster.

Create your formula in the first cell. Instead of using the Fill feature, select the cell and copy it.

Select as many cells in the column as you can. You can get quite a few easily enough if you zoom out the table. I got about 80 without making it too hard to select cells. If you're willing to scroll up and down a bit to do the selection you should be able to get a nice even 100.

Paste the formula. You now have 100 cells with the correct formula.

Being careful not to un-select, tap the selected cells again and choose Copy.

I bet you know where I'm going with this.

Select the last cell without a formula, Paste the formula. Now you have 200 cells.

It took me about a minute to do 1000 cells, once I had the steps down.

You could do it faster, if you went back and grabbed a larger section of cells to copy each time, until it became unwieldy.



If you start with a new table, no data to worry about, you can select the entire column to copy (once it's filled with the existing cells), then paste into the last cell. By going back to the top and selecting and copying the entire column a few times you can get a truely impressive number of cells created with each pass. The old trick of doubling the number each time only has to be repeated about 8 times to exceed 1000 cells if you start with 10.

This doesn't answer the question of how well the iPad will handle such a large spreadsheet, but the only way to find that out is to try.

Good luck.

 

[twerppoet]

Thanks for the reply!
I discovered that method. The problem is that it takes too long.

I found something a bit faster.

Create your formula in the first cell. Instead of using the Fill feature, select the cell and copy it.

Select as many cells in the column as you can. You can get quite a few easily enough if you zoom out the table. I got about 80 without making it too hard to select cells. If you're willing to scroll up and down a bit to do the selection you should be able to get a nice even 100.

Paste the formula. You now have 100 cells with the correct formula.

Being careful not to un-select, tap the selected cells again and choose Copy.

I bet you know where I'm going with this.

Select the last cell without a formula, Paste the formula. Now you have 200 cells.

It took me about a minutes to do 1000 cells.

You could do it faster, if you went back and grabbed a larger section of cells to copy each time, until it became unwieldy.



If you start with a new table, no data to worry about, you can select the entire column to copy (once it's filled with the existing cells), then paste into the last cell. By going back to the top and selecting and copying the entire column a few times you can get a truely impressive number of cells created with each pass. The old trick of doubling the number each time only has to be repeated about 8 times to exceed 1000 cells if you start with 10.

This doesn't answer the question of how well the iPad will handle such a large spreadsheet, but the only way to find that out is to try.

Good luck.



Edit: A little more playing around and I'm not certain the methods described (except the doubling one) are much faster than zooming all the way out to do Fills, starting at the top of the screen and going to the bottom each time.

 

[tony873004]

It's not that Numbers isn't capable of handling this amount of data. Numbers works just fine for what I'm trying to do as long as I manually spend 20 minutes filling down the column. I just can't imagine that the programmers didn't include an automated way of doing this. Even Google spreadsheets has this feature.

 

[twerppoet]

!!!

I think I just stumbled onto how to do it.

1. Enter the formula into the first cell, then select and and copy that cell.
2. Tap on the column selection bar, selecting the entire column.
3. Double tap on the column selection bar. This appears to select all the cells in the column (minus the header and footer cells).
4. Tap on the selected cells.
5. Select Paste then Paste Formulas from the popup menu.


I can create a series of screenshots if it's necessary to make it clearer what I'm talking about.


Unessential blathering:

I was playing around trying to figure out how I might create an entire column withe formulas, when I accidentally double tapped the header (after selecting the column). It changed to selecting all the cells under the header.

Annoyed I canceled and started over.

A couple moments later I realized what I had just seen. Tried again, and sure enough I had selected every cell in the column, all the way to the bottom, off screen. No menu though, so I tapped on the selected cells, and just like when manually selecting a range of cells the menu popped up again.

From that point it was just a mater of going through pretty much the same motions as before, but being able to do it all in one shot.