Applying Formulas and Functions

1 / 12

# Applying Formulas and Functions - PowerPoint PPT Presentation

Applying Formulas and Functions. Dr. Carolyn Rainey Professor Emeritus. 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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Applying Formulas and Functions' - jerom

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Applying Formulas and Functions

Dr. Carolyn Rainey

Professor Emeritus

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

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

Applying Formulas and Functions
• To participate in this live demonstration: Start a new document
• Operators
• Use keyboard
• Use selection method
• Use relative, absolute, mixed cell references
• Use 3D references

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

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

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

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

Auditing Functions
• Trace Precedents, Dependents
• Audit Formulas
• Evaluate Formula
• Watch Window
• Trace Error
• Message Window

Excel Functions
• Consolidate Data

Example: =SUM(B5:B8)+‛[Source Workbook.xlsx]QTR1 Revenue’!\$B\$5

• Track Changes
• Share the workbook
• Edit a cell
• Accept/reject changes

Excel Functions
• Goal Seek