Microsoft Excel 2019 Formulas and Functions (Business Skills)

✍ Scribed by Paul McFedries

• Publisher: Microsoft Press
• Year: 2019
• Tongue: English
• Leaves: 469
• Edition: 1
• Category: Library

✦ Synopsis

Master core Excel tools for building powerful, reliable spreadsheets with  Excel 2019 Formulas and Functions. Excel expert Paul McFedries shows how to use Excel core features to solve problems and get the answers you need. Using real-world examples, McFedries helps you get the absolute most out of features and improvements ranging from AutoFill to Excel’s newest functions. Along the way, you discover the fastest, best ways to handle essential day-to-day tasks ranging from generating account numbers to projecting the impact of inflation.

Becoming an Excel expert has never been easier! You’ll find crystal-clear instructions; insider insights; even complete step-by-step projects for building timesheets, projecting cash flow, aging receivables, analyzing defects, and more.

• Quickly create powerful spreadsheets with range names and array formulas
• Use conditional formatting to instantly reveal anomalies, problems, or opportunities
• Analyze your data with standard tables and PivotTables
• Use complex criteria to filter data in lists
• Understand correlations between data
• Perform sophisticated what-if analyses
• Use regression to track trends and make forecasts
• Build loan, investment, and discount formulas
• Validate data, troubleshoot problems, and build more accurate, trustworthy spreadsheets

✦ Table of Contents

Microsoft Excel 2019 Formulas and Functions
Acknowledgments
About the author
Introduction
WHAT’S IN THE BOOK
THIS BOOK’S SPECIAL FEATURES
ABOUT THE COMPANION CONTENT
SUPPORT AND FEEDBACK
Stay in touch
Errata, updates, and book support
Part I
Mastering Excel formulas
Chapter 1
Building basic formulas
UNDERSTANDING FORMULA BASICS
Formula limits in Excel 2019
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
Displaying all worksheet formulas
Displaying a cell’s formula by using FORMULATEXT()
CONVERTING A FORMULA TO A VALUE
WORKING WITH RANGE NAMES IN FORMULAS
Pasting a name into a formula
Applying names to formulas
Ignoring relative and absolute references when applying names
Using row and column names when applying names
Naming formulas
WORKING WITH LINKS IN FORMULAS
Understanding external references
Updating links
Changing the link source
Chapter 2
Creating advanced formulas
WORKING WITH ARRAYS
Using array formulas
Building dynamic array formulas in Excel 2019
Understanding array formulas
Array formulas that operate on multiple ranges
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
Displaying the Developer tab
Using the form controls
Adding a control to a worksheet
Linking a control to a cell value
Understanding the worksheet controls
Group boxes
Option buttons
Check boxes
List boxes and combo boxes
Scroll bars and spin boxes
Chapter 3
Troubleshooting formulas
UNDERSTANDING EXCEL’S ERROR VALUES
#CALC!
#DIV/0!
#FIELD!
#N/A
#NAME?
Avoiding #NAME? errors when deleting range names
#NULL!
#NUM!
#REF!
#SPILL!
#UNKNOWN!
#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
Part II
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
The CHAR() function
Generating the ANSI character set
Generating a series of letters
The CODE() function
Generating a series of letters starting from any letter
CONVERTING TEXT
The LOWER() function
The UPPER() function
The PROPER() function
The NUMBERVALUE() 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 TEXTJOIN() function: Concatenating text with a delimiter
The REPT() function: Repeating a character or string
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
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
Chapter 6
Working with logical and information functions
ADDING INTELLIGENCE WITH LOGICAL FUNCTIONS
Using the IF() function
IF(): The simplest case
Handling a FALSE result
Avoiding division by zero
Performing multiple logical tests
Nested IF() functions
Calculating tiered bonuses
The IFS() function
The AND() function
Slotting values into categories
The OR() function
Applying conditional formatting with formulas
Combining logical functions with arrays
Applying a condition across a range
Operating only on cells that meet a condition
Determining whether a value appears in a list
Counting occurrences in a range
Determining where a value appears in a list
GETTING DATA WITH INFORMATION FUNCTIONS
The CELL() function
The ERROR.TYPE() function
The INFO() function
The SHEET() and SHEETS() functions
The IS functions
Counting the number of blanks in a range
Checking a range for non-numeric values
Counting the number of errors in a range
Ignoring errors when working with a range
Chapter 7
Working with lookup functions
TAKING A LOOK AT EXCEL’S 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
Looking up a customer account number
Combining exact-match lookups with in-cell drop-down lists
Advanced lookup operations
The MATCH() and INDEX() functions
Looking up a value using worksheet list boxes
Using any column as the lookup column
Creating row-and-column lookups
Creating multiple-column lookups
Chapter 8
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
TODAY(): Returning the current date
DATE(): Returning any date
DATEVALUE(): Converting a string to a date
Returning parts of a date
The YEAR() function
The MONTH() function
The DAY() function
The WEEKDAY() function
The WEEKNUM() function
Returning a date X years, months, or days from now
A workday alternative: The WORKDAY() function
Adding X months: A problem
The EDATE() function
The EOMONTH() function
Returning the last day of any month
Determining a person’s birthday, given the birth date
Returning the date of the nth occurrence of a weekday in a month
Calculating holiday dates
Calculating the Julian date
Calculating the difference between two dates
Calculating a person’s age
DAYS(): Returning the number of days between two dates
NETWORKDAYS(): Calculating the number of workdays between two dates
DAYS360(): Calculating date differences using a 360-day year
YEARFRAC(): Returning the fraction of a year between two dates
USING EXCEL’S TIME FUNCTIONS
Returning a time
NOW(): Returning the current time
TIME(): Returning any time
TIMEVALUE(): Converting a string to a time
Returning parts of a time
The HOUR() function
The MINUTE() function
The SECOND() function
Returning a time X hours, minutes, or seconds from now
Summing time values
Calculating the difference between two times
Chapter 9
Working with math functions
EXCEL’S MATH AND TRIG FUNCTIONS
UNDERSTANDING EXCEL’S ROUNDING FUNCTIONS
The ROUND() function
The MROUND() function
The ROUNDDOWN() and ROUNDUP() functions
The CEILING.MATH() and FLOOR.MATH() functions
Calculating Easter dates
The EVEN() and ODD() functions
The INT() and TRUNC() functions
Using rounding to prevent calculation errors
Setting price points
SUMMING VALUES
The SUM() function
The SUMIF() function
Summing only the positive or negative values in a range
The SUMIFS() function
Calculating cumulative totals
THE MOD() FUNCTION
A better formula for time differences
Summing every nth row
Special case no. 1: Summing only odd rows
Special case no. 2: Summing only even rows
Determining whether a year is a leap year
Creating ledger shading
GENERATING RANDOM NUMBERS
The RAND() function
Generating random n-digit numbers
Generating a random letter
Sorting values randomly
The RANDBETWEEN() function
The RANDARRAY() function
Creating increasing random numbers with the SEQUENCE() function
Part III
Building business formulas
Chapter 10
Implementing basic business formulas
PRICING FORMULAS
Price markups
Selling price
Markup amount
Markup rate
Cost price
Price discounts
Net price
Discount amount
Discount rate
List price
Break-even point
FINANCIAL FORMULAS
Sales ratios
Sales to current assets
Sales to short-term debt
Sales per employee
Cost of goods sold
Gross margin
Net margin
Fixed-asset ratios
Fixed-asset turnover
Return on fixed assets
Fixed assets to short-term debt
INVENTORY FORMULAS
Inventory ratios
Inventory turnover
Inventory turnover rate
Sales to inventory ratio
Inventory management formulas
Inventory safety stock
Inventory reorder level
LIQUIDITY FORMULAS
Accounts receivable ratios
Accounts receivable turnover
Average AR duration
Accounts payable ratios
Accounts payable turnover
Days purchases in accounts payable
Sales to accounts payable
Working capital
Working capital
Working capital turnover
Liquidity ratios
Current ratio
Acid-test ratio
Cash ratio
Liquidity index
Chapter 11
Building descriptive statistical formulas
UNDERSTANDING DESCRIPTIVE STATISTICS
COUNTING ITEMS
The COUNT() function
The COUNTA() function
The COUNTBLANK() function
The COUNTIF() function
The COUNTIFS() function
CALCULATING AVERAGES
The AVERAGE() function
The AVERAGEIF() function
The AVERAGEIFS() 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
WORKING WITH RANK AND PERCENTILE
Calculating rank
Calculating percentile
CALCULATING MEASURES OF VARIATION
Calculating the range
Calculating the variance
Calculating the standard deviation
WORKING WITH FREQUENCY DISTRIBUTIONS
Chapter 12
Building inferential statistical formulas
UNDERSTANDING INFERENTIAL STATISTICS
SAMPLING DATA
Extracting a periodic sample
Extracting a random sample
DETERMINING WHETHER TWO VARIABLES ARE RELATED
Calculating covariance
Calculating correlation
WORKING WITH PROBABILITY DISTRIBUTIONS
Calculating probability
Discrete probability distributions
The binomial distribution
The hypergeometric distribution
The Poisson distribution
Understanding the normal distribution and the NORM.DIST() function
Calculating standard scores
Calculating normal percentiles with NORM.DIST()
The shape of the curve I: The SKEW() function
The shape of the curve II: The KURT() function
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
Analyzing trends using best-fit lines
Plotting a best-fit trendline
Understanding the regression equation
Understanding R2
Calculating best-fit values using TREND()
Calculating best-fit values using LINEST()
Analyzing the sales versus advertising trend
Making forecasts
Plotting forecasted values
Extending a linear trend with the fill handle
Extending a linear trend using the Series command
Forecasting with the regression equation
Forecasting with TREND()
Forecasting with LINEST()
USING SIMPLE REGRESSION ON NONLINEAR DATA
Working with an exponential trend
Plotting an exponential trendline
Calculating exponential trend and forecast values
Exponential trending and forecasting using the GROWTH() function
Working with a logarithmic trend
Plotting a logarithmic trendline
Calculating logarithmic trend and forecast values
Working with a power trend
Plotting a power trendline
Calculating power trend and forecast values
Using polynomial regression analysis
Plotting a polynomial trendline
Calculating polynomial trend and forecast values
USING MULTIPLE REGRESSION ANALYSIS
Chapter 14
Building loan formulas
UNDERSTANDING THE TIME VALUE OF MONEY
CALCULATING A LOAN PAYMENT
Loan payment analysis
Working with a balloon loan
Calculating interest costs, part I
Calculating the principal and interest
Calculating interest costs, part II
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 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
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
Chapter 16
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
Part IV
Building business models
Chapter 17
Analyzing data with tables
SORTING A TABLE
Sorting on part of a field
Sorting without articles
Sorting table data into an array, part I: The SORT() function
Sorting table data into an array, part II: The SORTBY() function
FILTERING TABLE DATA
Using complex criteria to filter a table
Setting up a criteria range
Filtering a table with a criteria range
Entering compound criteria
Entering computed criteria
Filtering table data with the FILTER() function
UNIQUE()
REFERENCING TABLES IN FORMULAS
Using table specifiers
Entering table formulas
EXCEL’S TABLE FUNCTIONS
About table functions
Using DAVERAGE()
Using DGET()
Chapter 18
Analyzing data with PivotTables
WORKING WITH PIVOTTABLE SUBTOTALS
Hiding PivotTable grand totals
Hiding PivotTable subtotals
Customizing the subtotal calculation
CHANGING THE VALUE FIELD SUMMARY CALCULATION
Using a difference summary calculation
Toggling the difference calculation with VBA
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
USING PIVOTTABLE RESULTS IN A WORKSHEET FORMULA
Chapter 19
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 data 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
Chapter 20
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
Selecting the method Solver uses
Controlling how Solver works
Working with Solver models
MAKING SENSE OF SOLVER’S MESSAGES
DISPLAYING SOLVER’S REPORTS
The Answer report
The Sensitivity report
The Limits report