Essential Excel Formulas and Functions – Peterborough
£250.00
Description
Functions are at the core of Microsoft Excel and provide much of its power. However, many users find working with functions and formulas confusing and frustrating. This Excel training course focuses on the creation of formulas using the most useful and essential functions in each of Excel’s main function categories. Delegates will also be shown the various types of formulas and the different methods of creating them, the different ways of inserting functions and using nested functions to build complex formulas. This Excel course also covers the auditing of formulas and and the handling of errors.
Course Outline
Building formulas
Entering and editing formulas, Understanding operator precedence, Copying and moving formulas, Understanding relative reference format, Understanding absolute reference format, Copying a formula without adjusting relative references , Displaying Worksheet Formulas , Working with range names in formulas , Pasting a name into a formula , Applying names to formulas, Understanding external references
Working with functions
Inserting functions, Supplying function arguments, Supplying range arguments, Inserting nested functions
Text functions
Converting text, LOWER(), UPPER(), PROPER(), Extracting a substring, LEFT(), RIGHT(), MID(), Concatenation, Creating customer account numbers
Logical functions
Using the IF() function, Working with nested IF()s, Using AND, OR and NOT
Lookup functions
Creating lookup tables, Using VLOOKUP() and HLOOKUP(), Using range lookups, Finding exact matches, Using the CHOOSE() function
Date and time functions
How Excel handles dates and times, Construction dates with the DATE() function, Extracting date elements with DAY(), MONTH(), and YEAR() , Isolating the day, month and year , Determining the day of the week, Calculating the tme between two dates using DATEDIF(), Calculating elapsed time , Creating time sheets
Maths functions
ROUND() and MROUND() , ROUNDDOWN() and ROUNDUP() , CEILING() and FLOOR() , EVEN() and ODD() , INT() and TRUNC() , Using RANDBETWEEN() to generate data, Using RANDBETWEEN() to generate date and time values
Database functions
How database functions work, Defining the Excel database, Defining the criteria area, Using AND and OR with your criteria, The DSUM() function, The DAVERAGE() function, The DCOUNT() function, DMIN() and DMAX(), The DGET() function
Dealing with errors
Understanding Excel error values, #DIV/0!, #NAME, #REF, #NULL!, #N/A, Using conditionals to anticipate error values, Using IFERROR(), Using the formula error checker, Auditing a worksheet, Tracing cell precedents and dependents, Evaluating formulas, Watching cell values
Additional information
Choose a Date | 5 Oct 2016, 6 Dec 2016, 3 Feb 2017, 5 Apr 2017, 6 Jun 2017, 4 Aug 2017, 4 Oct 2017, 5 Dec 2017 |
---|