MATH298B TUTORIAL PACKET

MATH298B TUTORIAL PACKET Excel Equation Editor PowerPoint Word Blank Page Inserted for Double −Sided Printing  TABLE OF CONTENTS PART 1: Microsoft Excel Tutorial PART 2: Equation Editor Tutorial PART 3: Microsoft PowerPoint Tutorial PART 4: Microsoft Word Tutorial Please note that each Part has its own page −numbering system. Blank Page Inserted for Double −Sided Printing  COVER PAGE MATH298B Excel TUTORIAL 2003 and beyond, Lawrence Morales Excel Tutorial - Page 1 Intentionally blank for double-sided copies. Excel Tutorial - Page 2  TABLE OF CONTENTS COVER PAGE ................................................................................................................................................................. 1 TABLE OF CONTENTS................................................................................................................................................. 3 BASIC EXCEL FACTS AND USAGE........................................................................................................................... 5 BASIC INTRODUCTIONS TO A SPREADSHEET................................................................................................................... 5 ENTERING DATA............................................................................................................................................................. 6 EDITING CELL CONTENTS................ 8 Changing Cell Formats............................................................................................................................................. 8 Changing Row and Column Sizes ........................................................................................................................... 11 Contents.......................................................................................................................................... 12 WORKING WITH FORMULAS.......... 12 COPYING FORMULAS..................... 12 ABSOLUTE REFERENCES................ 16 SORTING DATA.............................. 18 BASIC MATHEMATICS COMPUTATIONS............................................................................................................ 25 BASIC MATHEMATICAL FUNCTIONS............................................................................................................................. 25 Addition................................................................................................................................................................... 25 Subtraction.............................................................................................................................................................. 26 Multiplication.......................................................................................................................................................... 27 Division 27 Powers and Exponents............................................................................................................................................ 28 The Natural Base, e................................................................................................................................................. 29 Square Roots........................................................................................................................................................... 30 The Natural Logarithm .......................................................................................................... 30 The Value of π......................................................................................................................................................... 31 Rounding................................................................................................................................................................. 31 Rounding Up and Rounding Down ......................................................................................................................... 32 BASIC STATISTICAL FUNCTIONS.... 33 Averages and Means............................................................................................................................................... 33 Sums........................................................................................................................................................................ 33 Minimums......................................... 33 Maximums........................................ 34 The Range ...................................................................................................................... 34 DATABASES............................................ 34 BASIC INTRODUCTION TO DATABASES ......................................................................................................................... 34 THE DCOUNT COMMAND............ 36 FILTERING .................................................................................................................................................................... 41 THE DAVERAGE, DSUM, DMIN, DMAX COMMANDS ............................................................................................ 43 SPECIAL EXCEL FUNCTIONS.................................................................................................................................. 45 THE COUNT COMMAND............... 45 THE IF COMMAND............ 46 THE RAND COMMAND.................. 48 THE RANDBETWEEN COMMAND.............................................................................................................................. 49 THE IF COMMAND......................... 50 THE VLOOKUP COMMAND.......... 56 THE HLOAND 58 THE FUNCTION WIZARD AND THE VLOOKUP AND HLOOKUP COMMANDS.............................................................. 58 THE INDEX COMMAND................. 60 OPTIONAL: HOW I BUILT THE PHONE LOG FILE ........................................................................................................... 62 Excel Tutorial - Page 3 GRAPHING.................................................................................................................................................................... 63 BASIC GRAPHING ......................................................................................................................................................... 63 GRAPHING FUNCTIONS.................. 77 GHISTOGRAMS............... 82 Bins and Midpoints ................................................................................................................................................. 84 Histogram Wizard............................................................................................................... 87 Formatting the Histogram ...................................................................................................................................... 88 TRENDLINES................................................................................................................................................................ 93 Excel Tutorial - Page 4  Basic Excel Facts and Usage Basic Introductions to a Spreadsheet A spreadsheet is a software tool that allows you to work with data (mainly numerical) in very complex and helpful ways. In MAT116 and MAT117, Excel will be used extensively to complete the projects in each of the course. Hence, it is crucial that you have a basic understanding of how spreadsheets work. Here is a basic screen shot of Excel. Please take time to read the callouts to learn some basic spreadsheet vocabulary. These two rows are called Toolbars. These are the Menus used This particular button calls up the to issue commands. Function Wizard. This is the formula bar where you enter and edit formulas. The Address Box tells you what cell Each of these boxes is called a “Cell.” Cells contain, is currently active. numbers, text, formulas, or even pictures. A cell’s You can enter in a address is given by the intersection of the column letter cell address to go and the row number. This particular cell’s address is to directly to a cell, B3. When you click on or move to a cell, its border or use it to give a will become highlighted, as shown here. cell a specific name. These tabs select Sheets that you can work in. The current sheet being worked in is in bold letters. This class will often use files with several sheets included so pay attention to whether or not these are labeled or named to indicate more than one sheet exists. Excel Tutorial - Page 5 A spreadsheet may be used to quickly process numerical data, like the simple sales tax sheet shown below: Or, they could be used to simulate complex random sampling situations for complicated and intricate mathematically based problems, such as those we will encounter in MAT116 and MAT117. Entering Data Entering data into a spreadsheet is as simple as clicking once on a cell and entering the data. You can also use the arrow keys on your keyboard to navigate to a cell. The active cell will be highlighted and its address will appear in the Address Box. If you have not opened up Excel yet, please do so now by opening the file, Blank.xls. Practice moving around the cells if you are new to a spreadsheet. (In this tutorial, any text that is blue and underlined is a link to an existing file. To open the link, click on it. If that does not work, try to hold down the Ctrl button on your keyboard while you click once on a link.) The simplest kind of data to input is text and numbers. Practice Open Navigate to Cell A1 and type in the words “Tax Rate” followed by the Enter key. In Cell B1, type in the number .0865 (which corresponds to 8.65%). Continue to edit your worksheet until it looks like this…