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