Excel – Math, Formulas and Functions – Part I
Registration Fee – $49
May 20, 2021 – 2:00 pm – 4:00 pm (2.0 CPE)
Prerequisites: A good understanding of Excel basics
Advance Preparation: None
Field of Study: Technical
Presented by: Jim Colville, CPA Retired, CFE
Handout Materials: All files and notes available for download. See below
This course will start with the basics and move quickly to the more advanced concepts and problem solving concepts using Excel formulas and functions. This course assumes the attendee has a working knowledge of formulas and functions and begins with a review of the foundations to ensure the user is taking advantage of the full power of these features. All exercises will use real world problems and how to save time and reduce errors. All attendees will learn new concepts regardless of their current skill level.
This topic can be quite extensive and, as such, we’ve put it in four separate courses. The courses are not dependent on each other and each contains different Excel features. They can be taken in any order. As with all of our exercises, they are based on real world problems and how to effectively use them to achieve maximum efficiency including Zero Time Reporting and to help the attendee to “excel at Excel”.
- Understand what goes in a cell and how it effects calculations
- The real power of AutoSum, beyond the basics, including the Sub Total function
- Introduction to embedded functions and mega formulas
- CountIfs, SumIfs, and related functions
- Excel LookUp functions, VLookUp, HLookUp, LookUp, Index and Match and related concepts
- Excel If function, introduction
- Dates, introduction
- Offset function, introduction, updating budget with actual simply by updating the report date
- Relative and Absolute cell references
- Formula creation using defined names adding clarity to what the formula is doing
- Error trapping, formulas and concepts to help make your spreadsheet error free
- Effect of inserting rows on the =SUM and other formulas
The If and Date functions in this session are an integral part of the other exercises. As such they will be introduced so the attendee sees the entire objective and solution. Part 2 dives into If, Dates, DatedIf, Offset, embedded functions and mega formulas and covers them in-depth.
All the files used in presenting these topics will be available for download after the webinar. The files will contain instruction on how to use the concept which include the presenter’s notes. The files will remain available online for about two weeks after the webinar.