6. Functions

A function performs a specific set of operations on its input values to produce a single output value. The main spreadsheet applications available today have hundreds of ready-made functions that you can use.

For instance, one way to add up a list of items is to add them up one by one like this

The formula in cell D2 can add up the three numbers in cells A2, B2, B3 but the function called SUM can also do the same thing. Creating a formula by hand to add up three numbers is very simple - but what if you had to add up 30,000 numbers? By far the easiest thing to do is to use the built-in function called SUM and provide it with the range of numbers to be added up.

This is the power of functions - they can carry out complicated operations on a set of numbers very easily. Other very common functions are

• VLOOKUP (looks up a value in a table)
• AVERAGE (works out the average of a set of numbers)
• COUNTIF (adds up numbers meeting a certain criteria)
• STDEV ( statistical function called standard deviation)
• DSUM (powerful way of summing values meeting a given criteria)
• WEEKNUM (finds the week number in the year that the input date falls within)

Functions can also be combined in a formula to make even more complicated operations. For example

=SUM(A1:A4) * AVERAGE(C2:C5) + VLOOKUP(A1,PRICE_LIST,2,FALSE)

This formula sums a range of values, multiplies the result by the average of another range of numbers then finally adds another number obtained from a look-up table.

People can be very creative in combining functions to work out complicated problems.