SINGAPORE
Law
Welcome Guest | ログイン
検索
詳細検索
Formulas and Functions with Microsoft Office Excel 2007: McFedries, Paul: BOOKS KINOKUNIYA
詳細
Formulas and Functions with Microsoft Office Excel 2007
Formulas and Functions with Microsoft Office Excel 2007
著者名 McFedries, Paul
出版社 : Que Pub
出版年月 : 2007/03
Binding : Paperback
ISBN : 9780789736680

BookWeb価格 : S$ 67.27
会員価格 : S$ 60.55

在庫情報 : 在庫がありません。
出版社等に在庫の有無を確認します。詳細がわかり次第、発送予定日をメールにてご連絡いたします。
言語 : English

内容情報
Source: ENG
Academic Descriptors: A93905800
Place of Publication: United States
Textual Format: Computer Applications
Academic Level: Extracurricular
Review:
School Selection Guide Core Issue: Nonfiction Titles To Order 7-12 (2007-08)
School? Selection Guide Core Issue: All Titles 7-12 (2007-08)
Baker & Taylor Newly Released Computer Titles - January 2002 - Ongoing
Baker & Taylor Best-seller And Forthcoming Computer Titles - January 2000 - Ongoing
CLS Opening Day Collections Audit-June 2002 (And Ongoing)
Table of Contents
 
Introduction                                       1  (1)
      What's in the Book                           2  (1)
      This Book's Special Features                 2  (5)
  I. MASTERING EXCEL RANGES AND FORMULAS
    Getting the Most Out of Ranges                 7  (30)
      Advanced Range-Selection Techniques          7  (8)
        Mouse Range-Selection Tricks               8  (1)
        Keyboard Range-Selection Tricks            9  (1)
        Working with 3D Ranges                     9  (1)
        Selecting a Range Using Go To              10 (1)
        Using the Go To Special Dialog Box         11 (4)
      Data Entry in a Range                        15 (1)
      Filling a Range                              16 (1)
      Using the Fill Handle                        16 (3)
        Using AutoFill to Create Text and          16 (2)
        Numeric Series
        Creating a Custom AutoFill List            18 (1)
        Filling a Range                            19 (1)
      Creating a Series                            19 (1)
      Advanced Range Copying                       20 (3)
        Copying Selected Cell Attributes           20 (2)
        Combining the Source and Destination       22 (1)
        Arithmetically
        Transposing Rows and Columns               23 (1)
      Clearing a Range                             23 (1)
      Applying Conditional Formatting to a Range   24 (13)
        Creating Highlight Cells Rules             24 (2)
        Creating Top/Bottom Rules                  26 (2)
        Adding Data Bars                           28 (3)
        Adding Color Scales                        31 (2)
        Adding Icon Sets                           33 (2)
        From Here                                  35 (2)
    Using Range Names                              37 (18)
      Defining a Range Name                        38 (7)
        Working with the Name Box                  38 (1)
        Using the New Name Dialog Box              39 (2)
        Changing the Scope to Define               41 (1)
        Sheet-Level Names
        Using Worksheet Text to Define Names       41 (3)
        Naming Constants                           44 (1)
      Working with Range Names                     45 (10)
        Referring to a Range Name                  46 (1)
        Working with Name AutoComplete             47 (1)
        Navigating Using Range Names               48 (1)
        Pasting a List of Range Names in a         48 (1)
        Worksheet
        Displaying the Name Manager                48 (1)
        Filtering Names                            49 (1)
        Editing a Range Name's Coordinates         49 (1)
        Adjusting Range Name Coordinates           50 (1)
        Automatically
        Changing a Range Name                      51 (1)
        Deleting a Range Name                      52 (1)
        Using Names with the Intersection          52 (1)
        Operator
        From Here                                  53 (2)
    Building Basic Formulas                        55 (34)
      Understanding Formula Basics                 55 (4)
        Formula Limits in Excel 2007               56 (1)
        Entering and Editing Formulas              56 (1)
        Using Arithmetic Formulas                  57 (1)
        Using Comparison Formulas                  58 (1)
        Using Text Formulas                        59 (1)
        Using Reference Formulas                   59 (1)
      Understanding Operator Precedence            59 (3)
        The Order of Precedence                    60 (1)
        Controlling the Order of Precedence        60 (2)
      Controlling Worksheet Calculation            62 (2)
      Copying and Moving Formulas                  64 (3)
        Understanding Relative Reference Format    65 (1)
        Understanding Absolute Reference Format    66 (1)
        Copying a Formula Without Adjusting        66 (1)
        Relative References
      Displaying Worksheet Formulas                67 (1)
      Converting a Formula to a Value              67 (1)
      Working with Range Names in Formulas         68 (4)
        Pasting a Name into a Formula              68 (1)
        Applying Names to Formulas                 69 (3)
        Naming Formulas                            72 (1)
      Working with Links in Formulas               72 (3)
        Understanding External References          73 (1)
        Updating Links                             74 (1)
        Changing the Link Source                   75 (1)
      Formatting Numbers, Dates, and Times         75 (14)
        Numeric Display Formats                    76 (7)
        Date and Time Display Formats              83 (3)
        Deleting Custom Formats                    86 (1)
        From Here                                  87 (2)
    Creating Advanced Formulas                     89 (24)
      Working with Arrays                          89 (6)
        Using Array Formulas                       90 (3)
        Using Array Constants                      93 (1)
        Functions That Use or Return Arrays        94 (1)
      Using Iteration and Circular References      95 (2)
      Consolidating Multisheet Data                97 (5)
        Consolidating by Position                  97 (4)
        Consolidating by Category                  101(1)
      Applying Data-Validation Rules to Cells      102(3)
      Using Dialog Box Controls on a Worksheet     105(8)
        Using the Form Controls                    105(1)
        Adding a Control to a Worksheet            106(1)
        Linking a Control to a Cell Value          106(1)
        Understanding the Worksheet Controls       107(4)
        From Here                                  111(2)
    Troubleshooting Formulas                       113(20)
      Understanding Excel's Error Values           114(4)
        #DIV/0!                                    114(1)
        #N/A                                       115(1)
        #NAME?                                     115(1)
        Avoiding #Name? Errors When Deleting       116(1)
        Range Names
        #Null!                                     117(1)
        #Num!                                      117(1)
        #Ref!                                      117(1)
        #Value!                                    118(1)
      Fixing Other Formula Errors                  118(3)
        Missing or Mismatched Parentheses          118(1)
        Erroneous Formula Results                  119(1)
        Fixing Circular References                 120(1)
      Handling Formula Errors with IFERROR()       121(1)
      Using the Formula Error Checker              122(4)
        Choosing an Error Action                   123(1)
        Setting Error Checker Options              123(3)
      Auditing a Worksheet                         126(7)
        Understanding Auditing                     126(1)
        Tracing Cell Precedents                    127(1)
        Tracing Cell Dependents                    127(1)
        Tracing Cell Errors                        127(1)
        Removing Tracer Arrows                     128(1)
        Evaluating Formulas                        128(1)
        Watching Cell Values                       129(1)
        From Here                                  129(4)
  II. HARNESSING THE POWER OF FUNCTIONS
    Understanding Functions                        133(10)
      About Excel's Functions                      134(1)
      The Structure of a Function                  134(2)
      Typing a Function into a Formula             136(2)
      Using the Insert Function Feature            138(2)
      Loading the Analysis ToolPak                 140(3)
        From Here                                  141(2)
    Working with Text Functions                    143(24)
      Excel's Text Functions                       144(1)
      Working with Characters and Codes            145(4)
        The Char() Function                        145(3)
        The Code() Function                        148(1)
      Converting Text                              149(1)
        The Lower() Function                       149(1)
        The Upper() Function                       149(1)
        The Proper() Function                      149(1)
      Formatting Text                              150(2)
        The Dollar() Function                      150(1)
        The Fixed() Function                       151(1)
        The Text() Function                        151(1)
        Displaying When a Workbook Was Last        151(1)
        Updated
      Manipulating Text                            152(1)
      Removing Unwanted Characters from a String   152(3)
        The Trim() Function                        152(1)
        The Clean() Function                       153(1)
        The Rept() Function: Repeating a           153(1)
        Character
        Padding a Cell                             154(1)
        Building Text Charts                       154(1)
      Extracting a Substring                       155(3)
        The Left() Function                        156(1)
        The Right() Function                       156(1)
        The Mid() Function                         156(1)
        Converting Text to Sentence Case           156(1)
        A Date-Conversion Formula                  157(1)
      Generating Account Numbers                   158(1)
      Searching for Substrings                     158(4)
        The Find() and Search() Functions          158(1)
        Extracting a First Name or Last Name       159(1)
        Extracting First Name, Last Name, and      160(1)
        Middle Initial
        Determining the Column Letter              161(1)
      Substituting One Substring for Another       162(3)
        The Replace() Function                     162(1)
        The Substitute() Function                  163(1)
        Removing a Character from a String         163(1)
        Removing Two Different Characters from     164(1)
        a String
        Removing Line Feeds                        164(1)
      Generating Account Numbers, Part 2           165(2)
        From Here                                  165(2)
    Working with Logical and Information           167(28)
    Functions
      Adding Intelligence with Logical Functions   167(15)
        Using the IF() Function                    168(3)
        Performing Multiple Logical Tests          171(5)
        Combining Logical Functions with Arrays    176(6)
      Building an Accounts Receivable Aging        182(2)
      Worksheet
        Calculating a Smarter Due Date             182(1)
        Aging Overdue Invoices                     183(1)
      Getting Data with Information Functions      184(11)
        The Cell() Function                        186(2)
        The Error.Type() Function                  188(1)
        The Info() Function                        189(2)
        The IS Functions                           191(2)
        From Here                                  193(2)
    Working with Lookup Functions                  195(18)
      Understanding Lookup Tables                  196(1)
      The Choose() Function                        197(3)
        Determining the Name of the Day of the     198(1)
        Week
        Determining the Month of the Fiscal Year   198(1)
        Calculating Weighted Questionnaire         199(1)
        Results
        Integrating Choose() and Worksheet         200(1)
        Option Buttons
      Looking Up Values in Tables                  200(13)
        The Vlookup() Function                     201(1)
        The Hlookup() Function                     202(1)
        Returning a Customer Discount Rate with    202(1)
        a Range Lookup
        Returning a Tax Rate with a Range Lookup   203(1)
        Finding Exact Matches                      204(2)
        Advanced Lookup Operations                 206(5)
        From Here                                  211(2)
    Working with Date and Time Functions           213(30)
      How Excel Deals with Dates and Times         213(3)
        Entering Dates and Times                   214(1)
        Excel and Two-Digit Years                  215(1)
      Using Excel's Date Functions                 216(17)
        Returning a Date                           218(1)
        Returning Parts of a Date                  219(10)
        Calculating the Difference Between Two     229(4)
        Dates
      Using Excel's Time Functions                 233(5)
        Returning a Time                           234(1)
        Returning Parts of a Time                  235(2)
        Calculating the Difference Between Two     237(1)
        Times
      Building an Employee Time Sheet              238(5)
        From Here                                  241(2)
    Working with Math Functions                    243(20)
      Understanding Excel's Rounding Functions     247(6)
        The Round() Function                       247(1)
        The Mround() Function                      248(1)
        The Rounddown() and Roundup() Functions    248(1)
        The Ceiling() and Floor() Functions        249(1)
        Determining the Fiscal Quarter in Which    249(1)
        a Date Falls
        Calculating Easter Dates                   250(1)
        The Even() and Odd() Functions             250(1)
        The Int() and Trunc() Functions            251(1)
        Using Rounding to Prevent Calculation      251(1)
        Errors
        Setting Price Points                       252(1)
      Rounding Billable Time                       253(1)
      Summing Values                               253(2)
        The Sum() Function                         253(1)
        Calculating Cumulative Totals              254(1)
        Summing Only the Positive or Negative      255(1)
        Values in a Range
      The Mod() Function                           255(4)
        A Better Formula for Time Differences      256(1)
        Summing Every nth Row                      256(1)
        Determining Whether a Year Is a Leap       257(1)
        Year
        Creating Ledger Shading                    257(2)
      Generating Random Numbers                    259(4)
        The Rand() Function                        259(2)
        The Randbetween() Function                 261(1)
        From Here                                  262(1)
    Working with Statistical Functions             263(34)
      Understanding Descriptive Statistics         265(1)
      Counting Items with the Count() Function     266(1)
      Calculating Averages                         267(2)
        The Average() Function                     267(1)
        The Median() Function                      267(1)
        The Mode() Function                        268(1)
        Calculating the Weighted Mean              268(1)
      Calculating Extreme Values                   269(3)
        The Max() and Min() Functions              269(1)
        The Large() and Small() Functions          270(1)
        Performing Calculations on the Top k       271(1)
        Values
        Performing Calculations on the Bottom k    271(1)
        Values
      Calculating Measures of Variation            272(3)
        Calculating the Range                      272(1)
        Calculating the Variance with the Var()    272(1)
        Function
        Calculating the Standard Deviation with    273(2)
        the Stdevp() and STDEV() Functions
      Working with Frequency Distributions         275(5)
        The Frequency() Function                   275(1)
        Understanding the Normal Distribution      276(2)
        and the Normdist() Function
        The Shape of the Curve I: The Skew()       278(1)
        Function
        The Shape of the Curve II: The Kurt()      279(1)
        Function
      Using the Analysis ToolPak Statistical       280(17)
      Tools
        Using the Descriptive Statistics Tool      283(2)
        Determining the Correlation Between Data   285(2)
        Working with Histograms                    287(2)
        Using the Random Number Generation Tool    289(3)
        Working with Rank and Percentile           292(2)
        From Here                                  294(3)
  III. BUILDING BUSINESS MODELS
    Analyzing Data with Tables                     297(34)
      Converting a Range to a Table                299(1)
      Basic Table Operations                       300(1)
      Sorting a Table                              301(5)
        Sorting a Table in Natural Order           303(1)
        Sorting on Part of a Field                 304(1)
        Sorting Without Articles                   305(1)
      Filtering Table Data                         306(10)
        Using Filter Lists to Filter a Table       306(4)
        Using Complex Criteria to Filter a Table   310(3)
        Entering Computed Criteria                 313(2)
        Copying Filtered Data to a Different       315(1)
        Range
      Referencing Tables in Formulas               316(4)
        Using Table Specifiers                     316(2)
        Entering Table Formulas                    318(2)
      Excel's Table Functions                      320(11)
        About Table Functions                      320(1)
        Table Functions That Don't Require a       320(3)
        Criteria Range
        Table Functions That Accept Multiple       323(2)
        Criteria
        Table Functions That Require a Criteria    325(4)
        Range
        Applying Statistical Table Functions to    329(1)
        a Defects Database
        From Here                                  330(1)
    Analyzing Data with PivotTables                331(30)
      What Are PivotTables?                        331(4)
        How PivotTables Work                       332(2)
        Some PivotTable Terms                      334(1)
      Building PivotTables                         335(5)
        Building a PivotTable from a Table or      335(3)
        Range
        Building a PivotTable from an External     338(1)
        Database
        Working with and Customizing a             339(1)
        PivotTable
      Working with PivotTable Subtotals            340(1)
        Hiding PivotTable Grand Totals             341(1)
        Hiding PivotTable Subtotals                341(1)
        Customizing the Subtotal Calculation       341(1)
      Changing the Data Field Summary              341(9)
      Calculation
        Using a Difference Summary Calculation     342(2)
        Using a Percentage Summary Calculation     344(3)
        Using a Running Total Summary              347(1)
        Calculation
        Using an Index Summary Calculation         348(2)
      Creating Custom PivotTable Calculations      350(5)
        Creating a Calculated Field                352(1)
        Creating a Calculated Item                 353(2)
      Budgeting with Calculated Items              355(2)
      Using PivotTable Results in a Worksheet      357(4)
      Formula
        From Here                                  359(2)
    Using Excel's Business-Modeling Tools          361(24)
      Using What-If Analysis                       361(6)
        Setting Up a One-Input Data Table          362(2)
        Adding More Formulas to the Input Table    364(1)
        Setting Up a Two-Input Table               365(2)
        Editing a Data Table                       367(1)
      Working with Goal Seek                       367(7)
        How Does Goal Seek Work?                   367(1)
        Running Goal Seek                          368(2)
        Optimizing Product Margin                  370(1)
        A Note About Goal Seek's Approximations    371(1)
        Performing a Break-Even Analysis           372(1)
        Solving Algebraic Equations                373(1)
      Working with Scenarios                       374(11)
        Understanding Scenarios                    375(1)
        Setting Up Your Worksheet for Scenarios    376(1)
        Adding a Scenario                          376(2)
        Displaying a Scenario                      378(1)
        Editing a Scenario                         379(1)
        Merging Scenarios                          379(1)
        Generating a Summary Report                380(2)
        Deleting a Scenario                        382(1)
        From Here                                  382(3)
    Using Regression to Track Trends and Make      385(42)
    Forecasts
      Choosing a Regression Method                 386(1)
      Using Simple Regression on Linear Data       386(14)
        Analyzing Trends Using Best-Fit Lines      387(8)
        Making Forecasts                           395(5)
      Trend Analysis and Forecasting for a         400(9)
      Seasonal Sales Model
      Using Simple Regression on Nonlinear Data    409(14)
        Working with an Exponential Trend          409(6)
        Working with a Logarithmic Trend           415(2)
        Working with a Power Trend                 417(3)
        Using Polynomial Regression Analysis       420(3)
      Using Multiple Regression Analysis           423(4)
        From Here                                  426(1)
    Solving Complex Problems with Solver           427(22)
      Some Background on Solver                    427(2)
        The Advantages of Solver                   428(1)
        When Do You Use Solver?                    428(1)
      Loading Solver                               429(1)
      Using Solver                                 429(3)
      Adding Constraints                           432(2)
      Saving a Solution as a Scenario              434(1)
      Setting Other Solver Options                 434(4)
        Controlling Solver                         435(1)
        Selecting the Method Solver Uses           436(1)
        Working with Solver Models                 437(1)
      Making Sense of Solver's Messages            438(1)
      Solving the Transportation Problem           439(2)
      Displaying Solver's Reports                  441(8)
        The Answer Report                          442(1)
        The Sensitivity Report                     443(2)
        The Limits Report                          445(1)
        From Here                                  445(4)
  IV. BUILDING FINANCIAL FORMULAS
    Building Loan Formulas                         449(20)
      Understanding the Time Value of Money        449(1)
      Calculating the Loan Payment                 450(6)
        Loan Payment Analysis                      451(1)
        Working with a Balloon Loan                452(1)
        Calculating Interest Costs, Part I         453(1)
        Calculating the Principal and Interest     453(1)
        Calculating Interest Costs, Part 2         454(1)
        Calculating Cumulative Principal and       455(1)
        Interest
      Building a Loan Amortization Schedule        456(3)
        Building a Fixed-Rate Amortization         457(1)
        Schedule
        Building a Dynamic Amortization Schedule   458(1)
      Calculating the Term of the Loan             459(2)
      Calculating the Interest Rate Required       461(1)
      for a Loan
      Calculating How Much You Can Borrow          462(1)
      Working with Mortgages                       463(6)
        Building a Variable-Rate Mortgage          464(1)
        Amortization Schedule
        Allowing for Mortgage Principal Paydowns   465(2)
        From Here                                  467(2)
    Building Investment Formulas                   469(14)
      Working with Interest Rates                  469(3)
        Understanding Compound Interest            470(1)
        Nominal Versus Effective Interest          470(1)
        Converting Between the Nominal Rate and    471(1)
        the Effective Rate
      Calculating the Future Value                 472(2)
        The Future Value of a Lump Sum             473(1)
        The Future Value of a Series of Deposits   473(1)
        The Future Value of a Lump Sum Plus        474(1)
        Deposits
      Working Toward an Investment Goal            474(5)
        Calculating the Required Interest Rate     474(1)
        Calculating the Required Number of         475(1)
        Periods
        Calculating the Required Regular Deposit   476(1)
        Calculating the Required Initial Deposit   477(1)
        Calculating the Future Value with          478(1)
        Varying Interest Rates
      Building an Investment Schedule              479(4)
        From Here                                  481(2)
    Building Discount Formulas                     483(22)
      Calculating the Present Value                484(4)
        Taking Inflation into Account              484(1)
        Calculating Present Value Using PV()       485(1)
        Income Investing Versus Purchasing a       486(1)
        Rental Property
        Buying Versus Leasing                      487(1)
      Discounting Cash Flows                       488(5)
        Calculating the Net Present Value          489(1)
        Calculating Net Present Value Using        490(1)
        NPV()
        Net Present Value with Varying Cash        491(1)
        Flows
        Net Present Value with Nonperiodic Cash    492(1)
        Flows
      Calculating the Payback Period               493(3)
        Simple Undiscounted Payback Period         494(1)
        Exact Undiscounted Payback Point           495(1)
        Discounted Payback Period                  496(1)
      Calculating the Internal Rate of Return      496(3)
        Using the IRR() Function                   497(1)
        Calculating the Internal Rate of Return    498(1)
        for Nonperiodic Cash Flows
        Calculating Multiple Internal Rates of     498(1)
        Return
      Publishing a Book                            499(6)
        From Here                                  503(2)
Index                                              505
 

"If you've never quite grasped formulas and functions, Paul McFedries will radically expand your understanding and use of Excel. And if you're already an expert and you're moving up to Excel 2007, this book will quickly show you features you've only dreamed of until now..." -Thomas 'Duffbert' Duff, Duffbert's Random Musings, http://www.twduff.com Develop your Microsoft Excel expertise instantly with proven techniques * Master Excel Ranges* Create Powerful Arrays* Troubleshoot Formula Problems* Validate Worksheet Data* Perform What-If Analysis* Model Your Business* Track Trends and Make * Forecasts* Analyze Data* Find Optimal Solutions* Build Dynamic Loan * Schedules Most Microsoft(R) Excel users learn only a small percentage of the program's features. They know they could get more out of Excel if they could just get a leg up on building formulas and using functions. Unfortunately, this side of Excel appears complex and intimidating to the uninitiated-shrouded in the mysteries of mathematics, finance, and impenetrable spreadsheet jargon. Sound familiar? If you're a businessperson who needs to use Excel as an everyday part of your job, then you've come to the right book.Formulas and Functions with Microsoft(R) Office Excel 2007 demystifies worksheet formulas and presents the most useful Excel functions in an accessible, jargon-free way. This book not only takes you through Excel's intermediate and advanced formula-building features, it also tells you why these features are useful to you and shows you how to use them in everyday situations. Throughout the book you'll find no-nonsense, step-by-step tutorials and lots of practical examples aimed directly at business users. * Focuses like a laser on the four technologies that you must master to get the most out of Excel: ranges, formulas, functions, and data analysis tools. * Shuns spreadsheet theory in favor of practical know-how that you can put to use right away. * Provides numerous real-world examples and techniques to help you learn and understand the importance of each section.Introduction 1 Getting the Most Out of Ranges 2 Using Range Names 3 Building Basic Formulas 4 Creating Advanced Formulas 5 Troubleshooting Formulas II Harnessing the Power of Functions 6 Understanding Functions 7 Working with Text Functions 8 Working with Logical and Information Functions 9 Working with Lookup Functions 10 Working with Date and Time Functions 11 Working with Math Functions 12 Working with Statistical Functions III Building Business Models 13 Analyzing Data with Tables 14 Analyzing Data with PivotTables 15 Using Excel's Business-Modeling Tools 16 Using Regression to Track Trends and Make Forecasts 17 Solving Complex Problems with Solver IV Building Financial Formulas 18 Building Loan Formulas 19 Building Investment Formulas 20 Building Discount Formulas Paul McFedries is well-known as a teacher of Windows and Office, particularly Excel, and is the president of Logophilia Limited, a technical writing company. Paul has been working with spreadsheets for more than 20 years and has been developing Excel solutions since the late 1980s.Now primarily a writer, Paul has written more than 50 books that have sold more than three million copies worldwide. These books include Microsoft Office Access 2007 Forms, Reports, and Queries; Tricks of the Microsoft Office 2007 Gurus (all from Que); and Microsoft Windows Vista Unleashed (Sams). Category Office Productivity Suite Covers Microsoft Office Excel 2007 User Level Intermediate - Advanced

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 Mouse Range-Selection Tricks Keyboard Range-Selection Tricks Working with 3D Ranges Selecting a Range Using Go To Using the Go To Special Dialog Box Data Entry in a Range Filling a Range Using the Fill Handle Using AutoFill to Create Text and Numeric Series Creating a Custom AutoFill List Filling a Range Creating a Series Advanced Range Copying Copying Selected Cell Attributes Combining the Source and Destination Arithmetically Transposing Rows and Columns Clearing a Range Applying Conditional Formatting to a Range Creating Highlight Cells Rules Creating Top/Bottom Rules Adding Data Bars Adding Color Scales Adding Icon Sets From Here 2 Using Range Names Defining a Range Name Working with the Name Box Using the New Name Dialog Box Changing the Scope to Define Sheet-Level Names Using Worksheet Text to Define Names Naming Constants Working with Range Names Referring to a Range Name Working with Name AutoComplete Navigating Using Range Names Pasting a List of Range Names in a Worksheet Displaying the Name Manager Filtering Names Editing a Range Name's Coordinates Adjusting Range Name Coordinates Automatically Changing a Range Name Deleting a Range Name Using Names with the Intersection Operator From Here 3 Building Basic Formulas Understanding Formula Basics Formula Limits in Excel 2007 Entering and Editing Formulas Using Arithmetic Formulas Using Comparison Formulas Using Text Formulas Using Reference Formulas Understanding Operator Precedence The Order of Precedence Controlling the Order of Precedence Controlling Worksheet Calculation Copying and Moving Formulas Understanding Relative Reference Format Understanding Absolute Reference Format Copying a Formula Without Adjusting Relative References Displaying Worksheet Formulas Converting a Formula to a Value Working with Range Names in Formulas Pasting a Name into a Formula Applying Names to Formulas Naming Formulas Working with Links in Formulas Understanding External References Updating Links Changing the Link Source Formatting Numbers, Dates, and Times Numeric Display Formats Date and Time Display Formats Deleting Custom Formats From Here 4 Creating Advanced Formulas Working with Arrays Using Array Formulas Using Array Constants Functions That Use or Return Arrays Using Iteration and Circular References Consolidating Multisheet Data Consolidating by Position Consolidating by Category Applying Data-Validation Rules to Cells Using Dialog Box Controls on a Worksheet Using the Form Controls Adding a Control to a Worksheet Linking a Control to a Cell Value Understanding the Worksheet Controls From Here 5 Troubleshooting Formulas Understanding Excel's Error Values #DIV/0! #N/A #NAME? Avoiding #NAME? Errors When Deleting Range Names #NULL! #NUM! #REF! #VALUE! Fixing Other Formula Errors Missing or Mismatched Parentheses Erroneous Formula Results Fixing Circular References Handling Formula Errors with IFERROR() Using the Formula Error Checker Choosing an Error Action Setting Error Checker Options Auditing a Worksheet Understanding Auditing Tracing Cell Precedents Tracing Cell Dependents Tracing Cell Errors Removing Tracer Arrows Evaluating Formulas Watching Cell Values From Here 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 From Here 7 Working with Text Functions Excel's Text Functions Working with Characters and Codes The CHAR() Function The CODE() Function Converting Text The LOWER() Function The UPPER() Function The PROPER() Function Formatting Text The DOLLAR() Function The FIXED() Function The TEXT() Function Displaying When a Workbook Was Last Updated Manipulating Text Removing Unwanted Characters from a String The TRIM() Function The CLEAN() Function The REPT() Function: Repeating a Character Padding a Cell Building Text Charts Extracting a Substring The LEFT() Function The RIGHT() Function The MID() Function Converting Text to Sentence Case A Date-Conversion Formula Generating Account Numbers Searching for Substrings The FIND() and SEARCH() Functions Extracting a First Name or Last Name Extracting First Name, Last Name, and Middle Initial Determining the Column Letter Substituting One Substring for Another The REPLACE() Function The SUBSTITUTE() Function Removing a Character from a String Removing Two Different Characters from a String Removing Line Feeds Generating Account Numbers, Part 2 From Here 8 Working with Logical and Information Functions Adding Intelligence with Logical Functions Using the IF() Function Performing Multiple Logical Tests Combining Logical Functions with Arrays Building an Accounts Receivable Aging Worksheet Calculating a Smarter Due Date Aging Overdue Invoices Getting Data with Information Functions The CELL() Function The ERROR.TYPE() Function The INFO() Function The IS Functions From Here 9 Working with Lookup Functions Understanding Lookup Tables The CHOOSE() Function Determining the Name of the Day of the Week Determining the Month of the Fiscal Year Calculating Weighted Questionnaire Results Integrating CHOOSE() and Worksheet Option Buttons Looking Up Values in Tables The VLOOKUP() Function The HLOOKUP() Function Returning a Customer Discount Rate with a Range Lookup Returning a Tax Rate with a Range Lookup Finding Exact Matches Advanced Lookup Operations From Here 10 Working with Date and Time Functions How Excel Deals with Dates and Times Entering Dates and Times Excel and Two-Digit Years Using Excel's Date Functions Returning a Date Returning Parts of a Date Calculating the Difference Between Two Dates Using Excel's Time Functions Returning a Time Returning Parts of a Time Calculating the Difference Between Two Times Building an Employee Time Sheet From Here 11 Working with Math Functions Understanding Excel's Rounding Functions The ROUND() Function The MROUND() Function The ROUNDDOWN() and ROUNDUP() Functions The CEILING() and FLOOR() Functions Determining the Fiscal Quarter in Which a Date Falls Calculating Easter Dates The EVEN() and ODD() Functions The INT() and TRUNC() Functions Using Rounding to Prevent Calculation Errors Setting Price Points Rounding Billable Time Summing Values The SUM() Function Calculating Cumulative Totals Summing Only the Positive or Negative Values in a Range The MOD() Function A Better Formula for Time Differences Summing Every nth Row Determining Whether a Year Is a Leap Year Creating Ledger Shading Generating Random Numbers The RAND() Function The RANDBETWEEN() Function From Here 12 Working with Statistical Functions Understanding Descriptive Statistics Counting Items with the COUNT() Function Calculating Averages The AVERAGE() Function The MEDIAN() Function The MODE() Function Calculating the Weighted Mean Calculating Extreme Values The MAX() and MIN() Functions The LARGE() and SMALL() Functions Performing Calculations on the Top k Values Performing Calculations on the Bottom k Values Calculating Measures of Variation Calculating the Range Calculating the Variance with the VAR() Function Calculating the Standard Deviation with the STDEVP() and STDEV() Functions Working with Frequency Distributions The FREQUENCY() Function Understanding the Normal Distribution and the NORMDIST() Function The Shape of the Curve I: The SKEW() Function The Shape of the Curve II: The KURT() Function Using the Analysis ToolPak Statistical Tools Using the Descriptive Statistics Tool Determining the Correlation Between Data Working with Histograms Using the Random Number Generation Tool Working with Rank and Percentile From Here III Building Business Models 13 Analyzing Data with Tables Converting a Range to a Table Basic Table Operations Sorting a Table Sorting a Table in Natural Order Sorting on Part of a Field Sorting Without Articles Filtering Table Data Using Filter Lists to Filter a Table Using Complex Criteria to Filter a Table Entering Computed Criteria Copying Filtered Data to a Different Range Referencing Tables in Formulas Using Table Specifiers Entering Table Formulas Excel's Table Functions About Table Functions Table Functions That Don't Require a Criteria Range Table Functions That Accept Multiple Criteria Table Functions That Require a Criteria Range Applying Statistical Table Functions to a Defects Database From Here 14 Analyzing Data with PivotTables What Are PivotTables? How PivotTables Work Some PivotTable Terms Building PivotTables Building a PivotTable from a Table or Range Building a PivotTable from an External Database Working with and Customizing a PivotTable Working with PivotTable Subtotals Hiding PivotTable Grand Totals Hiding PivotTable Subtotals Customizing the Subtotal Calculation Changing the Data Field Summary Calculation Using a Difference Summary Calculation Using a Percentage Summary Calculation Using a Running Total Summary Calculation Using an Index Summary Calculation Creating Custom PivotTable Calculations Creating a Calculated Field Creating a Calculated Item Budgeting with Calculated Items Using PivotTable Results in a Worksheet Formula From Here 15 Using Excel's Business-Modeling Tools Using What-If Analysis Setting Up a One-Input Data Table Adding More Formulas to the Input Table Setting Up a Two-Input Table Editing a Data Table Working with Goal Seek How Does Goal Seek Work? Running Goal Seek Optimizing Product Margin A Note About Goal Seek's Approximations Performing a Break-Even Analysis Solving Algebraic Equations Working with Scenarios Understanding Scenarios Setting Up Your Worksheet for Scenarios Adding a Scenario Displaying a Scenario Editing a Scenario Merging Scenarios Generating a Summary Report Deleting a Scenario From Here 16 Using Regression to Track Trends and Make Forecasts Choosing a Regression Method Using Simple Regression on Linear Data Analyzing Trends Using Best-Fit Lines Making Forecasts Trend Analysis and Forecasting for a Seasonal Sales Model Using Simple Regression on Nonlinear Data Working with an Exponential Trend Working with a Logarithmic Trend Working with a Power Trend Using Polynomial Regression Analysis Using Multiple Regression Analysis From Here 17 Solving Complex Problems with Solver Some Background on Solver The Advantages of Solver When Do You Use Solver? Loading Solver Using Solver Adding Constraints Saving a Solution as a Scenario Setting Other Solver Options Controlling Solver Selecting the Method Solver Uses Working with Solver Models Making Sense of Solver's Messages Solving the Transportation Problem Displaying Solver's Reports The Answer Report The Sensitivity Report The Limits Report From Here IV Building Financial Formulas 18 Building Loan Formulas Understanding the Time Value of Money Calculating the Loan Payment Loan Payment Analysis Working with a Balloon Loan Calculating Interest Costs, Part I Calculating the Principal and Interest Calculating Interest Costs, Part 2 Calculating Cumulative Principal and Interest Building a Loan Amortization Schedule Building a Fixed-Rate Amortization Schedule Building a Dynamic Amortization Schedule Calculating the Term of the Loan Calculating the Interest Rate Required for a Loan Calculating How Much You Can Borrow Working with Mortgages Building a Variable-Rate Mortgage Amortization Schedule Allowing for Mortgage Principal Paydowns From Here 19 Building Investment Formulas Working with Interest Rates Understanding Compound Interest Nominal Versus Effective Interest Converting Between the Nominal Rate and the Effective Rate Calculating the Future Value The Future Value of a Lump Sum The Future Value of a Series of Deposits The Future Value of a Lump Sum Plus Deposits Working Toward an Investment Goal Calculating the Required Interest Rate Calculating the Required Number of Periods Calculating the Required Regular Deposit Calculating the Required Initial Deposit Calculating the Future Value with Varying Interest Rates Building an Investment Schedule From Here 20 Building Discount Formulas Calculating the Present Value Taking Inflation into Account Calculating Present Value Using PV() Income Investing Versus Purchasing a Rental Property Buying Versus Leasing Discounting Cash Flows Calculating the Net Present Value Calculating Net Present Value Using NPV() Net Present Value with Varying Cash Flows Net Present Value with Nonperiodic Cash Flows Calculating the Payback Period Simple Undiscounted Payback Period Exact Undiscounted Payback Point Discounted Payback Period Calculating the Internal Rate of Return Using the IRR() Function Calculating the Internal Rate of Return for Nonperiodic Cash Flows Calculating Multiple Internal Rates of Return Publishing a Book From Here 0789736683, TOC, 2/19/2007