SpreadsheetsFormulas and functions

Formulas and functions are extremely useful features. They make automatic calculations that update when the data does.

Formulas

Formulas are usually simple calculations, eg adding two or more numbers together. They always start with an equals sign (=).

Symbols used in formulas

There are a number of symbols used in formulas or calculations. These are the most common ones:

• ‘+’ add
• ‘-’ subtract
• ‘*’ multiply
• ‘/’ divide

Functions

Functions make more complex calculations. Simple and regularly used functions include:

• SUM – adds values in selected cells
• MIN – finds smallest value
• MAX – finds largest value
• AVERAGE – finds the average value
• COUNT – counts how many of the selected cells have numbers in them

Like formulas, all functions start with an equals sign (=) followed by the function’s name, eg SUM, MIN, MAX, etc.

AutoSum

This feature writes a SUM function for you. Highlight or select the cells you want to add together then click the AutoSum button.

Advanced functions

• IF – change the value of a cell if something is true, eg if a customer’s total bill is over £100, deduct 10% from their bill.
• COUNTIF – adds up cells that meet a certain rule, eg count the number of students that achieved level 6.
• VLOOKUP – matches contents of a cell with an answer, eg how much is a pepperoni pizza?