USEFUL EXCEL FORMULAS AND FUNCTIONS
Online Training by
OTC Training Centre Sdn Bhd
Inquire Now
Online
/ Training
Details
INTRODUCTION FOR USEFUL EXCEL FORMULAS AND FUNCTIONS
A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel.
Every function has the same structure. It’s not easy to remember which function and which arguments to use for each task. Fortunately, the Insert Function feature in Excel helps you with this.
COURSE OBJECTIVE
-
At the end of this course you will have a greater understanding of building complex formulas using nested functions (such as IF statements).
-
You will find quicker ways to customise formulas by taking advantage of the built-in functions available in Excel. Save time by learning more sophisticated search & auditing techniques when working with data-heavy spreadsheets.
Outline
COURSE CONTENT
Day 1: 9am – 5pm
1.0 Building complex formulas
- Using nested IF statements
- Creating compound logical tests using AND, OR, NOT functions with IF statements
- Nesting LOOKUP functions
- Source table structure information using CHOOSE function
- Using MATCH function to locate data
- The INDEX function for retrieving information by location
- Using a nested formula containing INDEX, MATCH and MATCH (two-way lookup)
2.0 Advanced functions in formulas
- Using COUNTIFS, SUMIFS & AVERAGEIFS for tabulating data based on single/multiple criteria
- Statistical functions; MEDIAN, MODE, RANK, LARGE, SMALL, ROUND, MOD
- AGGREGATE function to sum data in ranges with errors
- Use a variety of Financial functions such PMT, FV, IRR
Day 2: 9am – 5pm
3.0 Data & Text Functions
- Smarter ways to calculate date & time; TODAY
- NETWORKDAYS, WORKDAY, DATEDIF
- Use TYPE to identify data type of existing cell contents
- Text functions; UPPER, PROPER, FIND, MID, SEARCH, LEFT, RIGHT, LEN
- TRIM excess space in cells
4.0 Introduction to Array formulas
- Using embedded Excel Array formulas
- Create an Array formula
5.0 Auditing formulas
-
Tracing formula precedents, dependents, and errors
-
Correcting errors in formulas
-
Combining IF with VLOOKUP to suppress error messages
-
Using the IS information function
-
Error checking functions; ISERR, ISERROR, IFERROR
Special Offer
Register 3 Participants From Your Organization And Get The 4th Participant For Free!
The promotion expiry date for each programmes differs. For more information on the exact expiry date of the promotion, feel free to contact us.
OTC Training Centre Sdn Bhd may at any time in its sole and absolute discretion withdraw, amend and/or alter the terms and conditions of the stated promotions without prior notice.
The promotion expiry date for each programmes differs. For more information on the exact expiry date of the promotion, feel free to contact us.
OTC Training Centre Sdn Bhd may at any time in its sole and absolute discretion withdraw, amend and/or alter the terms and conditions of the stated promotions without prior notice.
Schedules
| No. of Days: | 2 |
| Total Hours: | 14 |
Reviews
Be the first to write a review about this course.
Write a Review
OTC Training Centre Sdn Bhd is an innovation leader in the development and delivery of practical outsourcing training programs for the past 17 years. Since then, OTC has supported thousands of companies to upskills their employees with personalized content and learning solutions. Today, we have evolved to provide a full suite of outsourcing services in the learning and development field.
Our training programs are entitled to HRDCorp claim, with our trainers being certified by HRDCorp as well. As a training provider, we aspire to keep providing the best training to our respected clients to ensure their satisfaction while using our service as the training provider.
Related Courses
OTC Training Centre Sdn Bhd
OTC Training Centre Sdn Bhd
OTC Training Centre Sdn Bhd
OTC Training Centre Sdn Bhd
