Formulas and functions, Microsoft Excel 2010 / Paul McFedries.

Format:

Book

Author/Creator:

McFedries, Paul.

Series:

MrExcel library.

MrExcel library

Language:

English

Subjects (All):

Microsoft Excel (Computer file).

Business--Computer programs.

Business.

Electronic spreadsheets.

Physical Description:

1 online resource (xv, 526 p.) : ill.

Edition:

1st edition

Other Title:

Copyright has title: Formulas and functions with Microsoft Office Excel 2010

Place of Publication:

[Indianapolis, Ind. : Que Pub., c2011]

System Details:

text file

Summary:

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 improvements–from Sparklines to the brand-new version of Solver. Along the way, you’ll discover the fastest, best ways to handle essential day-to-day tasks ranging from generating account numbers to projecting the impact of inflation.Becoming an Excel expert has never been easier! You’ll find crystal-clear instructions… insider insights… even complete step-by-step projects for building timesheets, projecting cash flow, aging receivables, analyzing defects, and more. • Create more powerful formulas • Use conditional formatting to instantly reveal anomalies, problems, or opportunities • Analyze your data with standard tables and PivotTables • Use complex criteria to filter data in lists • Understand correlations between data • Perform sophisticated what-if analyses • Use regression to track trends and make forecasts • Build loan, investment, and discount formulas • Troubleshoot problems with formulas, ranges, and functions About MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excelskills, and presents focused tasks and examples for performing them rapidly and effectively. Selectedby Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will: • Dramatically increase your productivity–saving you 50 hours a year, or more • Present proven, creative strategies for solving real-world problems • Show you how to get great results, no matter how much data you have • Help you avoid critical mistakes that even experienced users make Paul McFedries, president of Logophilia Limited, is a Microsoft Office expert and full-time technical writer. Paul has been authoring computer books since 1991 and has more than 70 books to his credit, which combined have sold more than three million copies worldwide. His recent titles include Using the Microsoft Office Web Apps, and Microsoft Windows 7 Unleashed. He is also proprietor of Word Spy (www.wordspy.com), a website that tracks new words and phrases as they enter the English language.

Contents:

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.

Notes:

Description based on print version record.

Cover title.

Publication information from p. x and p. xiv.

Description based on publisher supplied metadata and other sources.

ISBN:

9786612568954

9780789746559

0789746557

9780789743060

078974306X

9781282568952

1282568957

OCLC:

606573035