[Tutorial] How do I specify the formula for a column?

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Locked
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

[Tutorial] How do I specify the formula for a column?

Post by MrProgrammer »

Let's assume we have a spreadsheet with information about daily company inventory, say date in column A, Boston warehouse inventory in column B, Chicago warehouse inventory in column C, and Detroit warehouse inventory in column D. How do we indicate to Calc that we want the formula for column E to be "the sum of columns B, C, and D", showing total inventory on that day?

Well, the direct answer is that we can't. Formulas apply to cells, not to columns. But this answer doesn't help with the goal: getting the spreadsheet to display total inventory in column E. It will help to be specific. We have the words "Date", "Boston", "Chicago", "Destoit", and "Total" in cells A1, B1, C1, D1, and E1. Row 2 has the date January 1 in A2 and inventory counts on that day for Boston, Chicago, and Detroit in B2 through D2. Row 3 has the date January 2 in A3 and inventory counts on that date in B3 through D3, etc. We'd like E2 to use the formula =SUM(B2:D2), E3 to use =SUM(B3:D3), E4 to use =SUM(B4:D4), …. If we have a month of data, the last cells will be in row 32 and we want =SUM(B32:D32) in E32.
Row    A        B        C        D        E
 1   Date    Boston   Chicago  Detroit   Total
 2   Jan 1      314       159      265   =SUM(B2:D2)
 3   Jan 2      358        97       93   =SUM(B3:D3)
 …     …          …         …        …   …
32   Jan 31     238       462      643   =SUM(B32:D32)
Now, we could simply type formulas =SUM(B2:D2) in E2, =SUM(B3:D3) in E3, =SUM(B4:D4) in E4, … but this will get tedious after a dozen rows, and a performing a lot of manual entries increases the chance for errors. The key to a simple solution is the concept of relative references. Let's take a look at cell E2. Many beginners think that putting formula =SUM(B2:D2) in E2 tells Calc to sum the values in cells B2 through D2. This is incorrect!! The formula to sum the values in cells B2 through D2 is =SUM($B$2:$D$2). To reference specific cells we use the $column$row form. This is explained in more detail in section 8. Using formulas and cell references of Ten concepts that every Calc user should know.

Cell references without the $ signs are always relative references. They indicate that the reference is relative to the cell which contains the formula. Relative to E2, cells B2, C2, and D2 are the three to its left. So in E2 the formula =SUM(B2:D2) means "sum the three cells to the left". When we omit the $ signs in the formula, there is no reference to any specific cells, just to the three cells to the left. We type "B2:D2" in the formula only so Calc will know we want to reference the three cells to E2's left; Calc knows it is a relative reference because we did not use the $ sign. E2 will display the total inventory for January 1.

Now copy the formula in E2 and paste it into E3. Our "sum the three cells to the left" formula now appears in E3. It's displayed in E3 as =SUM(B3:D3), since, relative to E3, the three cells to its left are B3 though D3. Note that the appearance of the formula has changed, but all we did was a copy/paste operation. E3 will display the total inventory for January 2. It is important to understand that the formulas in E2 and E3 are the same formula, although they appear different. A characteristic of relative formulas is that they are displayed differently, depending on which cell they appear in.

The use of identical relative formulas in a column in very common in spreadsheets, and Calc provides several methods to produce them. Do not use the last two if your formula is an array formula.
  • The most basic way is to enter the formula in the first cell of the column, Edit → Copy, select all the other cells in the column, Edit → Paste. (You must use Copy; do not use Cut.)
  • You can enter the formula in the first cell of the column, select it and the other cells in the column, Edit → Fill → Down.
  • If you have data immediately to the left of the formula's column and you want to replicate the formula down to match that data, you can use the fill handle. Type the formula into the first cell and press Enter. You'll see a black square in the lower right corner of the formula's cell. Double click the little black square to fill the formula down as far as the data to its left. In our example above you'd type the formula in E2 and press Enter. When you double click E2's fill handle, Calc will copy the formula into E3 through E32 if you have data in D2 through D32. This is often the easiest method.
  • You can drag the fill handle to copy the formula.
  • You can select all of the cells in the column, type the formula, and instead of Enter, press ⌥Enter (option+Enter) on a Mac or Alt+Enter on other platforms. This key combination puts the formula into all of the cells of the selection. It does not matter if you type the formula into the first cell, the last cell, or somewhere in the middle, though you have to specify the correct cell references for the cell into which you've typed the formula. That is, for E2 type =SUM(B2:D2) but for E32 type =SUM(B32:D32).
When we say "all of the cells in the column" we mean "all of the cells in the column in the rows with the data". Modern spreadsheet programs, like Calc, support an enormous number of rows. There is no point in copying the summation formula into a million or more cells when you have only 31 rows of data. This would make the spreadsheet very large, and it will load/operate/save slowly. Some formulas may produce errors if they reference unused cells. For example, a division operation will produce a #DIV/0! result since the value of an empty cell is zero. If you have copied the formula into cells, and later add more rows of data to the left, select the last cell with the formula, then double click its fill handle to replicate the formula to match the data.

In many cases, all the references in a formula will be relative (to the cell containing it). However if your formula needs to always access a specific, fixed cell, just use an absolute reference for that cell. Example:
• cell B2 should have the value in A2,
• cell B3 should have the value of A2+A3,
• cell B4 should have the value of A2+A3+A4,
• cell B5 should have the value of A2+A3+A4+A5, etc.
Thus column B is the running total of the cells in column A. Enter =SUM(A$2:A2) in B2, then fill that down. The $ prevents the beginning of the sum range from changing as it is copied to other cells in column B.

If you have large numbers of cells to select, say E2 through E5000, doing so with the mouse is inconvenient. Calc provides the Name Box (see Help → Index → formula bar;sheet area names) found to the left of fᵪ and ∑ in the formula bar. Just type E2:E5000 in the box and press Enter to select these cells for a Paste or Fill operation. If you've created a set of identical formulas in a column, and need to make a change to them, make the change to the first cell of the column, then copy/fill it to the other cells in the column.

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Locked