# Microsoft Excel Formulas and Functions (Office 2021 and Microsoft 365)

- Length: 512 pages
- Edition: 1
- Language: English
- Publisher: Microsoft Press
- Publication Date: 2022-03-07
- ISBN-10: 0137559402
- ISBN-13: 9780137559404
- Sales Rank: #2087770 (See Top 100 Books)

Use Excel 365 and Excel 2021 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 recent improvements ranging from dynamic arrays to XLOOKUP and LET. 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!

By reading this book, you will:

- Improve business analyses by adding intelligence and knowledge to your models
- Replace cumbersome formulas with convenient predefined functions
- Use modern lookups to make your formulas more powerful and flexible
- Simplify complex calculations with dynamic arrays in Excel 365 and Excel 2021
- Use conditional formatting to reveal anomalies, problems, or opportunities
- Calculate loan payments, interest costs, terms, and amortization schedules
- Project the future value of investments, and plan to achieve investment goals
- Master essential discounting and cash-flow analysis tools, including NPV and IRR
- Sort, filter, and analyze any tabular data, from customers to inventory
- Easily analyze huge datasets with PivotTable calculations
- Perform sophisticated what-if analyses, scenario planning, and forecasting
- Optimize profit, cost, or operational efficiency with Solver

About This Book

- For everyone who wants to get more done with Microsoft Excel in less time
- For business and financial professionals, entrepreneurs, students, and others who need to efficiently manage and analyze data

Cover Page Title Page Copyright Page Pearson’s Commitment to Diversity, Equity, and Inclusion Contents at a Glance Contents Acknowledgments About the author Introduction What’s in the book This book’s special features About the companion content Support and feedback Part 1. Mastering Excel Formulas Chapter 1. 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 formulas Chapter 2. Creating 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 worksheet Chapter 3. Troubleshooting formulas Understanding Excel’s error values Fixing other formula errors Handling formula errors with IFERROR Using the formula error checker Auditing a worksheet Part 2. Harnessing the Power of Functions Chapter 4. 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 Chapter 5. Working 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 another Chapter 6. Working with logical and information functions Adding intelligence with logical functions Getting data with information functions Chapter 7. Working with lookup functions Taking a look at Excel’s lookup functions Understanding lookup tables The CHOOSE function Looking up values in ranges or tables Modern lookups with XLOOKUP Chapter 8. Working with date and time functions How Excel deals with dates and times Using Excel’s time functions Chapter 9. Working with math functions Excel’s math and trig functions Understanding Excel’s rounding functions Summing values The MOD function Generating random numbers Programming with Excel’s formula language Part 3. Building Business Formulas Chapter 10. Implementing basic business formulas Pricing formulas Financial formulas Inventory formulas Liquidity formulas Chapter 11. Building 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 distributions Chapter 12. Building inferential statistical formulas Understanding inferential statistics Sampling data Determining whether two variables are related Working with probability distributions Determining confidence intervals Hypothesis testing Chapter 13. Applying 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 analysis Chapter 14. Building 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 borrow Chapter 15. Working with investment formulas Working with interest rates Calculating the future value Working toward an investment goal Chapter 16. Building discount formulas Calculating the present value Discounting cash flows Calculating the payback period Calculating the internal rate of return Part 4. Building Business Models Chapter 17. Analyzing data with tables Sorting a table Filtering table data Referencing tables in formulas Excel’s table functions Chapter 18. Analyzing data with PivotTables Working with PivotTable subtotals Changing the value field summary calculation Creating custom PivotTable calculations Using PivotTable results in a worksheet formula Chapter 19. Using Excel’s business modeling tools Using what-if analysis Working with Goal Seek Working with scenarios Chapter 20. Solving 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 Index Code Snippets

1. Disable the **AdBlock** plugin. Otherwise, you may not get any links.

2. Solve the CAPTCHA.

3. Click download link.

4. Lead to download server to download.