Basic mathematical operations are performed using standard arithmetic operators: addition (), subtraction (), multiplication (), division (), and indices or powers (^). These operators allow for the direct manipulation of numeric values and cell data within a formula.
Spreadsheets follow a strict Order of Operations, often referred to as BIDMAS, which dictates that brackets are processed first, followed by indices, division and multiplication, and finally addition and subtraction. Proper use of brackets is essential to ensure calculations are executed in the intended sequence.
External referencing allows functions to pull data from other worksheets or even separate workbooks. This capability enables the creation of large, interconnected data systems where a central dashboard can summarize data stored in multiple source files.
Aggregation functions like , , , and allow for the rapid analysis of large cell ranges (). These functions automatically ignore empty cells or non-numeric data depending on their specific definitions.
Counting functions vary based on the data type: only tallies cells containing numeric values, while (Count All) includes cells containing text, numbers, or errors. This distinction is vital for accurate data auditing and inventory tasks.
Precision functions control how numbers are displayed and stored; removes decimal components to round down to the nearest integer, whereas allows the user to specify a precise number of decimal places (e.g., for currency formatting).
The IF function is the primary tool for automated decision-making, using the syntax . It evaluates a logical test and returns a specific result based on whether the test is met.
Nesting involves placing one function inside another to handle multi-layered logic. For example, a nested can check a primary condition and, if false, trigger a secondary to evaluate a different criteria, allowing for complex branching paths.
Logical conditions can compare text (e.g., ), numbers (), or even results of other functions. If the 'false' value is omitted or represented by empty quotes (""), the spreadsheet can be made to display a blank cell when conditions are not met.
| Feature | VLOOKUP | HLOOKUP | XLOOKUP |
|---|---|---|---|
| Search Direction | Vertical (Columns) | Horizontal (Rows) | Bi-directional |
| First Column Requirement | Mandatory | Mandatory (Top Row) | No |
| Default Match | Approximate | Approximate | Exact |
Syntax Verification: In exams, always ensure your functions have the correct number of commas and brackets. A missing bracket in a nested statement is a common reason for losing marks.
Lookup Arguments: For VLOOKUP and HLOOKUP, always double-check the 'Column Index' or 'Row Index'. Remember that the index starts at 1 for the first column/row of the selected range, not the spreadsheet's absolute column A or row 1.
Blank Returns: If a task requires a cell to remain empty if a condition is not met, use empty double quotes ("") in the value_if_false argument of an function. Simply leaving the argument blank may result in the cell displaying "0" or "FALSE".
Absolute vs. Relative Referencing: When asked to explain or write a formula intended for replication (copying down a column), evaluate which parts must stay fixed (, using Relative$).
