This course will give you the opportunity to build on your existing Microsoft Excel skills and gain knowledge and experience using advanced formulas and calculations in spreadsheets. You will become familiar with:
Building complex formulas
- Understand how to use nested IF statements, nested LOOKUP functions and nested formula containing INDEX, MATCH and MATCH (two-way lookup).
- Understand to creating compound logical tests by using AND, OR, NOT functions with IF statements.
- Understand reference function by using MATCH function to locate data and using INDEX function for retrieving information by location.
Advanced functions in formulas
- Understand tabulating data based on single/multiple criteria by using using COUNTIFS,SUMIFS
- Understand statistical functions, including MEDIAN, MODE, RANK, LARGE, SMALL, ROUND, MOD
- Understand how to use a variety of Financial functions such PMT, FV, IRR
- Understand smarter ways to calculate date and time by using TODAY, NETWORKDAYS, WORKDAY, DATEDIF
- Understand how to use TYPE to identify data type of existing cell contents, and apply text functions; UPPER, PROPER, FIND, MID, SEARCH, LEFT, RIGHT, LEN, and perform TRIM excess space in cells
Error check and audit
- Understand Excel error values, and how to trace formula precedents, dependents, and errors
- Understand the error checking functions ISERR, ISERROR, and IFERROR and combining IF with VLOOKUP to suppress error messages
- Understand how to use embedded Excel Array formula
- Understand how to create single cell array formulas and multi-cell array formulas
Upon completion of this course you will be well equipped to work with a range of sophisticated functions and formulas in Microsoft Excel. You will gain the necessary skills to successfully:
Be confident in using advanced statistical and complex logical functions;
Feel comfortable writing formulas to handle errors, and audit a workbook to find error sources;
Understand how to use a range of different lookup functions to cover a variety of list.
Number of questions
Enrol now to save your place
Before you go...
Would you like to subscribe to the
IT Futures newsletter?