Microsoft Excel Functions, Marcos & Data Commands

ASA Research

Microsoft Excel Functions, Marcos &
Data Commands

J. Carlton Collins

ASA Research - Atlanta, Georgia

770.734.0950

[e-mail]

Table of Contents

Course Information 3

Chapter 1 – All Functions 4
Chapter 2 – The If Function 17
Chapter 3 – Using Functions to Crunch & Clean Data 22
Chapter 4 – Data Commands 40
Chapter 5 - Macros 72
Chapter 6 – Miscellaneous 75
Chapter 7 – XML Data 83
Chapter 8 – Quick Tips 91
Chapter 9 – New in Excel 2007 94
Appendix - Instructor’s Biography 105

Excel Functions, Marcos & Data Commands
Course Information

Learning Objectives	To increase the productivity of accountants and CPAs using Excel’s functions, macros, & data commands
Course Level	Intermediate
Pre-Requisites	Good Familiarity with Microsoft Excel
Advanced Preparation	None
Presentation Method	Live lecture using full color projection systems and live Internet access with follow up course materials
Recommended CPE Credit	8 hours
Handouts	Templates, checklists, web examples, manual
Instructors	J. Carlton Collins, CPA
	AdvisorCPE is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be addressed to the national Registry of CPE Sponsors, 150 Fourth Avenue, Nashville, TN, 37219-2417. Telephone: [phone].

Copyright  August 2009, AdvisorCPE and Accounting Software Advisor, LLC
4480 Missendell Lane, Norcross, Georgia 30092 770.734.0450

All rights reserved. No part of this publication may be reproduced or transmitted in any form without the express written consent of AdvisorCPE, a subsidiary of ASA Research. Request may be e-mailed to [e-mail] or further information can be obtained by calling 770.734.0450 or by accessing the AdvisorCPE home page at: http://www.advisorcpe.com/

All trade names and trademarks used in these materials are the property of their respective manufacturers and/or owners. The use of trade names and trademarks used in these materials are not intended to convey endorsement of any other affiliations with these materials. Any abbreviations used herein are solely for the reader’s convenience and are not intended to compromise any trademarks. Some of the features discussed within this manual apply only to certain versions of Excel, and from time to time, Microsoft might remove some functionality. Microsoft Excel is known to contain numerous software bugs which may prevent the successful use of some features in some cases. AdvisorCPE makes no representations or warranty with respect to the contents of these materials and disclaims any implied warranties of merchantability of fitness for any particular use. The contents of these materials are subject to change without notice.
Contact Information:

J. Carlton Collins

[e-mail]

770.734.0950

Chapter 1

All Functions

Introduction to Excel Functions
Excel Functions are preprogrammed commands that make the task of writing complex formulas easier. There are a total of 333 functions in Excel. These functions are separated into 11 categories as follows:

1. 
   Database Functions (12)
   
2. 
   Date and Time Functions (20)
   
3. 
   Engineering Functions (39)
   
4. 
   Financial Functions (53)
   
5. 
   Information Functions (17)
   
6. 
   Logical Functions (6)
   
7. 
   Lookup and Reference Functions (18)
   
8. 
   Math and Trigonometry Functions (59)
   
9. 
   Statistical Functions (80)
   
10. 
    Text Functions (27)
    
11. 
    External Functions (2)
    

Some Excel functions are more powerful than others and some are more relevant to the CPA than others. For example, most CPAs will find the IF, SUM, COUNT, SUBTOTAL, TEXT, and VLOOKUP are very relevant to the CPA while other engineering and trigonometry functions such as LOG, PI, RADIENS, DELTA, TAN, COMPLEX, and HAX2DEC are typically less relevant to CPAs. It has been my experience that the following 67 functions are most relevant to the CPA; therefore CPAs wishing to increase their command of Excel functions should concentrate on these functions first.
Carlton’s List of The Top 67 Functions Most Relevant to CPAs

Sorted By Carlton’s Opinion of the Most Useful

IF	SUM	SUMIF	COUNT	COUNTA
AVERAGE	COUNTBLANK	COUNTIF	VALUE	TEXT
VLOOKUP	HLOOKUP	LOOKUP	TRIM	PROPER
LOWER	LEFT, LEFTB	MID, MIDB	RIGHT, RIGHTB	FIND, FINDB
REPLACE	CONCATENATE	CLEAN	UPPER	LEN, LENB
SUBSTITUTE	NOW	TODAY	MONTH	DATE
DAY	YEAR	WEEKDAY	ROUND	ROUNDDOWN
ROUNDUP	MAX	MIN	MEDIAN	MODE
PERCENTILE	PERCENTRANK	PMT	NPV	DSUM
DCOUNT	DCOUNTA	AND	OR	CHOOSE
TIME	FV	IRR	YIELD	CELL
ERROR.TYPE	INFO	ISBLANK	ISNA	GETPIVOTDATA
HYPERLINK	TRANSPOSE	ABS	RAND	RANDBETWEEN
CONFIDENCE	REPT