Applying Advanced Functions and Formulae to a Workbook

Understanding Advanced Functions and Formulae

Become familiar with the concept that more complex data analysis requires advanced functions and formulae.
Recognise that functions are built-in calculations that analysise data for you, such as SUM for finding the total of selected cells or AVERAGE for finding the average value of selected cells.
Appreciate that Excel functions use a system of parentheses “()” to carry out operations on the data within them.

Understand how to use a formula by typing “=” followed by a built-in function name or mathematical operation.
Learn to create calculations using cell references (e.g., “=A1+A2”) that automatically update if the contents of the referenced cells change.
Explore the use of arithmetic operators like “+”, “-“, “*”, “/”, and “^” for addition, subtraction, multiplication, division and raising to a power respectively.
Understand how to use the SUMPRODUCT function to multiply corresponding components in the given arrays, and returns the sum of those products.
Realise the importance of respecting the order of operations (BIDMAS), which stands for Brackets, Indices, Division and Multiplication (both left to right), Addition and Subtraction (both left to right).

Experiment with text functions like CONCATENATE (joins two or more text strings into one text string) and LEFT, RIGHT OR MID (extracts a specific number of characters from a text string, starting from the left, right, or middle).
Learn to use date and time functions such as NOW (returns the current date and time) or DAY, MONTH and YEAR (to extract the day, month, or year from a date cell).
Grasp how to use logical functions such as IF (returns one value if a condition is true and another if it’s false), AND, OR and NOT.
Familiarise yourself with lookup and reference functions like VLOOKUP and HLOOKUP to find related data in rows and columns.
Develop your skills with financial functions: the FV (future value of an investment), PV (present value of an investment), and PMT (payment for a loan) functions can all be very useful.

Understand that there may be times when formulae and functions don’t work as expected. Learn how to trace errors and troubleshoot them.
Familiarise yourself with Excel’s Formula Auditing tools, especially Trace Precedents and Trace Dependents, to see how your data is interconnected.
Recognise that optimising your formulae by keeping them clean and simple can lead to a more efficient Workbook.