book

Excel® 2013 Formulas and Functions

by Paul McFedries

February 2013

Intermediate to advanced

528 pages

13h 53m

English

Que

Read now

Unlock full access

What’s in the BookThis Book’s Special Features

Advanced Range-Selection TechniquesData Entry in a RangeFilling a RangeCreating a SeriesAdvanced Range CopyingClearing a RangeApplying Conditional Formatting to a RangeFrom Here
Defining a Range NameWorking with Range NamesFrom Here
Understanding Formula BasicsUnderstanding Operator PrecedenceControlling Worksheet CalculationCopying and Moving FormulasDisplaying Worksheet FormulasConverting a Formula to a ValueWorking with Range Names in FormulasWorking with Links in FormulasFormatting Numbers, Dates, and TimesFrom Here
Working with ArraysUsing Iteration and Circular ReferencesConsolidating Multisheet DataApplying Data-Validation Rules to CellsUsing Dialog Box Controls on a WorksheetFrom Here
Understanding Excel’s Error ValuesFixing Other Formula ErrorsHandling Formula Errors with IFERROR()Using the Formula Error CheckerAuditing a WorksheetFrom Here
About Excel’s FunctionsThe Structure of a FunctionTyping a Function into a FormulaUsing the Insert Function FeatureLoading the Analysis ToolPakFrom Here
Excel’s Text FunctionsWorking with Characters and CodesConverting TextFormatting TextManipulating TextRemoving Unwanted Characters from a StringExtracting a SubstringSearching for SubstringsSubstituting One Substring for AnotherFrom Here
Adding Intelligence with Logical FunctionsGetting Data with Information FunctionsFrom Here
Understanding Lookup TablesThe CHOOSE() FunctionLooking Up Values in TablesFrom Here
How Excel Deals with Dates and TimesUsing Excel’s Date FunctionsUsing Excel’s Time FunctionsFrom Here
Understanding Excel’s Rounding FunctionsSumming ValuesThe MOD() FunctionGenerating Random NumbersFrom Here
Understanding Descriptive StatisticsCounting Items with the COUNT() FunctionCalculating AveragesCalculating Extreme ValuesCalculating Measures of VariationWorking with Frequency DistributionsUsing the Analysis ToolPak Statistical ToolsFrom Here
Converting a Range to a TableBasic Table OperationsSorting a TableFiltering Table DataReferencing Tables in FormulasExcel’s Table FunctionsFrom Here
What Are PivotTables?Building PivotTablesWorking with PivotTable SubtotalsChanging the Data Field Summary CalculationCreating Custom PivotTable CalculationsFrom Here
Using What-If AnalysisWorking with Goal SeekWorking with ScenariosFrom Here
Choosing a Regression MethodUsing Simple Regression on Linear DataUsing Simple Regression on Nonlinear DataUsing Multiple Regression AnalysisFrom Here
Some Background on SolverLoading SolverUsing SolverAdding ConstraintsSaving a Solution as a ScenarioSetting Other Solver OptionsMaking Sense of Solver’s MessagesDisplaying Solver’s ReportsFrom Here
Understanding the Time Value of MoneyCalculating the Loan PaymentBuilding a Loan Amortization ScheduleCalculating the Term of the LoanCalculating the Interest Rate Required for a LoanCalculating How Much You Can BorrowFrom Here
Working with Interest RatesCalculating the Future ValueWorking Toward an Investment GoalFrom Here
Calculating the Present ValueDiscounting Cash FlowsCalculating the Payback PeriodCalculating the Internal Rate of ReturnFrom Here

Content preview from Excel® 2013 Formulas and Functions

14. Analyzing Data with PivotTables

In This Chapter

What Are PivotTables?

Building PivotTables

Working with PivotTable Subtotals

Changing the Data Field Summary Calculation

Creating Custom PivotTable Calculations

Tables and external databases can contain hundreds or even thousands of records. Analyzing that much data can be a nightmare without the right kinds of tools. To help you, Excel offers a powerful data analysis tool called a PivotTable. This tool enables you to summarize hundreds of records in a concise tabular format. You can then manipulate the layout of the table to see different views of your data. This chapter introduces you to PivotTables and shows you various ways to use them with your own data. Because this is a book about Excel ...