Repeating a formula down a spreadsheet

Question

Level 1

4 points

Repeating a formula down a spreadsheet

Okay, be gentle. Am feeling very fragile and very fed up of this now. It's teddies out of the pram time.

I'm trying to work out how to repeat a formula going down a spreadsheet. So in column F there's a value which changes row by row but I always want to get 20% of that number to put in column G. So for cell G10 for example, I would want the formula =PRODUCT(F10,0.2). In cell G11, I want =PRODUCT(F11,0.2)

Is there a way of getting the formula to repeat automatically row by row without having to type it in again and change the cell reference each time? I realise this is probably a very, very simple question looking at the other topics but for the sake of my health, please can you help? Thanks

Mac, Mac OS X (10.6.6)

Posted on Jan 26, 2011 10:38 AM

Reply

Answer 1

Best reply

Barry

Level 9

59,861 points

Jan 26, 2011 11:04 AM in response to cairnterrier

cairnterrier 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

Reply

Answer 2

cairnterrier Author

Level 1

4 points

Jan 26, 2011 11:09 AM in response to cairnterrier

Thank 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

Reply

Answer 3

KOENIG Yvan

Level 8

41,807 points

Jan 26, 2011 11:27 AM in response to Barry

There 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

Reply

Answer 4

Bjuell

Level 1

0 points

Jan 27, 2011 7:51 AM in response to cairnterrier

I 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

Reply

Answer 5

KOENIG Yvan

Level 8

41,807 points

Jan 27, 2011 8:31 AM in response to Bjuell

Open *_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

Reply

Answer 6

Barry

Level 9

59,861 points

Jan 28, 2011 12:47 AM in response to Bjuell

Bjuell 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

Reply

Answer 7

Bjuell

Level 1

0 points

Jan 28, 2011 3:43 PM in response to Barry

So, is there a shortcut (like F4 in Excel) or do I have to install $ twice?
Bjuell

Reply

Answer 8

Jan 28, 2011 7:59 PM in response to Bjuell

No, 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

Reply