Formulae & Functions (CIE IGCSE ICT)

What is the difference between a Formula and a Function?

• A formula can:
  • Contain a function
  • Be simple calculations/mathematical operation
  • Be typed directly into the formula bar

It is a statement that performs calculations on values in your worksheet. For instance, "=A1+B1"

• A function:
  • Is a special type of formula/complex formula
    
  • Is built into the software/spreadsheet
    
  • Can be used to simplify complicated calculations
    
  • Can have built-in commands
    
  • Has a pre-defined name/reserved word

It is a preset command in spreadsheets. It is a type of formula that performs specific calculations like SUM, AVERAGE, MAX, MIN, etc. For instance, "=SUM(A1:B1)"

Using Functions

	A	B	C	D
1	10	20	30	40
2	15	25	35	45
3	20	30	40	50

• Spreadsheets offer a variety of functions. Some of the most commonly used are:
  • E.g. "=SUM(A1:B2)" This would add all the numbers from cell A1 to B2, giving the result 65.
  • E.g. "=AVERAGE(A1:B2)" This would find the average of all numbers from cell A1 to B2, giving the result 16.25.
  • E.g. "=MAX(A1:B2)" This would return the maximum number in the range from A1 to B2, which is 25.
  • E.g. "=MIN(A1:B2)" This would return the minimum number in the range from A1 to B2, which is 10.
  • E.g. ​​"=INT(A2)" This would round down the number in cell A2 to the nearest integer, which is 15.
  • E.g. "=ROUND(A2, 0)" This would round the number in cell A2 to the nearest whole number, which is 15.
  • E.g. "=COUNT(A1:B2)" This would count the number of cells in the range A1 to B2 that contain numbers, which is 4.
  • E.g. "=LOOKUP(25, A1:B3)" This would look for the number 25 in the range A1 to B3 and return it.
  • E.g. "=VLOOKUP(25, A1:B3, 2, FALSE)" This would look for the number 25 in the first column of the range A1 to B3 and return the corresponding value in the second column of the same row.
  • E.g. "=HLOOKUP(25, A1:D2, 2, FALSE)" This would look for the number 25 in the first row of the range A1 to D2 and return the corresponding value in the second row of the same column.
  • E.g. "=XLOOKUP(25, A1:B3, D1:D3)" This would look for the number 25 in the range A1 to B3 and return the corresponding value from the range D1 to D3.
  • E.g. "=IF(A1>B1, "Yes", "No")" This would check if the value in cell A1 is greater than the value in cell B1. If true, it returns "Yes". If false, it returns "No".
• SUM: Adds all the numbers in a range of cells
• AVERAGE: Calculates the average of a range of cells
• MAX and MIN: Finds the largest and smallest numbers in a range respectively
• INT: Rounds a number down to the nearest integer
• ROUND: Rounds a number to a specified number of digits
• COUNT: Counts the number of cells in a range that contain numbers
• LOOKUP, VLOOKUP, HLOOKUP, XLOOKUP: Looks up values in a table based on a given condition
• IF: Returns one value if a condition is true and another if it's false

Using External Data Sources within Functions

• Spreadsheets allow you to use external data sources within functions.
• This could be data from another worksheet, workbook, or even a database

Using Nested Functions

• You can use a function within another function. This is called nesting.
• For instance, "=IF(A1>B1, MAX(A1:B1), MIN(A1:B1))".
  • This checks if A1 is greater than B1, and if true, it returns the max value, else it returns the min value