Formulas and Functions with Excel - CDTL（Excel - CDTL公式和函数）.pdf

Formulas and Functions with Excel Kiruthika Ragupathi Assistant Director (Educational Technology) Centre for Development of Teaching and Learning (CDTL) National University of Singapore email: kiruthika@.sg TABLE OF CONTENTS Introduction 4 Exploring the Excel Workspace 4 Navigating the Workbook4 Setting up Data6 Inserting a new column or row6 Formatting the worksheet 6 Verifying Data 8 Formulas and Functions8 Constructing a formula 8 Inserting a function 10 Built-in Functions 12 Mathematical Calculations 14 Conditional Calculations 17 Cutting up Piecing together Text Strings using Text functions 18 Using Look up tables / References 18 Pivot Tables20 Create a PivotTable from worksheet data 21 Statistical functions 22 Basic Statistical built-in functions 22 Linear regression functions23 Statistical Analysis 24 Using the Analysis ToolPak 25 Descriptive Statistics 26 More Information28 Synopsis Need to perform complex calculations and data analysis? The usage of formulas and functions is what gives an Excel spreadsheet much of its power. This workshop explores the formula and functions that are useful for computing and managing data. This course begins with mathematical principles and moves on to basic formula construction. Participants will learn about mathematical functions, statistical functions, conditional calculations, and lookup tables. In addition, the use of Analysis ToolPak will be discussed aiding in the generation of descriptive statistics and regression. Objectives By the end of this workshop, participants should be able to: • Set up and verify data • Understand the mathematical order