Course Information
Expanding Your Knowledge of Excel Functions
Course Description
In our Intermediate/Advanced Excel class we learn the more common functions used by many intermediate Excel users. This class will expand on that knowledge introducing Excel users to even more helpful functions.
This class with expand of your knowledge of Date Functions, Logical Functions, Lookup and Reference Functions, Math Functions, Statistical Functions and Text Functions. The functions taught in the class will work in most current versions of Excel such as 2013, 2016, 2019 and Microsoft 365.
Course Objectives
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.
Prerequisites
No Prerequisites Required.
Instructors
Darla Cloud has been teaching computer classes for over 27 years. She has earned the various Microsoft Office certifications acknowledging her expertise in Microsoft products. Darla is also a Certified Public Accountant and a Certified Technical Trainer. In addition to Darla’s years of teaching, she has over seven years of accounting experience. Darla’s accounting experience and love of teaching help make her an excellent trainer. She has spent years learning tips, tricks and shortcuts that she will pass on during her classes.
Additional Information
TAC Rule 523.142(g) requires the CPE Sponsor to monitor individual attendance and assign the correct number of CPE credits. Participants will be asked to document their time of arrival and departure in compliance with this Rule. Additionally, attendance will be monitored throughout the day and CPE certificates will reflect actual attendance of each participant.
To see answers to our Frequently Asked Questions, visit http://www.sao.texas.gov/training/faq.html.