Excel Formulas and Functions

Introduction

Who Is This Book For?

How to Use This Book

Assumptions

Practice Files

Chapter 1: Formula Basics

How to Enter a Formula

The Insert Function Dialog Box

Operators in Excel

How to Step Through a Formula

Chapter 2: Lookup and Reference Functions

Looking Up Values in a Range

Find Data with XLOOKUP

Find Data with VLOOKUP

Find Data with HLOOKUP

MATCH Function

XMATCH Function

INDEX Function

Finding Matches with INDEX and XMATCH

Manipulating Columns and Rows

CHOOSECOLS Function

CHOOSEROWS Function

Using an Array Constant

CHOOSE Function

TRANSPOSE Function

COLUMNS and ROWS Functions

Returning Cell Information

FORMULATEXT Function

ADDRESS Function

Filtering and Sorting Ranges

UNIQUE Function

FILTER Function

SORT Function

SORTBY Function

Chapter 3: Logical Functions

Creating Conditional Formulas

IF Function

Nested IF Functions

Advanced IF Functions

IFS Function

SWITCH Function

AND Function

OR Function

Trapping and Replacing Error Values

IFERROR Function

IFNA Function

Chapter 4: Math Functions

Summing and Aggregating Values

SUM Function

SUMIF Function

SUMIFS Function

AGGREGATE Function

Generating Random and Sequential Numbers

RANDBETWEEN Function

RANDARRAY Function

SEQUENCE Function

Rounding Numbers

ROUND Function

ROUNDUP Function

ROUNDDOWN Function

Other Math Functions

Returning the Reminder with MOD

Returning The Square Root with SQRT

Chapter 5: Statistical Functions

Counting Values

COUNT Function

COUNTIF Function

COUNTIFS Function

COUNTA Function

COUNTBLANK Function

Calculating Averages and Extreme Values

AVERAGE Function

AVERAGEIF Function

AVERAGEIFS Function

MAX, MIN, and MEDIAN Functions

MAXIFS and MINIFS Functions

Chapter 6: Date and Time Functions

Date Formats

Add Days, Months, and Years to a Date

DAY, MONTH, and YEAR Functions

DATE Function

EDATE Function

NOW Function

TODAY Function

Calculate the Difference between Two Dates

DATEDIF Function

DAYS Function

NETWORKDAYS Function

Convert Values to Date and Time

DATEVALUE Function

TIME Function

Chapter 7: Text Functions

Find and Extract Substrings

TEXTBEFORE Function

TEXTAFTER Function

TRIM Function

LEN Function

FIND Function

LEFT and RIGHT Functions

LEFTB, RIGHTB Functions

MID Function

Split or Concatenate Text

TEXTSPLIT Function

TEXTJOIN Function

CONCAT Function

Change the Case of Text Values

UPPER, LOWER, and PROPER Functions

Chapter 8: Financial Functions

Definitions

Calculating Investments and Loans

PV Function

FV Function

NPV Function

PMT Function

Calculating Depreciation

SLN Function

SYD Function

DB Function

DDB Function

Glossary