Formulas and Functions with Microsoft Office Excel 2007

Formulas and Functions with Microsoft Office Excel 2007

4.09 (11 ratings by Goodreads)
By (author) 

Free delivery worldwide

Available. Dispatched from the UK in 3 business days
When will my order arrive?

Description

"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 techniquesMaster Excel RangesCreate Powerful ArraysTroubleshoot Formula ProblemsValidate Worksheet DataPerform What-If AnalysisModel Your BusinessTrack Trends and Make ForecastsAnalyze DataFind Optimal SolutionsBuild 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. Introduction1 Getting the Most Out of Ranges2 Using Range Names3 Building Basic Formulas4 Creating Advanced Formulas5 Troubleshooting FormulasII Harnessing the Power of Functions 6 Understanding Functions7 Working with Text Functions8 Working with Logical and Information Functions9 Working with Lookup Functions10 Working with Date and Time Functions11 Working with Math Functions12 Working with Statistical FunctionsIII Building Business Models 13 Analyzing Data with Tables14 Analyzing Data with PivotTables15 Using Excel's Business-Modeling Tools16 Using Regression to Track Trends and Make Forecasts17 Solving Complex Problems with SolverIV Building Financial Formulas 18 Building Loan Formulas19 Building Investment Formulas20 Building Discount FormulasPaul 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 SuiteCovers Microsoft Office Excel 2007User Level Intermediate - Advanced
show more

Product details

  • Paperback | 552 pages
  • 177.8 x 226.1 x 35.6mm | 839.16g
  • Que Corporation,U.S.
  • Indianapolis, IN, United States
  • English
  • 0789736683
  • 9780789736680
  • 981,852

Back cover copy

"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 Ranges2 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 Tables14 Analyzing Data with PivotTables15 Using Excel's Business-Modeling Tools 16 Using Regression to Track Trends and Make Forecasts 17 Solving Complex Problems with SolverIV Building Financial Formulas 18 Building Loan Formulas19 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
show more

Table of contents

Introduction What's in the Book This Book's Special Features I Mastering Excel Ranges and Formulas1 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 Here2 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 Here3 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 Here4 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 Here5 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 Here7 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 Here8 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 Here9 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 Here10 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 Here11 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 Here12 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 Here14 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 Here15 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 Here16 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 Here17 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 Here19 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 Here20 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 Here0789736683, TOC, 2/19/2007
show more

About Paul McFedries

Paul McFedries is the president of Logophilia Limited, a technical writing company. Now primarily a writer, Paul has worked as a programmer, consultant, spreadsheet developer, and website developer. He has written more than 50 books that have sold more than three million copies worldwide. These books include Access 2007 Forms, Reports, and Queries (Que, 2007), Tricks of the Microsoft Office 2007 Gurus (Que, 2007), VBA for the 2007 Microsoft Office System (Que, 2007), and Windows Vista Unleashed (Sams, 2006).
show more

Rating details

11 ratings
4.09 out of 5 stars
5 36% (4)
4 36% (4)
3 27% (3)
2 0% (0)
1 0% (0)
Book ratings by Goodreads
Goodreads is the world's largest site for readers with over 50 million reviews. We're featuring millions of their reader ratings on our book pages to help you find your new favourite book. Close X