DESCRIPTION
In this presentation Excel expert David H. Ringstrom, CPA shares over three dozen worksheet functions with you. The presentation leads off with an introduction to Excel's Function Wizard, a helpful tool for locating new worksheet functions and crafting formulas. David will take you through a variety of look-up functions, including VLOOKUP, HLOOKUP, MATCH, INDEX, SUMIF, SUMIFS, and more. You'll see how to create formulas that have better integrity, and go beyond the SUM function with SUBTOTAL, AGGREGATE, and SUMPRODUCT. Decision making functions include IF, CHOOSE, and IS functions. Calculate any aspect of a loan with PMT, CUMIPMT, CUMPRINC, PPMT, IPMT, and NPER. Transform text with LEFT, RIGHT, MID, UPPER, LOWER, PROPER, CLEAN, and TRIM. The presentation closes with discussions of EOMONTH and NETWORKDAYS.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 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.
Learning objectives:
- Apply the INDIRECT and OFFSET functions to create more flexible formulas.
- Define the capabilities of the SUMPRODUCT function.
- Identify how to use the SUBTOTAL function to streamline the summing of multiple areas within a spreadsheet.
Areas Covered in the Session:
- Using the SUMIFS function to sum values based on multiple criteria.
- Using the COUNTIF function to determine the number of times an item appears in a list.
- Comparing the AGGREGATE function in Excel 2010 and later to the SUBTOTAL function available in all versions of Excel.
- Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts.
- Understanding the risks and complications of nesting too many levels of IF functions.
- Avoiding the complexity of nested IF statements with Excel’s CHOOSE function.
- Utilizing the PMT worksheet function to calculate loan payment amounts.
- Utilizing the CUMIPMT worksheet function to calculate interest amounts for any period of a loan without utilizing an amortization table.
- Calculating the principal portion of a loan paid during a specific time period by way of the CUMPRINC function.
- Identifying the interest portion of a single loan payment by way of the IPMT function.
- Forecasting the number of months it will take to repay a loan based on a constant payment and constant interest rate with the NPER function.
- Using the CLEAN and TRIM functions to eliminate non-printing characters, such as tabs, carriage returns, and spaces, in your data that can trigger #VALUE! and other errors.
Who should attend:
Practitioners who may benefit from improving the integrity of their Excel spreadsheets and boost their efficiency while doing so.
