Formulae & Functions | IGCSE Information and Communication Technology Preparation - Year 11 PDF Download

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

Spreadsheets are equipped with various functions that aid in data manipulation and analysis. Let's explore some of the most frequently utilized functions:

• SUM Function: Adds up all the numbers within a specified range of cells. For instance, =SUM(A1:B2) would sum the values from cell A1 to B2, resulting in 65.
• AVERAGE Function: Calculates the average of numbers in a given range. For example, =AVERAGE(A1:B2) would provide the average of values from A1 to B2, yielding 16.25.
• MAX and MIN Functions: Determine the largest and smallest numbers within a range, respectively. For instance, =MAX(A1:B2) would identify the maximum number in A1:B2 as 25, while =MIN(A1:B2) would find the minimum as 10.
• INT Function: Rounds a number down to the nearest integer. If we consider =INT(A2), it would round down the value in cell A2 to the closest integer, which is 15.
• ROUND Function: Rounds a number to a specified number of decimal places. For instance, =ROUND(A2, 0) would round the value in A2 to the nearest whole number, resulting in 15.
• COUNT Function: Tallies the number of cells in a range containing numerical values. If we take =COUNT(A1:B2), it would count the cells with numbers in the range A1 to B2, which is 4.
• LOOKUP Functions: Search for specific values in a range and return them. For example:
  • =LOOKUP(25, A1:B3) would search for 25 in A1:B3 and return it.
  • =VLOOKUP(25, A1:B3, 2, FALSE) would find 25 in the first column of A1:B3 and return the corresponding value in the second column.
  • =HLOOKUP(25, A1:D2, 2, FALSE) would locate 25 in the first row of A1:D2 and return the corresponding value in the second row.
  • =XLOOKUP(25, A1:B3, D1:D3) would identify 25 in A1:B3 and return the corresponding value from D1:D3.
• IF Function: Checks a condition and provides different outputs based on whether it's true or false. For example, =IF(A1>B1, "Yes", "No") would output "Yes" if the value in A1 is greater than that in B1; otherwise, it would output "No".

Using External Data Sources within Functions

• Spreadsheets enable the use of external data sources in functions.
• These sources can include data from another worksheet, workbook, or a database.

Using Nested Functions

• Functions can be used within other functions, a technique known as nesting.
• For example, "=IF(A1>B1, MAX(A1:B1), MIN(A1:B1))".
• This formula checks if A1 is greater than B1; if true, it returns the maximum value, otherwise, it returns the minimum value.