Course Information
Expanding Your Knowledge of Excel Functions
Parking for SAO, Professional Development courses is in Garage B (1511 San Jacinto Blvd.). The Garage signage may read 1511 San Jacinto or Garage B. The elevator in Garage B is not reliable. If you are unable to walk the stairs, please contact the professionaldevelopment@sao.texas.gov for alternate parking arrangements. Handicapped parking is free at the meters around the downtown area.
A course coordinator will Email you a parking permit prior to the course start date. A permit must be displayed or you will be ticketed.
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
Intermediate/Advanced Excel Features Beneficial for Auditors & Accountant or equivalent knowledge.
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.
If you are making travel plans to come to Austin, we recommend making "refundable" air and hotel reservations or waiting until 14 days before the class to actually book your reservations. Courses are occasionally canceled or rescheduled due to low enrollment. We determine whether a course has enough participants 16 days prior to the course date. If we cancel or reschedule, we will email the participant and his or her billing contact no later than 14 days before the original class date.
The course coordinator will contact you with parking information. Handicapped parking is free at the meters around the downtown area.
Vending machines with Coca-Cola products and various snack items are available. There is also a refrigerator and microwave in our coffee bar area. Feel free to bring in your own drinks and food if you prefer.
You might want to bring a light sweater or jacket, as room temperatures vary.
To see answers to our Frequently Asked Questions, visit http://www.sao.texas.gov/training/faq.html.