Chapter 5: Create Advanced Formulas with Functions

The most commonly used functions in Excel like IF, VLOOKUP, SUM, COUNT etc. were covered in detail in my Excel 2019 Basics book. For brevity, this book will focus on the more advanced functions that were not covered in that book. Many of these functions are relatively new to Excel and were introduced from Excel 2016 onwards. Functions are predesigned formulas, so they are tried and tested, and all you have to do is supply the inputs to get the results you need.

In this chapter, we will cover:

• Conditional statements which combine logical and aggregate functions.
• Functions that generate random numbers, especially for producing test or sample data.
• Functions to round numbers up or down.
• How to manipulate and rearrange text with functions.
• Specialised functions for financial accounting.
• Installing the Analysis ToolPak which provides more functions for carrying out complex statistical and engineering analysis.