Day 4
Agenda / Tutorials
___________________________________________________________________________________________________
Transpose
Data Analysis
Load and activate the Analysis ToolPak. Click the File tab, click Options, and then click the Add-Ins category. In the Manage box, select Excel Add-ins and then click Go. In the Add-Ins box, check the Analysis ToolPak check box, and then click OK.
If you need to develop complex statistical or engineering analyses, you can save steps and time by using the Analysis ToolPak. You provide the data and parameters for each analysis, and the tool uses the appropriate statistical or engineering macro functions to calculate and display the results in an output table. Some tools generate charts in addition to output tables.
The data analysis functions can be used on only one worksheet at a time. When you perform data analysis on grouped worksheets, results will appear on the first worksheet and empty formatted tables will appear on the remaining worksheets. To perform data analysis on the remainder of the worksheets, recalculate the analysis tool for each worksheet.
The Analysis ToolPak includes the tools described in the following sections. To access these tools, click Data Analysis in the Analysis group on the Data tab. If the Data Analysis command is not available, you need to load the Analysis ToolPak add-in program.
Calculations
Templates
Agenda / Tutorials
- Formulas
- Function Library
- Data Analysis
- Calculation
- Networkdays
- Workdays
- Function Library
- Data
- Data Tools
- Comments
___________________________________________________________________________________________________
Transpose
- Select the cells containing the headings and data you want to transpose. Click in a blank cell on the spreadsheet. This cell will be the top, left corner of the new table of data. Click the down arrow on the Paste button and select Paste Special from the drop-down
Data Analysis
Load and activate the Analysis ToolPak. Click the File tab, click Options, and then click the Add-Ins category. In the Manage box, select Excel Add-ins and then click Go. In the Add-Ins box, check the Analysis ToolPak check box, and then click OK.
If you need to develop complex statistical or engineering analyses, you can save steps and time by using the Analysis ToolPak. You provide the data and parameters for each analysis, and the tool uses the appropriate statistical or engineering macro functions to calculate and display the results in an output table. Some tools generate charts in addition to output tables.
The data analysis functions can be used on only one worksheet at a time. When you perform data analysis on grouped worksheets, results will appear on the first worksheet and empty formatted tables will appear on the remaining worksheets. To perform data analysis on the remainder of the worksheets, recalculate the analysis tool for each worksheet.
The Analysis ToolPak includes the tools described in the following sections. To access these tools, click Data Analysis in the Analysis group on the Data tab. If the Data Analysis command is not available, you need to load the Analysis ToolPak add-in program.
- Histogram - non numeric
- Regression - non numeric
- What If Analysis / Nested If (GS, )
- Roster
- Additional Practice
- Calculating Days
- Data Tools
- Additional Teacher Tools
- Chart Highlights
- Tricks / Tips
- How Excel Processes things on the backend: GPA Calculator
- Writing the function
- The first argument sets up the condition the IF function will test: In this example, if the value in cell D6 is greater than or equal to 100. So our first argument will be D6>=100.
- The second argument tells the function what to write if the condition is true. In this example, if the value is greater than or equal to 100, we want it to write "0" in the cell. So our second argument will be 0.
- The third argument tells the function what to write if the condition is false: In this example, if the value is less than 100, we want it to use the value from cell G3 (5.99). So our third argument will be $G$3 (we'll use an absolute reference here just in case we move this formula in the future).
Calculations
- Calculation is the process of computing formulas and then displaying the results as values in the cells that contain the formulas. To avoid unnecessary calculations, Microsoft Excel automatically recalculates formulas only when the cells that the formula depends on have changed. This is the default behavior when you first open a workbook and when you are editing a workbook. However, you can control when and how Excel recalculates formulas.
Iteration is the repeated recalculation of a worksheet until a specific numeric condition is met. Excel cannot automatically calculate a formula that refers to the cell — either directly or indirectly — that contains the formula. This is called a circular reference. If a formula refers back to one of its own cells, you must determine how many times the formula should recalculate. Circular references can iterate indefinitely. However, you can control the maximum number of iterations and the amount of acceptable change.
Precision is a measure of the degree of accuracy for a calculation. Excel stores and calculates with 15 significant digits of precision. However, you can change the precision of calculations so that Excel uses the displayed value instead of the stored value when it recalculates formulas.
Templates
- Gant Chart
- Gradebook Percentage
- Fishbone Diagram
- Money Manager
- Control Chart
- Education Credit Tracker
- Emergency Contact
- Trip Planner
- Allergy Trigger and Symptoms Table
- Mileage Fuel Calculator
- Knowledge Management Report
- Checkbook Register
- Student Identification
- Student Task Schedule
- Problem Analysis
- Control Chart
- College Comparison