10 Replies Latest reply: Jan 7, 2014 4:42 PM by Badunit
cairnterrier Level 1 Level 1 (0 points)
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)
  • Barry Level 7 Level 7 (29,210 points)
    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
  • cairnterrier Level 1 Level 1 (0 points)
    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
  • Level 8 Level 8 (41,780 points)
    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
  • Bjuell Level 1 Level 1 (0 points)
    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
  • Level 8 Level 8 (41,780 points)
    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
  • Barry Level 7 Level 7 (29,210 points)
    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
  • Bjuell Level 1 Level 1 (0 points)
    So, is there a shortcut (like F4 in Excel) or do I have to install $ twice?
    Bjuell
  • Jerrold Green1 Level 7 Level 7 (29,855 points)
    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
  • Badunit Level 6 Level 6 (11,385 points)

    A thread from the dead.