Upon completion of this course, participants will be able to:
Use and build more advanced Excel Functions.
Detailed Course Outline:
Lesson 1 – Date Functions
Understanding the Function Library
Reviewing Absolute References
Working with Dates in Excel
DATE - Turning non-dates into dates
EDATE - Calculate a date on the same day of the month, x months in the past or future
NETWORKDAYS - Returns the number of whole working days between a start date and an end date.
WEEKDAY - Returns a number between 1-7 representing the day of week.
WORKDAY - Calculate the number of days from a start date excluding weekends and possible holidays.
Lesson 2 – Logical Functions
AND - A logical function used to determine if all conditions in a test are TRUE.
Using the Evaluate Functions Auditing Tool
OR - A logical function used to determine if any conditions in a test are TRUE.
IFERROR - Returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.
Lesson 3 – Lookup and Reference Functions
FORMULATEXT – Displays the formula
INDIRECT - Returns the reference specified by a text string
TRANSPOSE - Changes the layout of data, from horizontal to vertical, or from vertical to horizontal
Compare the Transpose function to the Copy and Paste Transpose
INDEX - Returns a value or the reference to a value from within a table or range
MATCH - Searches for a specified item in a range of cells, and then returns the relative position of that item in the range.
Lesson 4 – Math Functions
CEILING.MATH – Rounds a number up to the nearest integer or to the nearest multiple of significance
FLOOR.MATH – Rounds a number down to the nearest integer or to the nearest multiple of significance
INT – Returns the integer part of a decimal number
RAND -Returns an evenly distributed random real number greater than or equal to 0 and less than 1
RANDBETWEEN - Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated
SUMIF - Returns the sum of cells that meet a single condition
SUMPRODUCT - Returns the sum of the products of corresponding ranges or arrays.
Lesson 5 – Statistical Functions
RANK.AVG – Returns the rank of a number in a list
RANK.EQ – Also Returns the rank of a number in a list
SMALL - Returns the k-th smallest value in a data set
LARGE - Returns the k-th largest value in a data set
Lesson 6 – Text Functions
TRIM - Removes all spaces from text except for single spaces between words
TEXT – This function allows you change the way a number appears by applying formatting to it with format codes
SUBSTITUTE - Substitutes new text for old text in a text string
REPLACE - Replaces part of a text string, based on the number of characters you specify, with a different text string.