book

Business Solutions Formulas and Functions with Microsoft® Office Excel 2007

Name: Business Solutions Formulas and Functions with Microsoft® Office Excel 2007
Author: Paul McFedries
ISBN: 0789736683

by Paul McFedries

March 2007

Intermediate to advanced

552 pages

12h 36m

English

Que

Read now

Unlock full access

Copyright
Dedication
About the Author
Acknowledgments
We Want to Hear from You!
Reader Services
Introduction
What’s in the BookThis Book’s Special Features
I. Mastering Excel Ranges and Formulas
1. Getting the Most Out of Ranges
Advanced Range-Selection TechniquesMouse Range-Selection TricksKeyboard Range-Selection TricksWorking with 3D RangesSelecting a Range Using Go ToUsing the Go To Special Dialog BoxSelecting Cells by TypeSelecting Adjacent CellsSelecting Cells by DifferencesSelecting Cells by ReferenceOther Go To Special OptionsShortcut Keys for Selecting via Go ToData Entry in a RangeFilling a RangeUsing the Fill HandleUsing AutoFill to Create Text and Numeric SeriesCreating a Custom AutoFill ListFilling a RangeCreating a SeriesAdvanced Range CopyingCopying Selected Cell AttributesCombining the Source and Destination ArithmeticallyTransposing Rows and ColumnsClearing a RangeApplying Conditional Formatting to a RangeCreating Highlight Cells RulesCreating Top/Bottom RulesAdding Data BarsAdding Color ScalesAdding Icon SetsFrom Here
2. Using Range Names
Defining a Range NameWorking with the Name BoxUsing the New Name Dialog BoxChanging the Scope to Define Sheet-Level NamesUsing Worksheet Text to Define NamesNaming ConstantsWorking with Range NamesReferring to a Range NameWorking with Name AutoCompleteNavigating Using Range NamesPasting a List of Range Names in a WorksheetDisplaying the Name ManagerFiltering NamesEditing a Range Name’s CoordinatesAdjusting Range Name Coordinates AutomaticallySolution 1: Include a Blank Cell at the End of the RangeSolution 2: Name the Entire Row or ColumnChanging a Range NameDeleting a Range NameUsing Names with the Intersection OperatorFrom Here
3. Building Basic Formulas
Understanding Formula BasicsFormula Limits in Excel 2007Entering and Editing FormulasUsing Arithmetic FormulasUsing Comparison FormulasUsing Text FormulasUsing Reference FormulasUnderstanding Operator PrecedenceThe Order of PrecedenceControlling the Order of PrecedenceControlling Worksheet CalculationCopying and Moving FormulasUnderstanding Relative Reference FormatUnderstanding Absolute Reference FormatCopying a Formula Without Adjusting Relative ReferencesDisplaying Worksheet FormulasConverting a Formula to a ValueWorking with Range Names in FormulasPasting a Name into a FormulaApplying Names to FormulasIgnoring Relative and Absolute References When Applying NamesUsing Row and Column Names When Applying NamesNaming FormulasWorking with Links in FormulasUnderstanding External ReferencesUpdating LinksChanging the Link SourceFormatting Numbers, Dates, and TimesNumeric Display FormatsChanging Numeric FormatsCustomizing Numeric FormatsHiding ZerosUsing Condition ValuesDate and Time Display FormatsCustomizing Date and Time FormatsDeleting Custom FormatsFrom Here

4. Creating Advanced Formulas
Working with ArraysUsing Array FormulasUnderstanding Array FormulasArray Formulas That Operate on Multiple RangesUsing Array ConstantsFunctions That Use or Return ArraysUsing Iteration and Circular ReferencesConsolidating Multisheet DataConsolidating by PositionConsolidating by CategoryApplying Data-Validation Rules to CellsUsing Dialog Box Controls on a WorksheetUsing the Form ControlsAdding a Control to a WorksheetLinking a Control to a Cell ValueUnderstanding the Worksheet ControlsGroup BoxesOption ButtonsCheck BoxesList Boxes and Combo BoxesScroll Bars and Spin BoxesFrom Here
5. Troubleshooting Formulas
Understanding Excel’s Error Values#DIV/0!#N/A#NAME?#NULL!#NUM!#REF!#VALUE!Fixing Other Formula ErrorsMissing or Mismatched ParenthesesErroneous Formula ResultsFixing Circular ReferencesHandling Formula Errors with IFERROR()Using the Formula Error CheckerChoosing an Error ActionSetting Error Checker OptionsAuditing a WorksheetUnderstanding AuditingTracing Cell PrecedentsTracing Cell DependentsTracing Cell ErrorsRemoving Tracer ArrowsEvaluating FormulasWatching Cell ValuesFrom Here
II. Harnessing the Power of Functions
6. Understanding Functions
About Excel’s FunctionsThe Structure of a FunctionTyping a Function into a FormulaUsing the Insert Function FeatureLoading the Analysis ToolPakFrom Here
7. Working with Text Functions
Excel’s Text FunctionsWorking with Characters and CodesThe CHAR() FunctionGenerating the ANSI Character SetGenerating a Series of LettersThe CODE() FunctionGenerating a Series of Letters Starting from Any LetterConverting TextThe LOWER() FunctionThe UPPER() FunctionThe PROPER() FunctionFormatting TextThe DOLLAR() FunctionThe FIXED() FunctionThe TEXT() FunctionDisplaying When a Workbook Was Last UpdatedManipulating TextRemoving Unwanted Characters from a StringThe TRIM() FunctionThe CLEAN() FunctionThe REPT() Function: Repeating a CharacterPadding a CellBuilding Text ChartsExtracting a SubstringThe LEFT() FunctionThe RIGHT() FunctionThe MID() FunctionConverting Text to Sentence CaseA Date-Conversion FormulaSearching for SubstringsThe FIND() and SEARCH() FunctionsExtracting a First Name or Last NameExtracting First Name, Last Name, and Middle InitialDetermining the Column LetterSubstituting One Substring for AnotherThe REPLACE() FunctionThe SUBSTITUTE() FunctionRemoving a Character from a StringRemoving Two Different Characters from a StringRemoving Line FeedsFrom Here
8. Working with Logical and Information Functions
Adding Intelligence with Logical FunctionsUsing the IF() FunctionIF(): The Simplest CaseHandling a FALSE ResultAvoiding Division by ZeroPerforming Multiple Logical TestsNesting IF() FunctionsCalculating Tiered BonusesThe AND() FunctionSlotting Values into CategoriesThe OR() FunctionApplying Conditional Formatting with FormulasCombining Logical Functions with ArraysApplying a Condition Across a RangeOperating Only on Cells That Meet a ConditionDetermining Whether a Value Appears in a ListCounting Occurrences in a RangeDetermining Where a Value Appears in a ListGetting Data with Information FunctionsThe CELL() FunctionThe ERROR.TYPE() FunctionThe INFO() FunctionThe IS FunctionsCounting the Number of Blanks in a RangeChecking a Range for Non-numeric ValuesCounting the Number of Errors in a RangeIgnoring Errors When Working with a RangeFrom Here
9. Working with Lookup Functions
Understanding Lookup TablesThe CHOOSE() FunctionDetermining the Name of the Day of the WeekDetermining the Month of the Fiscal YearCalculating Weighted Questionnaire ResultsIntegrating CHOOSE() and Worksheet Option ButtonsLooking Up Values in TablesThe VLOOKUP() FunctionThe HLOOKUP() FunctionReturning a Customer Discount Rate with a Range LookupReturning a Tax Rate with a Range LookupFinding Exact MatchesLooking Up a Customer Account NumberCombining Exact-Match Lookups with In-Cell Drop-Down ListsAdvanced Lookup OperationsThe MATCH() and INDEX() FunctionsLooking Up a Value Using Worksheet List BoxesUsing Any Column as the Lookup ColumnCreating Row-and-Column LookupsCreating Multiple-Column LookupsFrom Here
10. Working with Date and Time Functions
How Excel Deals with Dates and TimesEntering Dates and TimesExcel and Two-Digit YearsUsing Excel’s Date FunctionsReturning a DateTODAY(): Returning the Current DateDATE(): Returning Any DateDATEVALUE(): Converting a String to a DateReturning Parts of a DateThe YEAR() FunctionThe MONTH() FunctionThe DAY() FunctionThe WEEKDAY() FunctionThe WEEKNUM() FunctionReturning a Date X Years, Months, or Days from NowA Workday Alternative: The WORKDAY() FunctionAdding X Months: A ProblemThe EDATE() FunctionThe EOMONTH() FunctionReturning the Last Day of Any MonthDetermining a Person’s Birthday Given the Birth DateReturning the Date of the Nth Occurrence of a Weekday in a MonthCalculating Holiday DatesCalculating the Julian DateCalculating the Difference Between Two DatesCalculating a Person’s AgeThe DATEDIF() FunctionCalculating a Person’s Age, Part 2NETWORKDAYS(): Calculating the Number of Workdays Between Two DatesDAYS360(): Calculating Date Differences Using a 360-Day YearYEARFRAC(): Returning the Fraction of a Year Between Two DatesUsing Excel’s Time FunctionsReturning a TimeNOW(): Returning the Current TimeTIME(): Returning Any TimeTIMEVALUE(): Converting a String to a TimeReturning Parts of a TimeThe HOUR() FunctionThe MINUTE() FunctionThe SECOND() FunctionReturning a Time X Hours, Minutes, or Seconds from NowSumming Time ValuesCalculating the Difference Between Two TimesFrom Here
11. Working with Math Functions
Understanding Excel’s Rounding FunctionsThe ROUND() FunctionThe MROUND() FunctionThe ROUNDDOWN() and ROUNDUP() FunctionsThe CEILING() and FLOOR() FunctionsDetermining the Fiscal Quarter in Which a Date FallsCalculating Easter DatesThe EVEN() and ODD() FunctionsThe INT() and TRUNC() FunctionsUsing Rounding to Prevent Calculation ErrorsSetting Price PointsSumming ValuesThe SUM() FunctionCalculating Cumulative TotalsSumming Only the Positive or Negative Values in a RangeThe MOD() FunctionA Better Formula for Time DifferencesSumming Every nth RowSpecial Case No. 1: Summing Only Odd RowsSpecial Case No. 2: Summing Only Even RowsDetermining Whether a Year Is a Leap YearCreating Ledger ShadingGenerating Random NumbersThe RAND() FunctionGenerating Random n-Digit NumbersGenerating a Random LetterSorting Values RandomlyThe RANDBETWEEN() FunctionFrom Here
12. Working with Statistical Functions
Understanding Descriptive StatisticsCounting Items with the COUNT() FunctionCalculating AveragesThe AVERAGE() FunctionThe MEDIAN() FunctionThe MODE() FunctionCalculating the Weighted MeanCalculating Extreme ValuesThe MAX() and MIN() FunctionsThe LARGE() and SMALL() FunctionsPerforming Calculations on the Top k ValuesPerforming Calculations on the Bottom k ValuesCalculating Measures of VariationCalculating the RangeCalculating the Variance with the VAR() FunctionCalculating the Standard Deviation with the STDEVP() and STDEV() FunctionsWorking with Frequency DistributionsThe FREQUENCY() FunctionUnderstanding the Normal Distribution and the NORMDIST() FunctionThe Shape of the Curve I: The SKEW() FunctionThe Shape of the Curve II: The KURT() FunctionUsing the Analysis ToolPak Statistical ToolsUsing the Descriptive Statistics ToolDetermining the Correlation Between DataWorking with HistogramsUsing the Random Number Generation ToolWorking with Rank and PercentileFrom Here
III. Building Business Models
13. Analyzing Data with Tables
Converting a Range to a TableBasic Table OperationsSorting a TableSorting a Table in Natural OrderSorting on Part of a FieldSorting Without ArticlesFiltering Table DataUsing Filter Lists to Filter a TableWorking with Quick FiltersShowing Filtered RecordsUsing Complex Criteria to Filter a TableSetting Up a Criteria RangeFiltering a Table with a Criteria RangeEntering Compound CriteriaEntering Computed CriteriaCopying Filtered Data to a Different RangeReferencing Tables in FormulasUsing Table SpecifiersEntering Table FormulasExcel’s Table FunctionsAbout Table FunctionsTable Functions That Don’t Require a Criteria RangeUsing COUNTIF()Using SUMIF()Using AVERAGEIF()Table Functions That Accept Multiple CriteriaUsing COUNTIFS()Using SUMIFS()Using AVERAGEIFS()Table Functions That Require a Criteria RangeUsing DAVERAGE()Using DGET()From Here
14. Analyzing Data with PivotTables
What Are PivotTables?How PivotTables WorkSome PivotTable TermsBuilding PivotTablesBuilding a PivotTable from a Table or RangeBuilding a PivotTable from an External DatabaseWorking with and Customizing a PivotTableWorking with PivotTable SubtotalsHiding PivotTable Grand TotalsHiding PivotTable SubtotalsCustomizing the Subtotal CalculationChanging the Data Field Summary CalculationUsing a Difference Summary CalculationUsing a Percentage Summary CalculationUsing a Running Total Summary CalculationUsing an Index Summary CalculationCreating Custom PivotTable CalculationsCreating a Calculated FieldCreating a Calculated ItemUsing PivotTable Results in a Worksheet FormulaFrom Here
15. Using Excel’s Business-Modeling Tools
Using What-If AnalysisSetting Up a One-Input Data TableAdding More Formulas to the Input TableSetting Up a Two-Input TableEditing a Data TableWorking with Goal SeekHow Does Goal Seek Work?Running Goal SeekOptimizing Product MarginA Note About Goal Seek’s ApproximationsPerforming a Break-Even AnalysisSolving Algebraic EquationsWorking with ScenariosUnderstanding ScenariosSetting Up Your Worksheet for ScenariosAdding a ScenarioDisplaying a ScenarioEditing a ScenarioMerging ScenariosGenerating a Summary ReportDeleting a ScenarioFrom Here
16. Using Regression to Track Trends and Make Forecasts
Choosing a Regression MethodUsing Simple Regression on Linear DataAnalyzing Trends Using Best-Fit LinesPlotting a Best-Fit TrendlineUnderstanding the Regression EquationUnderstanding R2Calculating Best-Fit Values Using TREND()Calculating Best-Fit Values Using LINEST()Analyzing the Sales Versus Advertising TrendMaking ForecastsPlotting Forecasted ValuesExtending a Linear Trend with the Fill HandleExtending a Linear Trend Using the Series CommandForecasting with the Regression EquationForecasting with TREND()Forecasting with LINEST()Using Simple Regression on Nonlinear DataWorking with an Exponential TrendPlotting an Exponential TrendlineCalculating Exponential Trend and Forecast ValuesExponential Trending and Forecasting Using the GROWTH() FunctionWorking with a Logarithmic TrendPlotting a Logarithmic TrendlineCalculating Logarithmic Trend and Forecast ValuesWorking with a Power TrendPlotting a Power TrendlineCalculating Power Trend and Forecast ValuesUsing Polynomial Regression AnalysisPlotting a Polynomial TrendlineCalculating Polynomial Trend and Forecast ValuesUsing Multiple Regression AnalysisFrom Here
17. Solving Complex Problems with Solver
Some Background on SolverThe Advantages of SolverWhen Do You Use Solver?Loading SolverUsing SolverAdding ConstraintsSaving a Solution as a ScenarioSetting Other Solver OptionsControlling SolverSelecting the Method Solver UsesWorking with Solver ModelsMaking Sense of Solver’s MessagesDisplaying Solver’s ReportsThe Answer ReportThe Sensitivity ReportThe Limits ReportFrom Here
IV. Building Financial Formulas
18. Building Loan Formulas
Understanding the Time Value of MoneyCalculating the Loan PaymentLoan Payment AnalysisWorking with a Balloon LoanCalculating Interest Costs, Part ICalculating the Principal and InterestCalculating Interest Costs, Part 2Calculating Cumulative Principal and InterestBuilding a Loan Amortization ScheduleBuilding a Fixed-Rate Amortization ScheduleBuilding a Dynamic Amortization ScheduleCalculating the Term of the LoanCalculating the Interest Rate Required for a LoanCalculating How Much You Can BorrowFrom Here
19. Building Investment Formulas
Working with Interest RatesUnderstanding Compound InterestNominal Versus Effective InterestConverting Between the Nominal Rate and the Effective RateCalculating the Future ValueThe Future Value of a Lump SumThe Future Value of a Series of DepositsThe Future Value of a Lump Sum Plus DepositsWorking Toward an Investment GoalCalculating the Required Interest RateCalculating the Required Number of PeriodsCalculating the Required Regular DepositCalculating the Required Initial DepositCalculating the Future Value with Varying Interest RatesFrom Here
20. Building Discount Formulas
Calculating the Present ValueTaking Inflation into AccountCalculating Present Value Using PV()Income Investing Versus Purchasing a Rental PropertyBuying Versus LeasingDiscounting Cash FlowsCalculating the Net Present ValueCalculating Net Present Value Using NPV()Net Present Value with Varying Cash FlowsNet Present Value with Nonperiodic Cash FlowsCalculating the Payback PeriodSimple Undiscounted Payback PeriodExact Undiscounted Payback PointDiscounted Payback PeriodCalculating the Internal Rate of ReturnUsing the IRR() FunctionCalculating the Internal Rate of Return for Nonperiodic Cash FlowsCalculating Multiple Internal Rates of ReturnFrom Here

Overview

“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® 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® 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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Business Analysis: Microsoft® Excel 2010

Publisher Resources

ISBN: 0789736683Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills