Excel ® Formulas & Functions

Title Page
Copyright Page
Table of Contents
Introduction
	About This Book
	Foolish Assumptions
	Icons Used in This Book
	Beyond the Book
	Where to Go from Here
Part 1 Getting Started with Excel Formulas and Functions
	Chapter 1 Tapping Into Formula and Function Fundamentals
		Working with Excel Fundamentals
			Understanding workbooks and worksheets
			Introducing the Formulas tab
			Working with rows, columns, cells, ranges, and tables
			Formatting your data
			Getting help
		Gaining the Upper Hand on Formulas
			Entering your first formula
			Understanding references
			Copying formulas with the fill handle
			Assembling formulas the right way
		Using Functions in Formulas
			Looking at what goes into a function
			Arguing with a function
			Nesting functions
	Chapter 2 Saving Time with Function Tools
		Getting Familiar with the Insert Function Dialog Box
		Finding the Correct Function
		Entering Functions Using the Insert Function Dialog Box
			Selecting a function that takes no arguments
			Selecting a function that uses arguments
			Entering cells, ranges, named areas, and tables as function arguments
			Getting help in the Insert Function dialog box
			Using the Function Arguments dialog box to edit functions
		Directly Entering Formulas and Functions
			Entering formulas and functions in the Formula Bar
			Entering formulas and functions directly in worksheet cells
	Chapter 3 Saying “Array!” for Formulas and Functions
		Discovering Arrays
		Using Arrays in Formulas
		Working with Functions That Return Arrays
	Chapter 4 Fixing Formula Boo-Boos
		Catching Errors As You Enter Them
			Getting parentheses to match
			Avoiding circular references
			Mending broken links
			Using the Formula Error Checker
		Auditing Formulas
		Watching the Watch Window
		Evaluating and Checking Errors
		Making an Error Behave the Way You Want
Part 2 Doing the Math
	Chapter 5 Calculating Loan Payments and Interest Rates
		Understanding How Excel Handles Money
			Going with the cash flow
			Formatting for currency
			Choosing separators
		Figuring Loan Calculations
			Calculating the payment amount
			Calculating interest payments
			Calculating payments toward principal
			Calculating the number of payments
			Calculating the number of payments with PDURATION
			Calculating the interest rate
			Calculating the principal
	Chapter 6 Appreciating What You’ll Get, Depreciating What You’ve Got
		Looking into the Future
		Depreciating the Finer Things in Life
			Calculating straight-line depreciation
			Creating an accelerated depreciation schedule
			Creating an even faster accelerated depreciation schedule
			Calculating a midyear depreciation schedule
		Measuring Your Internals
	Chapter 7 Using Basic Math Functions
		Adding It All Together with the SUM Function
		Rounding Out Your Knowledge
			Just plain old rounding
			Rounding in one direction
		Leaving All Decimals Behind with INT
		Leaving Some Decimals Behind with TRUNC
		Looking for a Sign
		Ignoring Signs
	Chapter 8 Advancing Your Math
		Using PI to Calculate Circumference and Diameter
		Generating and Using Random Numbers
			The all-purpose RAND function
			Precise randomness with RANDBETWEEN
		Ordering Items
		Combining
		Raising Numbers to New Heights
		Multiplying Multiple Numbers
		Using What Remains with the MOD Function
		Summing Things Up
			Using SUBTOTAL
			Using SUMPRODUCT
			Using SUMIF and SUMIFS
		Getting an Angle on Trigonometry
			Three basic trigonometry functions
			Degrees and radians
Part 3 Solving with Statistics
	Chapter 9 Throwing Statistics a Curve
		Getting Stuck in the Middle with AVERAGE, MEDIAN, and MODE
		Deviating from the Middle
			Measuring variance
			Analyzing deviations
			Looking for normal distribution
			Skewing from the norm
			Comparing data sets
		Analyzing Data with Percentiles and Bins
			QUARTILE.INC and QUARTILE.EXC
			PERCENTILE.INC and PERCENTILE.EXC
			RANK
			PERCENTRANK
			FREQUENCY
			MIN and MAX
			LARGE and SMALL
		Going for the Count
			COUNT and COUNTA
			COUNTIF
	Chapter 10 Using Significance Tests
		Testing to the T
		Comparing Results with an Estimate
	Chapter 11 Rolling the Dice on Predictions and Probability
		Modeling
			Linear model
			Exponential model
		Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data
		What’s Ahead: Using FORECAST, TREND, and GROWTH to Make Predictions
			FORECAST
			TREND
			GROWTH
		Using NORM.DIST and POISSON.DIST to Determine Probabilities
			NORM.DIST
			POISSON.DIST
Part 4 Dancing with Data
	Chapter 12 Dressing Up for Date Functions
		Understanding How Excel Handles Dates
		Formatting Dates
		Making a Date with DATE
		Breaking a Date with DAY, MONTH, and YEAR
			Isolating the day
			Isolating the month
			Isolating the year
		Converting a Date from Text
		Finding Out What TODAY Is
			Counting the days until your birthday
			Counting your age in days
		Determining the Day of the Week
		Working with Workdays
			Determining workdays in a range of dates
			Workdays in the future
		Calculating Time Between Two Dates with the DATEDIF Function
	Chapter 13 Keeping Well-Timed Functions
		Understanding How Excel Handles Time
		Formatting Time
		Keeping TIME
		Converting Text to Time with TIMEVALUE
		Deconstructing Time with HOUR, MINUTE, and SECOND
			Isolating the hour
			Isolating the minute
			Isolating the second
		Finding the Time NOW
		Calculating Elapsed Time Over Days
	Chapter 14 Using Lookup, Logical, and Reference Functions
		Testing on One Condition
		Choosing the Right Value
		Let’s Be Logical
			NOT
			AND and OR
			XOR
		Finding Where the Data Is
			ADDRESS
			INDIRECT
			ROW, ROWS, COLUMN, and COLUMNS
			OFFSET
		Looking It Up
			HLOOKUP and VLOOKUP
			XLOOKUP
			MATCH and INDEX
			FORMULATEXT
			NUMBERVALUE
	Chapter 15 Digging Up the Facts
		Getting Informed with the CELL Function
		Getting Information About Excel and Your Computer System
		Finding What IS and What IS Not
			ISERR, ISNA, and ISERROR
			ISBLANK, ISNONTEXT, ISTEXT, and ISNUMBER
		Getting to Know Your Type
	Chapter 16 Writing Home about Text Functions
		Breaking Apart Text
			Bearing to the LEFT
			Swinging to the RIGHT
			Staying in the MIDdle
			Finding the long of it with LEN
		Putting Text Together with CONCATENATE
		Changing Text
			Making money
			Turning numbers into text
			Repeating text
			Swapping text
			Giving text a trim
			Making a case
		Comparing, Finding, and Measuring Text
			Going for perfection with EXACT
			Finding and searching
	Chapter 17 Playing Records with Database Functions
		Putting Your Data into a Database Structure
		Working with Database Functions
			Establishing your database
			Establishing the criteria area
		Fine-Tuning Criteria with AND and OR
		Adding Only What Matters with DSUM
		Going for the Middle with DAVERAGE
		Counting Only What Matters with DCOUNT
		Finding Highest and Lowest with DMIN and DMAX
		Finding Duplicate Values with DGET
		Being Productive with DPRODUCT
Part 5 The Part of Tens
	Chapter 18 Ten Tips for Working with Formulas
		Master Operator Precedence
		Display Formulas
		Fix Formulas
		Use Absolute References
		Turn Calc On/Turn Calc Off
		Use Named Areas
		Use Formula Auditing
		Use Conditional Formatting
		Use Data Validation
		Create Your Own Functions
	Chapter 19 Ten Ways to Get Fancy with Excel
		Calculating Data from Multiple Sheets
		Getting Data from the Internet
		Determining the Needed Number
		Removing Duplicates
		Getting to the Last Row of Your Data
		Freezing Panes
		Splitting a Worksheet
		Filling Cells
		Adding Notes to Cells
		Getting More Information about a Workbook or Worksheet
	Chapter 20 Ten Really Cool Functions
		Work with Hexadecimal, Octal, Decimal, and Binary Numbers
		Convert Units of Measurement
		Find the Greatest Common Divisor and the Least Common Multiple
		Easily Generate a Random Number
		Convert to Roman Numerals
		Factor in a Factorial
		Determine Part of a Year with YEARFRAC
		Find the Data TYPE
		Find the LENgth of Your Text
		Just in CASE
Index
EULA