10808 Views
10 Replies
Latest reply:
Jan 7, 2014 4:42 PM by
Badunit

Barry Jan 26, 2011 11:04 AM
Re: Repeating a formula down a spreadsheet in response to cairnterriercairnterrier wrote:
Okay, be gentle. Am feeling very fragile and very fed up of this now. It's teddies out of the pram time.
Love the metaphor!
Here are two ways to accomplish this:
1.
Enter the formula in G10.
Click on G10 to (re)select it.
Grab the control (small circle at the bottom right of the selection border) and drag it down as many rows as you wish to have the formula. Numbers will adjust the cell reference to fit.
2.
Enter the formula in G10.
Select G10 and all of the cells below in which you wish to use the formula.
Go to the Insert menu and choose Fill > Fill Down.
You could also replace your formula with this version, using the multiplication operator:
=F*0.20
Numbers interprets cell references using only the column letter as meaning either the cell in that column on the same row as the cell containing the formula, OR the range of all cells in that column, depending which interpretation is appropriate to the formula.
Regards,
Barry
PS: An excellent place to have started your search would have been Chapter 4, Working with Table Cells, in the Numbers '09 User Guide. The Table of contents would lead you to the article "Autofilling Table Cells" on page 78.
You can download the Numbers '09 User Guide (and the iWork Formulas and Functions User Guide—a reference for all of the functions supported by Numbers, with syntax and descriptions) through the Help menu in Numbers.
B 
cairnterrier Jan 26, 2011 11:09 AM
Re: Repeating a formula down a spreadsheet in response to cairnterrierThank you, I can now think straight again! Thanks for the tip about autofilling as well  I've been going mad going around and around the topics of copying formulae between cells. GRRRRRRRR!!!!!
Hope you have a good evening 
KOENIG Yvan Jan 26, 2011 11:27 AM
Re: Repeating a formula down a spreadsheet in response to BarryThere is a third track.
Given a table with header row(s),
remove every 'standard' rows minus one.
Build your formulas in this row.
Then drag the handle allowing the creation of new rows.
They will receive automatically clones of the original formulas.
Yvan KOENIG (VALLAURIS, France) mercredi 26 janvier 2011 20:27:56 
Bjuell Jan 27, 2011 7:51 AM
Re: Repeating a formula down a spreadsheet in response to cairnterrierI am confused by the answers so far. In Excel, if I want to refer in my formula to a fixed number (cell), and not have that cell address change as i move to other addresses I am inserting the formula into, I simple hit the F4 key following that address entry in my base formula. This is used when I want to be able to change assumptions in a column of several numbers, eg changing interest rate assumptions, from 5% to 6%.
Bjuell 
KOENIG Yvan Jan 27, 2011 8:31 AM
Re: Repeating a formula down a spreadsheet in response to BjuellOpen *_iWork Formulas and Functions User Guide_*
and search for *_Absolute and Relative Cell References_*
Yvan KOENIG (VALLAURIS, France) jeudi 27 janvier 2011 17:31:47 
Barry Jan 28, 2011 12:47 AM
Re: Repeating a formula down a spreadsheet in response to BjuellBjuell wrote:
I am confused by the answers so far... I want to refer in my formula to a fixed number (cell), and not have that cell address change as i move to other addresses
Hi Bjuell.
Then you should have included that in the original question.
If the percentage rate is in G1, for example:
Revise the formulas I suggested earlier:
=F2*0.20
=F*0.20
by replacing "0.20" with a fixed reference to G1:
=F2*$G$1
=F*$G$1
Regards,
Barry 
Bjuell Jan 28, 2011 3:43 PM
Re: Repeating a formula down a spreadsheet in response to BarrySo, is there a shortcut (like F4 in Excel) or do I have to install $ twice?
Bjuell 
Jerrold Green1 Jan 28, 2011 7:59 PM
Re: Repeating a formula down a spreadsheet in response to BjuellNo, not like Excel. You hover over the cell reference in the formula and click the triangle on the right end of the cell reference and select the type of reference you prefer.
Jerry 
RPOS Jan 7, 2014 4:18 PM
Re: Repeating a formula down a spreadsheet in response to Bjuell 
Badunit Jan 7, 2014 4:42 PM
Re: Repeating a formula down a spreadsheet in response to RPOSA thread from the dead.