Formulas and functions, Microsoft Excel 2010
MASTER CORE EXCEL 2010 TOOLS FOR BUILDING POWERFUL, RELIABLE SPREADSHEETS! Excel expert Paul McFedries shows how to use Excel 2010’s core features to solve problems and get the answers you need! Using real-world examples, McFedries helps you get the absolute most out of breakthrough Excel 2010 impro...
| Autor principal: | |
|---|---|
| Formato: | Libro electrónico |
| Idioma: | Inglés |
| Publicado: |
[Indianapolis, Ind. :
Que Pub
c2011]
|
| Edición: | 1st edition |
| Colección: | MrExcel library.
|
| Materias: | |
| Ver en Biblioteca Universitat Ramon Llull: | https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009628133506719 |
Tabla de Contenidos:
- Cover
- Contents
- Introduction
- What's in the Book
- This Book's Special Features
- I: MASTERING EXCEL RANGES AND FORMULAS
- 1 Getting the Most Out of Ranges
- Advanced Range-Selection Techniques
- Data Entry in a Range
- Filling a Range
- Using the Fill Handle
- Creating a Series
- Advanced Range Copying
- Copying Selected Cell Attributes
- Clearing a Range
- Applying Conditional Formatting to a Range
- 2 Using Range Names
- Defining a Range Name
- Working with Range Names
- 3 Building Basic Formulas
- Understanding Formula Basics
- Understanding Operator Precedence
- Controlling Worksheet Calculation
- Copying and Moving Formulas
- Displaying Worksheet Formulas
- Converting a Formula to a Value
- Working with Range Names in Formulas
- Working with Links in Formulas
- Formatting Numbers, Dates, and Times
- 4 Creating Advanced Formulas
- Working with Arrays
- Understanding Array Formulas
- Using Array Constants
- Using Iteration and Circular References
- Consolidating Multisheet Data
- Applying Data-Validation Rules to Cells
- Using Dialog Box Controls on a Worksheet
- 5 Troubleshooting Formulas
- Understanding Excel's Error Values
- Fixing Other Formula Errors
- Handling Formula Errors with IFERROR( )
- Using the Formula Error Checker
- Auditing a Worksheet
- II: HARNESSING THE POWER OF FUNCTIONS
- 6 Understanding Functions
- About Excel's Functions
- The Structure of a Function
- Typing a Function into a Formula
- Using the Insert Function Feature
- Loading the Analysis ToolPak
- 7 Working with Text Functions
- Excel's Text Functions
- Working with Characters and Codes
- Converting Text
- Formatting Text
- Manipulating Text
- Removing Unwanted Characters from a String
- Extracting a Substring
- Searching for Substrings
- Case Study: Generating Account Numbers.
- Substituting One Substring for Another
- Case Study: Generating Account Numbers, Part 2
- 8 Working with Logical and Information Functions
- Adding Intelligence with Logical Functions
- Case Study: Building an Accounts Receivable Aging Worksheet
- Getting Data with Information Functions
- 9 Working with Lookup Functions
- Understanding Lookup Tables
- The CHOOSE( ) Function
- Looking Up Values in Tables
- 10 Working with Date and Time Functions
- How Excel Deals with Dates and Times
- Using Excel's Date Functions
- Using Excel's Time Functions
- Case Study: Building an Employee Time Sheer
- 11 Working with Math Functions
- Understanding Excel's Rounding Functions
- Case Study: Rounding Billable Time
- Summing Values
- MOD( ) Function
- Generating Random Numbers
- 12 Working with Statistical Functions
- Understanding Descriptive Statistics
- Counting Items with the COUNT( ) Function
- Calculating Averages
- Calculating Extreme Values
- Calculating Measures of Variation
- Working with Frequency Distributions
- Using the Analysis ToolPak Statistical Tools
- III: BUILDING BUSINESS MODELS
- 13 Analyzing Data with Tables
- Converting a Range to a Table
- Basic Table Operations
- Sorting a Table
- Filtering Table Data
- Referencing Tables in Formulas
- Excel's Table Functions
- Case Study: Applying Statistical Table Functions to a Defects Database
- 14 Analyzing Data with PivotTables
- What Are PivotTables?
- Building PivotTables
- Working with PivotTable Subtotals
- Changing the Data Field Summary Calculation
- Creating Custom PivotTable Calculations
- Case Study: Budgeting with Calculated Items
- Using PivotTable Results in a Worksheet Formula
- 15 Using Excel's Business-Modeling Tools
- Using What-If Analysis
- Working with Goal Seek
- Working with Scenarios.
- 16 Using Regression to Track Trends and Make Forecasts
- Setting Up and Performing a Find
- Choosing a Regression Method
- Using Simple Regression on Linear Data
- Case Study: Trend Analysis and Forecasting for a Seasonal Sales Model
- Using Simple Regression on Nonlinear Data
- Using Multiple Regression Analysis
- 17 Solving Complex Problems with Solver
- Some Background on Solver
- Loading Solver
- Using Solver
- Adding Constraints
- Saving a Solution as a Scenario
- Setting Other Solver Options
- Making Sense of Solver's Messages
- Case Study: Solving the Transportation Problem
- Displaying Solver's Reports
- IV: BUILDING FINANCIAL FORMULAS
- 18 Building Loan Formulas
- Understanding the Time Value of Money
- Calculating the Loan Payment
- Building a Loan Amortization Schedule
- Calculating the Term of the Loan
- Calculating the Interest Rate Required for a Loan
- Calculating How Much You Can Borrow
- Case Study: Working with Mortgages
- 19 Building Investment Formulas
- Working with Interest Rates
- Calculating the Future Value
- Working Toward an Investment Goal
- Case Study: Building an Investment Schedule
- 20 Building Discount Formulas
- Calculating the Present Value
- Discounting Cash Flows
- Calculating the Payback Period
- Case Study: Publishing a Book
- Index.
