applying formulas and functions n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Applying Formulas and Functions PowerPoint Presentation
Download Presentation
Applying Formulas and Functions

Loading in 2 Seconds...

play fullscreen
1 / 12

Applying Formulas and Functions - PowerPoint PPT Presentation


  • 121 Views
  • Uploaded on

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

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

PowerPoint Slideshow about 'Applying Formulas and Functions' - jerom


An Image/Link below is provided (as is) to download presentation

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

Applying Formulas and Functions

Dr. Carolyn Rainey

Professor Emeritus

College of Business Excel Workshops

what is microsoft certification
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

overview of workshops
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

applying formulas and functions1
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

applying formulas and functions2
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

using the function dialog box
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

using the function dialog box1
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

using the function dialog box2
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

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

College of Business Excel Workshops

excel functions
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

excel functions1
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

sample certiprep items
Sample CertiPrep Items

Participate in CertiPrep simulation of exam questions.

College of Business Excel Workshops