What feature inserts a function that totals the cell above or to the left of the selected cell?

In your formulas, you can include references to cells, ranges of cells, and whole columns or rows of data—including cells in other tables and on other sheets. Numbers uses the values in the referenced cells to calculate the result of the formula. For example, if you include “A1” in a formula, it refers to the value in cell A1 (the cell in Column A and Row 1).

Note: If your table uses categories and you add a new row within a range of reference cells, the formula results won’t include the new row unless you change the cell reference.

The examples below show the use of cell references in formulas.

• If the referenced range is more than one cell, the starting and ending cells are separated by a single colon.

  COUNT(A3:D7)

• If the reference is to a cell in another table, the reference must contain the name of the table (unless the cell name is unique within all tables).

  Table 2::B2

  Note that the table name and cell reference are separated by a double colon (::). When you select a cell in another table for a formula, the name of the table is automatically included.

• If the reference is to a cell in a table in another sheet, the sheet name must also be included (unless the cell name is unique within all the sheets).

  SUM(Sheet 2::Table 1::C2:G2)

  The sheet name, table name, and cell reference are separated by double colons. When you click a cell in another sheet while building a formula, the name of the sheet and the name of the table are automatically included in the formula.

• To refer to a column, you can use the column letter. The formula below calculates the total of the cells in the third column:

  SUM(C)

• To refer to a row, you can use the row number. The formula below calculates the total of the cells in the first row:

  SUM(1:1)

• To refer to a row or column with a header, you can use the header name. The formula below totals all the cells in the row with the header “Revenue”:

  SUM(Revenue)

The formula below refers to a column by its header name, “Number of Guests,” and cell B2 in a table on another sheet.

Formulas in Excel are basically mathematical expressions that use cell references (e.g., “A5”,” D17”) as arguments.  For example, a formula that adds the contents of cell E5 and E6 could be written as follows:

= E5+E6

(Note: all formulas in Excel need to be preceded by an “=” sign.) If the values contained in E5 and E6 are 6 and 11, respectively, the formula will produce 17 as the value it displays. If you change E5 to 7, the result will automatically change to 18.

Example

Let's say you were putting together an office supply order, and you wanted to keep track of much you were spending. You could put together a spreadsheet like the one below, with the list of items to be purchased, their unit prices, the number of each item ordered, and the total spent for each.  It would make sense to enter the things you know in advance (like the price of individual items and the number ordered), but you could let Excel calculate the totals for you.  For the first item listed below (pencils), this could be done by making the value of the total price (cell D2), the value of the unit price (held in cell C2) multiplied by the number of items ordered (held in D2).  This formula would be written "=B2*C2".

After hitting "Enter", the cell will display the calculated value, while the formula bar will still display the formula.  (Note: Always hit “Enter” when finished entering a formula, manually.  If you click off the cell, the cell you click to will be added to your formula.)

Excel will generally be able to handle any properly-input mathematical formula, if valid operators are used. Commonly used operators include "+" (addition), "-" (subtraction), "*" (multiplication) and "/" (division).  (Microsoft has a complete list of valid operators to be used in Excel formulas on the Office website).  Here are some examples of formulas using common operators:

Formula                                                                              Description

=C2-B2                                                                Subtracts contents of B2 from contents of C2

=C2/B2                                                                Divides contents of C2 by contents of B2

=(B2+C2+D2)/3                                                  Adds contents of B2, C2, and D2 and divides result by 3

Excel also has built-in functions that can do a lot of useful calculations.  These are most easily accessed by hitting the Insert Function button, which is represented by the “fx” symbol next to the formula bar.  For example, instead of entering the formula shown above, the same result could have been achieved using the built-in "PRODUCT" function by clicking in cell D2 and hitting the Insert Formula button.  This would give a dialog box like the one shown, below.

After selecting "PRODUCT" and clicking OK, you will get another dialog box, that allows you to select the cells to be multiplied.  You can do this for individual cells, by selecting cells separately in the "Number1" and  "Number2" boxes shown below, or by selecting an array of cells, by clicking and dragging on the range cells you want to use on the spreadsheet, itself.  (Note: if you try to enter a formula in a cell using the Insert Formula button and there are adjacent cells with numbers, Excel will often select those cells automatically, so make sure the cells selected in the dialog box are the correct ones.) 

Once you click "OK", your completed formula will be input into the cell.

Copying and pasting formulas

Often, you will need Excel to do a series of similar computations, where the only things that will change are the cells used as arguments.  For instance, in the example above, you would probably like Excel to calculate the Total Price for each item in the order.  You could re-input the same formula used to get the total price for pencils in each cell in that row, just changing the cells referenced (i.e. "=PRODUCT(B3:C3)", "=PRODUCT(B4:C4)", etc.), but Excel has simpler method for this. If you have multiple cells in the same row or column that need to do the same computation, you can simply copy the value in the cell you entered a formula, and then paste it into the subsequent cells.  Excel will then automatically adjust which cells are included in the formula, based upon which cell the formula was pasted to.  So, if the original formula entered in D2 was "=PRODUCT(B2:C2)", the formula pasted into D4 would be "=PRODUCT(B4:C4)"

More simply, if you have a formula you want repeated in a number of directly adjoining cells, you can just click and drag the bottom right corner of the cell with the original formula (see image below) onto the cells you want the same formula entered, and Excel will automatically copy and paste the formula for you, with appropriate adjustments made to the cell numbers in the formula.

Which of the following functions insert the total of a range?

Which of the following inserts a function that computes for the totals of the cells that are adjacent to the selected cell quizlet?

Which of the following can you use to insert a formula using a function?

Where can you see a brief description of a selected function?