Table of Contents

 

Chapter 1 - Must Know Excel Functions for CPAs – Section 1. 7

Introduction. 7

The SUM Function. 12

The SUBTOTAL Function. 13

The SUMPRODUCT Function. 22

The DSUM Function. 28

The SUMIF Function. 36

The SUMIFS Function. 43

Chapter 1 – Section 1 - Review Questions. 45

Chapter 1 - Must Know Excel Functions for CPAs – Section 2. 46

The COUNT Function. 46

The COUNTA Function. 47

The COUNTBLANK Function. 48

The COUNTIF and COUNTIFS Functions. 49

Using COUNTIF to Find Duplicates 51

The COUNTIFS Function. 53

The DCOUNT Function. 55

The AGGREGATE Function. 59

Chapter 1 – Section 2 - Review Questions. 64

Chapter 1 - Must Know Excel Functions for CPAs – Section 3. 65

Logic Functions. 65

Boolean Logic. 70

Logical Functions for Selection of Items 76

Locating Formula Exceptions 85

Handling Formula Exceptions. 89

Chapter 1 – Section 3 - Review Questions. 92

Chapter 1 - Must Know Excel Functions for CPAs – Section 4. 93

Working with Text 93

The Index and Match Functions 100

The Vlookup Function. 107

The DATEVALUE Function. 111

The NOW() Function. 112

Chapter 1 – Section 4 - Review Questions. 114

Chapter 1 - Must Know Excel Functions for CPAs – Section 5. 115

Time Value of Money Functions 115

Present Value of a Single Sum.. 117

Future Value of a Single Sum.. 123

Present Value of Annuities. 128

Future Value of Annuities. 132

Computing the Periodic Payment 136

Computing the Rate. 143

Computing Number of Periods 147

Chapter 1 – Section 5 - Review Questions. 151

Chapter 1 - Must Know Excel Functions for CPAs – Section 6. 152

New Functions in Excel 2013. 152

New Functions in Excel 2016. 155

Comprehensive Case Study Exercises. 161

Suggested Answers to Comprehensive Case Study Exercises 162

Answers to Review Questions 168

Glossary. 180

Index. 181

Exam Score Sheet ………………………………………………………………………………………182

CPE Exam.. 183