USEFUL EXCEL FORMULAS AND FUNCTIONS

By LIVE ONLINE TRAINING

Date:

14 & 15 Oct 2024 (Every Session 9am to 5pm)

MON & TUE

Venue:

LIVE ZOOM

Share:

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 OBJECTIVES

Our Microsoft Excel Formulas course is suitable for those with an advanced working knowledge of Excel who want a greater understanding of more sophisticated formulas & functions within Excel.

You may also wish to consider one of our finance for non financial managers courses.

 

COURSE CONTENT

Day 1: 9am – 5pm

  • 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)

 

  • 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

  • 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

 

  • Introduction to Array formulas
    • Using embedded Excel Array formulas
    • Create an Array formula

 

  • 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

 

HOW TO ENQUIRY FROM US?

  1. Fill in the form below and submit to us.
  2. Initiate a conversation via live chat on the bottom left of our website by stating: “Hi, my name is [your-name], Ive already submitted the form for this training.”
  3. Well promptly reach out to you regarding the training youre interested in.

USEFUL EXCEL FORMULAS AND FUNCTIONS

ENQUIRY form

    ORGANIZATION DETAILS






    PERSON-IN-CHARGE DETAILS




    FOR FURTHER INFORMATION, PLEASE CONTACT US!