McFedries Paul. Microsoft Excel 2019 Formulas and Functions

Pearson Education, 2019. — 512 р. — ISBN: 978-1-5093-0619-0.Expert Paul McFedries helps you master key Excel 2019 and Office 365 tools for building more powerful spreadsheets.Use Excel 2019 and Office 365 core features to build spreadsheets that solve business problems and deliver reliable answers. Drawing on his unsurpassed experience, Paul McFedries helps you make the most of formulas and functions, including the latest improvements to arrays, formula error handling, and statistics. McFedries’ step-by-step projects walk you through handling key tasks, from building timesheets to projecting cash flow and aging receivables. His practical examples and clear instructions demystify intermediate- to advanced-level formula construction, and help you leverage Excel’s most useful functions in your everyday work. Becoming an Excel expert has never been easier!About the authorWhat’s in the book
This book’s special features
About the companion content
Support and feedbackMastering Excel formulas
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 formulasCreating advanced formulas
Working with arrays
Using iteration and circular references
Consolidating multisheet data
Applying data-validation rules to cells
Using dialog box controls on a worksheetTroubleshooting formulas
Understanding Excel’s error values
Fixing other formula errors
Handling formula errors with IFERROR()
Using the formula error checker
Auditing a worksheetHarnessing the power of functions
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 ToolPakWorking with text functions
Excel’s text functions
Working with characters and codes
Converting text
Formatting text
Manipulating text
Searching for substrings
Substituting one substring for anotherWorking with logical and information functions
Adding intelligence with logical functions
Getting data with information functionsWorking with lookup functions
Taking a look at Excel’s lookup functions
Understanding lookup tables
The CHOOSE() function
Looking up values in tablesWorking with date and time functions
How Excel deals with dates and times
Using Excel’s time functionsWorking with math functions
Excel’s math and trig functions
Understanding Excel’s rounding functions
Summing values
The MOD() function
Generating random numbersBuilding business formulas
Implementing basic business formulas
Pricing formulas
Financial formulas
Inventory formulas
Liquidity formulasBuilding descriptive statistical formulas
Understanding descriptive statistics
Counting items
Calculating averages
Calculating extreme values
Working with rank and percentile
Calculating measures of variation
Working with frequency distributionsBuilding inferential statistical formulas
Understanding inferential statistics
Sampling data
Determining whether two variables are related
Working with probability distributions
Determining confidence intervals
Hypothesis testingApplying regression to track trends and make forecasts
Choosing a regression method
Using simple regression on linear data
Using simple regression on nonlinear data
Using multiple regression analysisBuilding loan formulas
Understanding the time value of money
Calculating a loan payment
Building a loan amortization schedule
Calculating the term of a loan
Calculating the interest rate required for a loan
Calculating how much you can borrowWorking with investment formulas
Working with interest rates
Calculating the future value
Working toward an investment goalBuilding discount formulas
Calculating the present value
Discounting cash flows
Calculating the payback period
Calculating the internal rate of returnBuilding business models
Analyzing data with tables
Sorting a table
Filtering table data
Referencing tables in formulas
Excel’s table functionsAnalyzing data with PivotTables
Working with PivotTable subtotals
Changing the value field summary calculation
Creating custom PivotTable calculations
Using PivotTable results in a worksheet formulaUsing Excel’s business modeling tools
Using what-if analysis
Working with Goal Seek
Working with scenariosSolving 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
Displaying Solver’s reports