CCNC/CCNC Module 4/Working with Formulas and Functions/Working with functions

From WikiEducator
Jump to: navigation, search

Working with functions

As explained earlier, formulas rely on functions to perform the required calculations. A function is an inbuilt utility that automates the numeric operation to be performed. Calc employs the following functions.

Sum

We could add up the contents of all the cells in the range B4: D7 using the formula

=B4+B5+B6+B7+C4+C5+C6+C7+D4+D5+D6+D7

Obviously, the bigger the cell range we wish to add the more terms there will be in the expression.

The SUM function simplifies the process considerably. All we have to enter is the function:

=SUM(B4:D7)

The = sign in front of the SUM function means add the contents of all the cells in the range B4:D7.

The SUM function can be used in an arithmetic expression as shown in the following examples:

=SUM(C4:F8) + B6

=B6 – SUM(B7:B12)

=SUM(A3:B6) + SUM(C3:D9)

Syntax

The syntax of a function is the formal structure of the function. We write the syntax of the SUM function as:

=SUM(range)

Where range is the range of cells whose values are being summed as, for example, =SUM(B4:D7).

We will use this structure when introducing the other functions.

Average

Syntax: =AVERAGE(range)

The calculation of the AVERAGE begins with the sum of the values in all the cells of the range. This sum is then divided by the number of cells involved.

Max and Min

Syntax: = MAX(range), =MIN(range)

These functions determine the maximum and minimum values in a range of cells.

Count

Syntax: =COUNT(range)

COUNT gives the number of cells that contain values in range.


Observe that =AVERAGE(range) is actually the same as =SUM(range)/COUNT(range.

Using functions in a spreadsheet

Often functions are used to summarise data in a spreadsheet. When you create a spreadsheet, it is good practice to put your summary information at the top of the spreadsheet and the data on which the summary is based below.

Suppose we wish to summarise the data in the spreadsheet below.

SCREENSHOT HERE


We may wish to find the sum, average, maximum and minimum of each column as well as the number of data entries in each column. We start by inserting four rows below row 2.

  • Select rows 2 to 6.
  • Insert->Rows
  • Enter the following in the newly inserted A3:A7

A3: SUM

A4: MAXIMUM

A5: MINIMUM

A6: ENTRIES

A7: AVERAGE

  • Enter the following in B3:B7

B3: =SUM(B9:B39)

B4: =MAX(B9:B39)

B5: =MIN(B9:B39)

B6: =COUNT(B9:B39)

B7: = AVERAGE(B9:B39)

The next screen illustrates the situation at this stage. At this stage we are displaying the functions in the cells rather than the values. How to do this will be explained shortly.

SCREENSHOT HERE


We will now enter the values generated by each of the functions in B3:B7 to be inserted in the cell range B3:B7.

  • Select B3:B7
  • Edit->Copy
  • Select C3:K7
  • Edit->Paste

SCREENSHOT HERE

If you opt to display the functions rather than values in the cells, you will do the following:

  • Tools->Options
  • Expand Spreadsheet and select View.
  • Tick the Formulas checkbox in the Display area. If you wish to display values at a later stage, untick this box.

Where an entry is too wide to display in a cell, a series of hash symbols (#####) will be displayed.

  • Widen the columns as necessary to view the cell contents.

SCREENSHOT HERE


In the above screen, the ##### in cells E6 and E7 indicates that the cell is too narrow to display the contents.