05 Using Formulae and Functions (part 1)

Difficulty Level: Beginner
Excel Version: XP
Assumed Knowledge: None

 

 

The aim of the tutorial is to:

 

show you how to create mathematical formulae and functions and perform calculations on the data in your worksheet.

 

Formulae and Functions

Excel is a spreadsheet programme that allow you to do more than just present numerical data in tables. It also allows you to create mathematical formulas and execute functions and this handout will show you how to create these calculations.

Formulae

Excel enables you to perform simple calculations such as adding two cells together to complex calculations. Formulae are entered in the worksheet cell and begin with an equal sign "=". Each formula then includes the addresses of the cells whose values will be included in the formula with mathematical operands placed in between. After the formula has been typed into the cell, the calculation can be performed immediately and the formula seen in the formula bar.

To add, subtract, multiple or divide two cell values:

  1. Click on the cell where the result is to appear.
  2. To begin the calculation type =
  3. Type in the cell address of the first cell.
    The first cell will have a (blue) coloured border and the cell address in the formula will be shown in the same (blue) colour.
  4. Type in + to add; - to subtract;* (SHIFT+8) to multiply; / to divide
  5. Type in the cell address of the second cell.
    The second cell will have a (green) coloured border and the cell address in the formula will be shown in the same (green) colour.
    The formula will appear in both the results cell and the formula bar.
    The illustration shows two cells to be added together:

  6. Press the ENTER key.
  7. The result will appear in the designated cell.

To find the sum of a range of two or more cell values using AutoSum:

  1. Click on the cell where the result is to appear.
  2. Click on the AutoSum button on the standard toolbar.
    A formula will appear in the formula bar and the result cell.
  3. Ensure that the formula shows the correct range of cell (: indicates range).
  4. Press the ENTER key on the keyboard.
    The result will appear in the designated cell.

In the illustration below cell A4 has been chosen to show the sum of the values in the cells ranging from A1 to A3. When the AutoSum button has been activated the cell address window at the top left will contain the function name SUM, the formula =SUM(A1:A3) in the formula bar with a : indicating a range of cells, a blue dotted border around the range of cells containing values to be summed, and the formula appears in the cell where the result will appear:

In this example the formula is correct. When the ENTER button on the keyboard is pressed the result will appear in the selected cell:

To find the average value of a range of two or more cell values:

  1. Click on the cell where the result is to appear.
  2. Click on the black triangle to the right of the AutoSum button on the standard toolbar.
    A pop-down menu will appear:

  3. In the pop-down menu click on Average.
    A formula will appear in the result cell and in the formula bar.
    The structure of the formula will be the same as the sum formula above except the word sum will be replaced with the word you chose from the pop-down menu, i.e. AVERAGE.
    Ensure that the formula shows the correct range of cells.
  4. Press the ENTER key on the keyboard.
    The result will appear in the designated cell.

To find the number of cells in a raneg of cells:

  1. Click on the cell where the result is to appear.
  2. Click on the black triangle to the right of the AutoSum button on the standard toolbar.
  3. In the pop-down menu click on Count.
    A formula will appear in the result cell and in the formula bar.
    Ensure that the formula shows the correct range of cells.
  4. Press the ENTER key on the keyboard.
    The result will appear in the designated cell.

To find the maximum or minimum cell value in a range of cells:

  1. Click on the cell where the result is to appear.
  2. Click on the black triangle to the right of the AutoSum button on the standard toolbar.
    In the pop-down menu click on either Max on Min.
    Ensure that the formula shows the correct range of cells.
  3. Press the ENTER key on the keyboard.
    The result will appear in the designated cell.

To perform more complex functions using the Insert Function box see the handout Using Formulae and Functions (part 2).

Linking Formulae across Worksheets

The above example shows formulae using cell values that appear on the same worksheet. As each book in Excel can contain more than worksheet it is possible to use the values from different worksheets within the same workbook in a formula. The only difference to the cell address is that it should follow the name of the worksheet and an ! and follow the format "sheetname!celladdress". For example, the value of cell A1 in worksheet 1 and the value of cell B4 in worksheet 2 can be added and the formula for this would be "=Sheet1!A1+Sheet2!B4". If you have renamed any worksheet then include the names in the formula. Imagine that a book about Expenses contains the first worksheet called Travel, the second called Accommodation and the third called Total Expenses. If we want to add Travel cell B10 and Accommodation cell C14 and put the result in Total Expenses cell E5 then then the formula entered into Total Expenses E5 would be =Travel!B10+Accommodation!C14:

This formula can either be typed in full or the mouse can be used to click on the worksheet tab and relevant cell to build up the formula.

To add, subtract, multiple or divide two cell values in different worksheets:

  1. Click on the cell where the result is to appear.
  2. To begin the calculation type =
  3. Click on the worksheet tab.
  4. Type in an !
  5. Click on the cell.
  6. Type in + to add; - to subtract;* (SHIFT+8) to multiply; / to divide
  7. Click on the worksheet tab.
  8. Type in an !
  9. Click on the cell.
  10. Press ENTER.
    The formula will appear in the formula bar.
    The result will appear in the designated cell.

To find the sum of a range of two or more cell values in a different worksheet using AutoSum:

  1. Click on the worksheet and cell where the result is to appear.
  2. Click on the AutoSum button on the standard toolbar.
  3. Click on the worksheet tab with the cell range to be summed.
  4. Select the cell range to be summed.
  5. Press the ENTER key on the keyboard.
    The result will appear in the designated cell.

In the illustration below cell A4 has been chosen to show the sum of the values in the cells ranging from A1 to A3. When the AutoSum button has been activated the cell address window at the top left will contain the function name SUM, the formula =SUM(A1:A3) in the formula bar with a : indicating a range of cells, a blue dotted border around the range of cells containing values to be summed, and the formula appears in the cell where the result will appear:

In this example the formula is correct. When the ENTER button on the keyboard is pressed the result will appear in the selected cell:

 

Relative, Absolute, and Mixed Referencing Calling cells by just their column and row labels (such as "A1") is called relative referencing. When a formula contains relative referencing and it is copied from one cell to another, Excel does not create an exact copy of the formula. It will change cell addresses relative to the row and column they are moved to. For example, if a simple addition formula in cell C1 "=(A1+B1)" is copied to cell C2, the formula would change to "=(A2+B2)" to reflect the new row. To prevent this change, cells must be called by absolute referencing and this is accomplished by placing dollar signs "$" within the cell addresses in the formula. Continuing the previous example, the formula in cell C1 would read "=($A$1+$B$1)" if the value of cell C2 should be the sum of cells A1 and B1. Both the column and row of both cells are absolute and will not change when copied. Mixed referencing can also be used where only the row OR column fixed. For example, in the formula "=(A$1+$B2)", the row of cell A1 is fixed and the column of cell B2 is fixed. Basic Functions Functions can be a more efficient way of performing mathematical operations than formulas. For example, if you wanted to add the values of cells D1 through D10, you would type the formula "=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10". A shorter way would be to use the SUM function and simply type "=SUM(D1:D10)". Several other functions and examples are given in the table below: Function Example Description SUM =SUM(A1:100) finds the sum of cells A1 through A100 AVERAGE =AVERAGE(B1:B10) finds the average of cells B1 through B10 MAX =MAX(C1:C100) returns the highest number from cells C1 through C100 MIN =MIN(D1:D100) returns the lowest number from cells D1 through D100 SQRT =SQRT(D10) Finds the square root of the value in cell D10 TODAY =TODAY() Returns the current date (leave the parentheses empty) Function Wizard View all functions available in Excel by using the Function Wizard. 1. Activate the cell where the function will be placed and click the Function Wizard button on the standard toolbar. 2. From the Paste Function dialog box, browse through the functions by clicking in the Function category menu on the left and select the function from the Function name choices on the right. As each function name is highlighted a description and example of use is provided below the two boxes. 3. Click OK to select a function. 4. The next window allows you to choose the cells that will be included in the function. In the example below, cells B4 and C4 were automatically selected for the sum function by Excel. The cell values {2, 3} are located to the right of the Number 1 field where the cell addresses are listed. If another set of cells, such as B5 and C5, needed to be added to the function, those cells would be added in the format "B5:C5" to the Number 2 field. 5. Click OK when all the cells for the function have been selected. Autosum Use the Autosum function to add the contents of a cluster of adjacent cells. 1. Select the cell that the sum will appear in that is outside the cluster of cells whose values will be added. Cell C2 was used in this example. 2. Click the Autosum button (Greek letter sigma) on the standard toolbar. 3. Highlight the group of cells that will be summed (cells A2 through B2 in this example). 4. Press the ENTER key on the keyboard or click the green check mark button on the formula bar .

 

MICROSOFT EXCEL 5 USING FORMULAS AND FUNCTIONS Exercises 1) Open a new document. 2) In cell A1 type in 'Weekday'. 3) In cell B1 type in 'Bus fare' 4) In the 7 cells below Weekday type in the days of the week from Monday to Sunday. 5) In the 7 cells below Bus fare type 3, 3, 3, 1.5, 1.5, 3, 0. 6) Click on 'A' so that column A is highlighted. Click on 'Format', highlight 'Column', then click on 'AutoFit Selection'. 7) Repeat 6) for column B. 8) Select cells B2 to B8, then click on 'Format', then 'Cell', then select the number category 'Currency' to two decimal places. The numbers you typed in under bus fare should appear with a pound sign, followed by the bus fares in pounds and pence. 9) Click on cell B9. This is where the total bus fare for the week will appear. 10) Click on the AutoSum button. The formula =SUM(B2:B8) should appear in cell B9. 11) Press the return key. The amount of £15.00 should appear in cell B9. 12) Type the word 'Total' in cell A9. 13) Click on cell B10. This is where the average bus fare for the week will appear. 14) Use the instructions in the handout to find the average bus fare. Ensure that the formula that applies to this cell is =AVERAGE(B2:B8). When you press return the figure £2.14 (or similar) should appear in the cell. 15) Close the document. 1

Here's How:

  1. ??
  2. ???
  3. ???