Know and understand the difference between a formula and a function

📊 20 Spreadsheets – Formulas vs Functions

What’s a Formula?

A formula is a set of instructions that tells the spreadsheet how to calculate a value. Think of it like a recipe you follow step by step. The result depends on the numbers you put in the cells you reference.

What’s a Function?

A function is a pre‑built recipe that does a specific job for you. It’s like a machine that takes inputs and spits out a result. Functions can be used inside formulas, but they’re not the whole recipe themselves.

Formula vs Function – Quick Comparison

Aspect Formula Function
What it is A custom expression written by you. A built‑in tool with a fixed purpose.
How you use it Type it directly into a cell, e.g. =A1+B1. Select it from the function list, e.g. SUM(A1:A10).
Flexibility Fully customizable. Limited to the function’s purpose.
Learning curve Requires understanding of operators and cell references. Easy once you know the function name.

Common Functions (and what they do)

  • SUM – Adds a range of numbers: SUM(A1:A10)
  • AVERAGE – Finds the mean: AVERAGE(B1:B5)
  • IF – Makes a decision: IF(C1>10,"High","Low")
  • VLOOKUP – Looks up a value in a table: VLOOKUP(D1,Table,2,FALSE)
  • MAX / MIN – Finds the largest/smallest number: MAX(E1:E8)

How to Build a Formula Using Functions

  1. Decide what you want to calculate. Example: “Total sales for the month.”
  2. Identify the data range. Example: sales figures are in cells B2:B31.
  3. Choose a function that fits. For total sales, use SUM.
  4. Write the formula: =SUM(B2:B31).
  5. Press Enter and the spreadsheet calculates the result.
Exam Tip: When you see a question that asks for a total or average, think “Which function will give me that in one step?” Use SUM for totals, AVERAGE for means, and IF for conditional checks. Always double‑check that your cell references are correct – a small typo can change the whole answer! 🍎

Analogy: Recipe vs. Appliance

- Formula = Your own recipe: you decide the ingredients (cell references) and the steps (operators). - Function = A ready‑made appliance: you just plug in the ingredients (arguments) and it does the work for you. Both can produce the same dish (result), but the appliance is faster and less error‑prone for common tasks. 🧮

Exam Tip: Practice writing formulas that combine multiple functions. Example: =IF(SUM(A1:A5)>100,"High","Normal"). This shows you can nest functions inside formulas – a useful skill for complex questions. 📊

Quick Practice Challenge

You have a list of student scores in cells C2:C10. Write a formula that will:

  1. Calculate the average score.
  2. Return “Pass” if the average is 60 or above, otherwise “Fail”.
Try it out and check your answer against the solution below.

Solution: =IF(AVERAGE(C2:C10)>=60,"Pass","Fail")

Revision

Log in to practice.

3 views 0 suggestions