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