For full functionality of this site it is necessary to enable JavaScript. Here are the instructions how to enable JavaScript in your web browser.

Advanced Formulas & Functions in Excel: Solve Your Data Challenges

David H. Ringstrom, CPA Keynote Speaker at Ijona Skills. He is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. David's mantra is "Either you work Excel, or it works you," so he focuses on what he sees users don't, but should, know about Microsoft Excel. His goal is to empower you to use Excel more effectively.

Read More

Overview

Excel expert David Ringstrom, CPA, shares helpful tweaks you can use with the venerable VLOOKUP function. In this invaluable presentation, he explains alternatives to VLOOKUP, including the INDEX and MATCH, SUMIF, SUMIFS, SUMPRODUCT, IFNA, and OFFSET functions. Lookup formulas are far superior to manually searching for specific data elements in a spreadsheet. Many users rely on VLOOKUP to return data from other locations in a worksheet, but because using VLOOKUP isn't always the most efficient approach, David explains alternatives.

Why should you attend?

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Office 365. He'll draw to your attention any differences in Excel 2019, 2016, 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.

Areas Covered in the Session:

  • Using the SUMIFS function to sum values based on multiple criteria.
  • Learning about the IFNA function available in Excel 2013 and later.
  • Transforming numbers stored as text into values by way of the Text to Columns wizard.
  • Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts.
  • Enabling VLOOKUP to look up data from the left (instead of only from the right) by using the CHOOSE function.
  • Identifying situations where VLOOKUP may return #N/A instead of a value.
  • Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
  • Using the SUMIFS function to sum values based on multiple criteria.
  • Utilizing Excel's IFERROR function to display alternate values when VLOOKUP returns an error.
  • Future-proofing VLOOKUP by using Excel's Table feature versus referencing static ranges.
  • Eliminating inputs that could cause VLOOKUP to return #N/A with Data Validation.
  • Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces.
Learning objectives:
  • Apply a variety of lookup formulas in order to work more efficiently in Excel.
  • Recognize why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP.
  • Apply the SUMIF and SUMIFS functions.

Who can Benefit:

  • Accountants
  • CPAs
  • CFOs
  • Controllers
  • Excel User
  • Income Tax Preparers
  • Enrolled Agents
  • Financial Consultants
  • IT Professionals
  • Auditors
  • Human Resource Personnel
  • Bookkeepers
  • Marketers
  • Government Personnel

Webinar Id: ISDR0010

Duration: 60 mins

Ratings:
No reviews yet!!

   

Refund Policy

Webinars of David H. Ringstrom
Mastering Excel Pivot Tables: How to Crunch...
Presenter: David H. Ringstrom

View Anytime
Price: $167
Status: Archived

Excel Finesse: Macros - Part 1
Presenter: David H. Ringstrom

View Anytime
Price: $167
Status: Archived

Webinars of Finance
Business Writing for Results
Presenter: Audrey Halpern

View Anytime
Price: $167
Status: Archived

Transforming Anger and Conflict into Collab...
Presenter: Mark Gorkin

View Anytime
Price: $167
Status: Archived


top
l