ICT 0417 - Spreadsheets: Formulas vs. Functions

Objective

Understand the difference between a formula and a function in spreadsheet software.

What is a Formula?

A formula is a calculation that you enter into a spreadsheet cell. It always begins with the equals sign (=). Formulas allow you to perform calculations, manipulate text, and reference other cells in the spreadsheet.

Examples of formulas:

• `=A1+B1` (Adds the values in cells A1 and B1)
• `="Hello" & " World"` (Concatenates the text "Hello" and " World")
• `=TODAY()` (Returns the current date)

What is a Function?

A function is a pre-built formula that performs a specific task. Functions have a defined syntax and accept arguments (input values) to produce a result. They are designed to simplify common calculations and operations.

Functions are typically categorized based on their purpose, such as:

• Mathematical Functions: Perform mathematical calculations (e.g., SUM, AVERAGE, MAX, MIN).
• Statistical Functions: Perform statistical calculations (e.g., STDEV, MEDIAN).
• Text Functions: Manipulate text (e.g., LEFT, RIGHT, MID, CONCATENATE).
• Date and Time Functions: Work with dates and times (e.g., TODAY, NOW, YEAR, MONTH).
• Lookup and Reference Functions: Retrieve data from other cells or sheets (e.g., VLOOKUP, INDEX, MATCH).

Key Differences: Formula vs. Function

Feature	Formula	Function
Definition	Any calculation starting with =	Pre-built formula with a specific purpose
Syntax	Variable; can be simple or complex	Has a defined syntax with arguments
Purpose	General calculation	Specific task (e.g., calculating the sum)
Examples	=A1*B1, =C2+5	=SUM(A1:A10), =AVERAGE(B1:B20)

Examples

Consider a spreadsheet with the following data:

Cell	Value
A1	10
A2	20
B1	5
B2	10

Examples of formulas and functions:

• Formula: `=A1+B1` (Result: 15)
• Function: `=SUM(A1:A2)` (Result: 30)
• Function: `=AVERAGE(A1:A2)` (Result: 15)
• Formula: `=A1*B2` (Result: 200)
• Function: `=MAX(A1:A2)` (Result: 20)

Practice

Try creating the following in a spreadsheet:

1. Calculate the sum of cells A1 to A5 using a function.
2. Calculate the average of cells B1 to B10 using a function.
3. Create a formula to calculate the area of a rectangle if the length is in cell C1 and the width is in cell C2.