7.6 Functions

A function is a preset formula that can be used instead of entering a more cumbersome formula. A simple example is the frequently used SUM function. It is very useful when there is a long list of values to add. For example, instead of entering the formula use the SUM function:

=B1+B2+B3+B4+B5+B6+B7+B8+B9 =SUM(B1:B9)

The SUM function is a command that, in the example above, would add all the values of the cells from B1 to B9.

Most spreadsheet programs have a series of built-in functions that help us work with or analyse data quickly and easily. For example, we can find the average of a series of figures, or the maximum or minimum of these figures. Or we can determine an entry dependent on other results using the ‘IF’ function.

Each function uses a similar format called syntax. The formula =B1+B2+B3+B5 +B6+B7+B9 would be written as:

0002

Arguments are often cell references; however, arguments can also be numbers, text, true or false, arrays, or error values as well. The argument can also be a formula or another function that returns a value.

Statistical functions

Statistical functions are probably the most commonly used functions. They are used to analyse data in ranges. For example, to find which month had the highest sales one would use the MAX function. The MIN function would be used to display the lowest sales.

In this section you will work with the following functions:

  • SUM – adds together all values of cells in the range
  • AVERAGE – calculates the average of all values in the range
  • MAX – displays the maximum or largest value of the cells in the range
  • MIN – displays the minimum or smallest value in the range of values.

Spreadsheets exercise 7

Functions

Skills practised
  • New file
  • Entering data
  • Entering functions
P0733
  1. Create the following worksheet and call it Functions.
  2. Click on B16 and type =SUM(B3:B14)
  3. Click on B18 and type =AVERAGE(B3:B14)
  4. Click on B19 and type =MAX(B3:B14)
  5. P0734
    Click on B20 and type =MIN(B3:B14)
  6. Find the sum (total), average, maximum and minimum of each department.
  7. Check the results then save and close the file.

Using functions on non-adjacent cells

In the next exercise you are going to add the sales for each salesperson to give the total sales. The expenses are also totalled.

Spreadsheets exercise 8

Using functions

Skills practised
  • Sum function
  • Max function
  1. Open the workbook Split sales downloaded from the above link.
  2. Use the SUM function to find the total sales for the week for each salesperson as shown below in column H.
  3. Now you will use a function to find the total sales and expenses each day. However, this time the cells in the function are not adjacent; for example, on Monday for total sales the values to be added are in cells C4, C10 and C16.
  4. Click on cell C22 and type =SUM(C4,C10,C16), which will sum just the values in those cells.
  5. Repeat to find the total sales for each day.
  6. Click on cell C23 and type =SUM(C5:C7,C11:C13,C17:C19), which will sum just the values in those cell ranges.
  7. Repeat to find the total sales for each day.
  8. In ROW 24, use a function to find the maximum sales for each day of the week.
  9. In ROW 25, use a function to find the minimum sales for each day of the week.
  10. Save and close the file.
    P0736.jpg

Date and Time functions

Date and Time functions are used to convert numerical values to date and time values and vice versa.

The TODAY function

The Today function can be used to display the current date on a worksheet. The Today function is one of the few functions that don’t actually have any arguments. It is entered simply as =Today()

The TIME function

The Time function can be used to create a time value that can then be used in calculations. For example when calculating the time spent parking or logged onto the internet. In this exercise you will use the Time function to enable the calculation of time spent logged onto the internet.