Skip to main content

Expanding Your Knowledge of Excel Functions

Back to Course Schedule
Date(s): Nov 04, 2021
Time: 8:00AM - 4:00PM
Registration Fee: $99.00
Cancellation Date: Oct 25, 2021
Location: Online

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.


Potential CPE Credits: 7.0

Instruction Type: Live
Experience Level:
Category: Computer Software and Applications

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

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.

 


Back to Course Schedule