SUM & Average Functions

Once you’ve mastered the use of formula in spreadsheets you may begin to find that many of your formulas become very long and complicated. In order to make the users life easier many spreadsheets applications include a list of builtin functions that the user can add in in a similar way to a formula.

One of the easiest ways to identify a function within a spreadsheet is to look for the brackets. In the example below , we can tell that some is a function because it begins with an equal sign, is followed by the name of the function, and then the name of the function is followed by a set of brackets that contains the data being passed into the function. The data inside the brackets is often referred to as parameters.

=SUM(10,2,15)

SUM Function

The first function that we’re looking at in this lesson is the sum function. The sum function takes in either a set of numbers , or references to a set of cells and adds together all of the numbers providing a sum total.

Some can be used in a number of different ways. In this first example we have provided a simple set of numbers each separated by a comma :

=SUM(10,2,15)

When typed into a spreadsheet, what value would be shown in the cell?

In the second example, we have provided a set of cell references which will pass in the numbers shown in each cell refer to in the function.

spreadsheet_example 1

When typed into a spreadsheet, what value would be shown in the cell?

In this third and final example, we have provided arranged cell reference where the range starts with the first reference and gathers all of the data in the cells up to and including the second cell reference.

spreadsheet_example 2

When typed into a spreadsheet, what value would be shown in the cell?

AVERAGE Function

The second function we will be looking at is the average function. This allows us to calculate the mean average have a set of data without needing to know the formula to do so. In the following example, we have given a set of cell references to the function which will calculate the mean average of all numbers.

spreadsheet_example 3

If we were creating a formula come on we would need to know how to calculate the mean average and so our formula would look similar to the one below:

spreadsheet_example 4

Activity

Consider the two examples of calculating an average and write down why you might use a function instead of a formula and your spreadsheet if you are calculating the average for each column in a business spreadsheet.