Applying Formulas and Functions

1. Applying Formulas and Functions Dr. Carolyn Rainey Professor Emeritus College of Business Excel Workshops

2. What is Microsoft Certification? • Microsoft Office Specialist (MOS) • www.certiport.com • http://www.certiport.com/Portal/desktopdefault.aspx?page=common/pagelibrary/MOS_whatIs.html • Microsoft Office 2010 Exam Objectives • Exam voucher • http://www.measureup.com/Microsoft-Office-Specialist-MOS-200720102013-Exam-Voucher-P3195.aspx College of Business Excel Workshops

3. Overview of Workshops • Workshop One (10/22) - Managing the Worksheet Environment • Workshop Two (10/24) - Creating Cell Data, Formatting/Revising Worksheets/Workbooks • Workshop Three (10/29) - Applying Formulas and Functions • Workshop Four (10/31) - Presenting Data Visually • Workshop Five(11/5) - Data Analysis College of Business Excel Workshops

4. Applying Formulas and Functions • To participate in this live demonstration: Start a new document • Create formulas: start with an equal sign • Operators • Use keyboard • Use selection method • Use relative, absolute, mixed cell references • Use 3D references College of Business Excel Workshops

5. Applying Formulas and Functions • Use Sum icon to add numbers • Use Sum icon to choose Average, Count, Max and Min; also available in Formulas group • Use AutoCalculate • Review Circular reference • Display formulas using Control plus tilde College of Business Excel Workshops

6. Using the Function Dialog Box • Open BlueLakeSports-03 fromhttp://highered.mcgraw-hill.com/sites/0073519383/student_view0/data_files.html • MAX, MIN, MEAN • TODAY, DATE, NOW • RANK.AVG • PMT College of Business Excel Workshops

7. Using the Function Dialog Box • IF function • Nested IF conditions • COUNTA—counts anything that is not blank • COUNT—counts dates and numeric entries • COUNTIF—counts cells that meet one criterion • COUNTIFS—counts cells that meet multiple criteria College of Business Excel Workshops

8. Using the Function Dialog Box • AVERAGEIF—finds the arithmetic mean of the cells in a specified range that meet a single criterion. • AVERAGEIFS • SUMIF • SUMIFS—used to add cells that meet multiple criteria • VLOOKUP and HLOOKUP College of Business Excel Workshops

9. Auditing Functions • Trace Precedents, Dependents • Audit Formulas • Evaluate Formula • Watch Window • Trace Error • Message Window College of Business Excel Workshops

10. Excel Functions • Consolidate Data • Link Data Example: =SUM(B5:B8)+‛[Source Workbook.xlsx]QTR1 Revenue’!$B$5 • Track Changes • Share the workbook • Edit a cell • Accept/reject changes College of Business Excel Workshops

11. Excel Functions • Goal Seek • Load Excel file: Whitney • Use Data Tab, What-if Analysis, click Goal Seek • Set cell B14 to a Value of 72; cell B10 to change • Scenarios • Add scenarios • Create/edit Scenario Report College of Business Excel Workshops

12. Sample CertiPrep Items Participate in CertiPrep simulation of exam questions. College of Business Excel Workshops