susannePA wrote: Also - doesn't the column designation refer to the whole column and not just the cell it is in?
Numbers will interpret partial references as appropriate.
R: requires a specific, single search value, so this will be interpreted as a reference to Rn, where n is the same row as the formula occupies.
F: requires a range of cells, so F will be interpreted as a reference to all cells in column F.
Q: requires a range of cells (equal to the range for the second argument, F, so Q will be interpreted as a reference to all cells in column Q.
Yes, but in order to "place that (result) in the column with the formula - all the way down the entire column," each cell in "the entire column" must contain the formula. Formulas cannot "place" values in cells other than their own; formulas can "retrieve" values from other cells, do calculations with those formulas, and place the result in the cell containing that iteration of that formula. In my earlier example, each cell in column C of the middle table contains the formula: =LOOKUP(A,Data :: $A,Data :: $B)
In C2, the formula, stated in English, says, "look for the value in A2 (of this table); look for a 'close match' in column A of the table 'Data'. When you find the largest value less than or equal to it; return the value from the same row of column B of the table 'Data'."
In C9, the same formula, stated in English, says, "look for the value in A9 (of this table); look for a 'close match' in column A of the table 'Data'. When you find the largest value less than or equal to it; return the value from the same row of column B of the table 'Data'."
Oops! Jumped directly to page 2 and missed this one. Sorry.
Is there a shorthand way to incidate the entire column as this sheet table has over 4,000 rows?
No, but there is a shortcut to selecting the whole column (minus the rows above the first instance of the formula) before filling down. Example is for column C, with the formula entered into C2:
- Click on any cell to activate the table and display the column and row reference tabs.
- Click on the reference tab for column C to select all of column C.
- Command-Click on C1 to remove it from the selection.
You now have C2 and all of the cells below it in column C selected.
- Go Insert (menu) > Fill > Fill Down.
Numbers will fill the formula into all of the selected cells.
Another way to fill an entire column is to Paste to it.
After entering your expression in the first body row, and hitting Enter or Tab, then click back on the cell with the formula in it and Command-C to copy it to the clipboard. Now Select the entire range that you want to extend the formula to and Command-V.
Probably the worst way to do this is by dragging the fill handle. It's fussy and it can be slow.