7 Replies Latest reply: Jun 18, 2013 2:02 AM by Cliff Peters
PatFrost Level 1 (0 points)

Hi, I am new to Numbers and am using iWork '09. How do I get the function formula (sum) to recognise when I am using the same formula in a column. eg, I have SUM(B3-C3) and wish this formula to be repeated down the column, so (B4-C4) etc, without actually having to insert the formula each time. Thanks.


iMac (21.5-inch Late 2009), Mac OS X (10.6.2)
  • Cliff Peters Level 2 (190 points)

    Hi Pat,

    "=SUM(B3-C3)" works.  Another option is "=B3-C3" No quotations.Screen shot 2013-06-17 at 8.36.26 PM.png

    The bottom Right corner of A3 has a o click & drag down page.

    Alternatively click A3 then the position down the page you would like to end the formula, then got to Insert, Fill, Fill Down.  NB I have set up a shortcut for it.

     

    Enjoy

    Hope this helps

     

     

    Screen shot 2013-06-17 at 8.44.43 PM.png

  • Jerrold Green1 Level 7 (29,960 points)

    Pat,

     

    Cliff's suggestion to eliminate the SUM function is not only an equivalent, it is the preferred way to express a simple difference. The SUM function serves only to confuse the reader since you only have one argument (expression) in your use of it, and SUM is only really useful with ranges or multiple arguments (expressions).

     

    Your question is really about repeating a formula with relative addressing, not the use of SUM, be we can't resist commenting.

     

    In addition to using the Fill tool, you can Copy the first cell and Paste it to the rest of the range. The result will be the same as a Fill. For small ranges, Fill works great. For very long ranges, like entire columns, sometimes there is a advantage to selecting an entire column by clicking on its tab and Pasting.

     

    Jerry

  • PatFrost Level 1 (0 points)

    Thanks Cliff. I will give it a try when I get back into the accounts. Cheers.

     

    Pat

  • PatFrost Level 1 (0 points)

    Thanks Jerry. I will try both ways. I am hoping this saves me a lot of work.

     

    Pat

  • PatFrost Level 1 (0 points)

    Jerry, thank you!!!! I just did the copy and paste and it worked!!!! You have no idea how much time and effort you have saved me.

     

    Pat

  • PatFrost Level 1 (0 points)

    Thanks again Cliff. It works! But as Jerry said, because I am doing whole accounts pages with multiple columns and rows, the copy and paste was quicker. Cheers. You two guys rock!

     

    Pat

  • Cliff Peters Level 2 (190 points)

    Hi Pat,

     

    Jerry's idea is great I'll make a mental note of that, didn't know it could be done!

     

    Regards

    Cliff