How do I add a formula to full column in Numbers?

These are the results I get with your formula* using 50 as the "fixed number".

*Numbers can recognize when "A" refers to 'all of column A' and when it refers (as here) to 'the cell in this row of column A.'

I don't see any issue with the results.

Regards,

Barry

Copy and paste into your reply, the formula in B1, and the formula in B4 after filling it down using the fill handle (yellow dot). A screen shot showing the results you are getting would also help.

Regards,

Barry

According to your initial description: "The formula I used for the first value is A1 / fixed number * 100 - 100."

In the formula I see above, A1 has been replaced by G3, which is fine, and "fixed number" has been replaced by H2. The result of this formula is shown (formatted to show three places after the decimal) in H3. (Ignore the current values in H4 and below—they have been copied directly from your image above.

"I'm dragging the yellow dot at the bottom of B1 down the column, and the answers are way off."

As you fill this formula down the column from its current location, both G3 and H2 will increment by 1 for each row the formula moves down:

H4: =G4/H3*100-100

H5: =G5/H4*100-100

…

H11: =G11/H10*100-100

With the results shown below:

Are those the results you are expecting? Or are they the "answers (that) are way off"?

If the latter, is the "fixed number" in your initial description the 761.290 in cell H2? Should that be the number that is the divisor in each of the formulas?

If so, make this simple revision in the initial formula in H3:

H3: =G3/H$2*100-100

Then fill down.

The $ is the absolute reference operator. When applied as shown, it locks the row part of H2 to row 2 as the formula is filled down column H. With this formula in place, and filled down to H11, these results are shown:

Regards,

Barry

Hi cc,

Here's an example showing a sort in ascending order in column A and a sort in descending order in column B, both in Table 2. The original 'data' is in Table 1 on the left. I've left the error triangles in the bottom row (along with the error message). I used these as a convenient means of seeing that the end of the data had been reached. Just delete the last line when done.

Note that I've hidden lines 13-112 of Table 2 for an easier fit into a screen shot.

Formulas are quite similar.

A2: =SMALL(Table 1 :: $A$2:$L$11,ROW()-1)

B2: =LARGE(Table 1 :: $A$2:$L$11,ROW()-1)

Ascending sort: use SMALL. Descending sort: Use LARGE.

Fill choice down the column until you start getting error triangles (see error message above), then back up to last row containing data.

Regards,

Barry

Hi cc,

"I do understand how to sort the numbers in ascending/descending order once they're all in the same column."

No need to. The two formulas in my post transfer the data in order, either ascending (if you use SMALL) or descending (if you use LARGE). Both formulas used the same data. No sorting was done after the transfer.

"I only get the error messages. I can't get the numbers to appear outside of their original cell. Does that make sense?"

No. but it might with a screen shot of the data and the formula you are using. A copy of "the error message(s)" might also provide some clues.

Your description said "I'm working with 12 columns… I want to combine the data from all 12 columns into 1 master column"

My assumption was that these were 12 contiguous columns, hence the data sample using a 12 column block. If the 12 columns are separated, you may not be able to use the method demonstrated. Numbers 3 does include use of a UNION RANGE, which may serve well in this context, but it's not something I'm familiar with.

The devil's in the details. Please provide some.

Regards,

Barry

Hi cc,

When you copy a cell, you copy the formula in the cell, the last result of its calculation, and any formatting that has been applied to the cell.

When you simply Paste, Numbers pastes everything that was copied. The formulas immediately try to create a new result, but they are looking for cells with the same location relative to their new location as the previous cells were relative to their prior location, and in this case, at least one of those relative locations is to a column that does not exist in the table they're currently in. Hence the invalid reference error.

You can't change what gets copied (except in the case of pressing option-command-C to copy only the format applied to a cell), but you can control what gets pasted. To paste just the results of the most recent calculation, go Edit > Paste Formula Results.

BTW: You want to sort these values, so you would not want to "copy them as just text." When numbers are copied (and pasted) as Text, they no longer sort like numbers. They are text, and are sorted as text. As Text, these numbers are correctly sorted in ascending order: 0, 1, 10, 100, 11, 19, 190, 199, 2, 3, 33, 4…

"In this example, H2 was the absolute operator for column H, and P1 was the operator for column P."

Actually, the value in H2 was the "fixed number" in your initial description of the formula used in column H. The 'absolute (reference) operator' is the $ symbol, which kept the formula referencing cell H2 as it was filled down the column.

"What I'd like to do is to take all the numbers in column H and all the numbers in column P, copy them into a new column, with one on top of the other, no spaces in between, and then sort them."

Not a problem.

Select the numbers you want to copy from column H, Copy.

Click on the first non-header cell in the single column* you want them in. Go Edit > Pasrte formula results.

Select the numbers you want to copy from column P. Copy.

Click once on the first empty cell in the single column you want the in. Go Edit > Paste formula results.

Repeat for any other columns of numbers you want to add to the single column.

With one or more cells in the single column selected, click on the triangle in the column's reference tab, and choose Sort Descending.

Done.

*Numbers does not sort single columns within a larger table. The 'single column' should be in its own one column table if you do not wish to change the order of other columns in the same table.

Regrds,

Barry

"I thought you could just cut and paste data from one place to another, as you can in Excel."

I did a little exploring and testing with LibreOffice to refrsh my memory of Excel's behaviour. Copy/Paste and Cut/Paste are pretty similar in Numbers, Excel, and the open source applications mimicking Excel. Here's an example showing the same behaviour (error nessages) as your Numbers example:

Column E contains the formula =ROW(), which returns the row number of the cell containing the formula.

Column H contains the formula =E1, filled down. The formula returns the value in the cell in the same row of Column E, three columns to the left of itself.

The cells in column H indicated be the dashed line were copied.

Cell C1 was selected, and the content of the clipboard was pasted into column C, with the results shown.

#REF! is the windows/LibreOffice version of the same error you got in Numbers—an Invalid reference error, caused by the formula pasted into column C referencing the (non-existing) cells in the third column to the left of C.

Just as in Numbers, you can paste the results without the formulas, but just as in Numbers, you must make a choice to do so. In Excel and similar applications, that's a choice to Paste Special, followed by choosing which type of 'special' you want.

Regrds,

Barry