Microsoft Excel Functions Quick Reference: For High-Quality Data Analysis, Dashboards, and More

Description

Intermediate Table of Contents About the Author About the Technical Reviewer Acknowledgments Introduction Chapter 1: What Is an Excel Function? What Is an Excel Function? How to Use Excel Functions Types of Excel Functions Categories of Excel Functions Summary Chapter 2: Text Functions LEFT Function Syntax Example RIGHT Function Syntax Example MID Function Syntax Example UPPER Function Syntax Example LOWER Function Syntax Example PROPER Function Syntax Example TRIM Function Syntax Example LEN Function Syntax Example FIND Function Syntax Example SEARCH Function Syntax Example CONCATENATE/& Function Syntax Example VALUE Function Syntax Example TEXT Function Syntax Example EXACT Function Syntax Example REPLACE Function Syntax Example SUBSTITUTE Function Syntax Examples REPT Function Syntax Example T Function Syntax Example TEXTJOIN Function Syntax Example Summary Chapter 3: Date Functions TODAY Function Syntax Example DAY Function Syntax Example MONTH Function Syntax Example YEAR Function Syntax Example EDATE Function Syntax Example EOMONTH Function Syntax Example WEEKDAY Function Syntax Example WEEKNUM Function Syntax Example DATE Function Syntax Example DATEVALUE Function Syntax Example WORKDAY Function Syntax Example NETWORKDAYS Function Syntax Example DATEDIF Function Syntax Example Summary Chapter 4: Time Functions NOW Function Syntax Example HOUR Function Syntax Example MINUTE Function Syntax Example SECOND Function Syntax Example TIME Function Syntax Example TIMEVALUE Function Syntax Example Summary Chapter 5: Named Ranges and Excel Tables Named Ranges Advantages of Named Ranges Create a Named Range Rules for Creating Names Benefits of Named Ranges Excel Tables Benefits of Using Excel Tables Creating a Table Styling a Table Renaming a Table Adding a Reference to Another Table Adding a Total Row to the Table Summary Chapter 6: Lookup & Reference Functions VLOOKUP Function Limitations of VLOOKUP Syntax Possible Causes of Value Not Found by VLOOKUP Example HLOOKUP Function Syntax Example MATCH Function Syntax Example INDEX Function Syntax Example CHOOSE Function Syntax Example FORMULATEXT Function Syntax Example ADDRESS Function Syntax Note About Reference Style A1 Reference Style R1C1 Reference Style Example COLUMN Function Syntax Example COLUMNS Function Syntax Example ROW Function Syntax Example ROWS Function Syntax Example INDIRECT Function Syntax Example Why Use the INDIRECT Function? Possible Issues with INDIRECT Function OFFSET Function Syntax Example Summary Chapter 7: Aggregate Functions SUM Function Syntax Example SUMIF Function Syntax Logical Operators Mathematical Operators Example SUMIFS Function Syntax Example SUMPRODUCT Function Syntax Example COUNT Function Syntax Example COUNTA Function Syntax Example COUNTBLANK Function Syntax Example COUNTIF Function Syntax Example COUNTIFS Function Syntax Example AVERAGE Function Syntax Example AVERAGEIF Function Syntax Example AVERAGEIFS Function Syntax Example SUBTOTAL Function Syntax Example AGGREGATE Function Syntax The Reference Form The Array Form Example MAX Function Syntax Example MIN Function Syntax Example MAXIFS Function Syntax Example MINIFS Function Syntax Example Summary Chapter 8: Logical Functions IF Function Syntax Example AND Function Syntax Example OR Function Syntax Example NOT Function Syntax Example XOR Function Syntax Example IFS Function Syntax Example SWITCH Function Syntax Example Summary Chapter 9: Math Functions ABS Function Syntax Example INT Function SYNTAX Example TRUNC Function Syntax Example ROUND Function Syntax Example CEILING Function Syntax Example FLOOR Function Syntax Example RAND Function Syntax Example RANDBETWEEN Function Syntax Example Summary Chapter 10: Information Functions CELL Function Syntax Example INFO Function Syntax Example ISBLANK Function Syntax Example ISERROR Function Syntax Example ISEVEN Function Syntax Example ISODD Function Syntax Example ISFORMULA Function Syntax Example ISLOGICAL Function Syntax Example ISNA Function Syntax Example ISNONTEXT Function Syntax Example ISNUMBER Function Syntax Example ISREF Function Syntax Example ISTEXT Function Syntax Example N Function Syntax Example NA Function Syntax Example SHEET Function Syntax Example SHEETS Function Syntax Example TYPE Function Syntax Example Summary Chapter 11: Finance Functions FV Function Syntax Example PV Function Syntax Example PMT Function Syntax Example IPMT Function Syntax Example PPMT Function Syntax Example RATE Function Syntax Example NPER Function Syntax Example NPV Function Syntax Example IRR Function Syntax Example XIRR Function Syntax Example XNPV Function Syntax Example Summary Chapter 12: Error Handling Why Do Errors Occur While Using Excel Functions? Different Types of Error Codes Functions Used to Handle Errors IFERROR Function Syntax Example Debugging Excel Formulas Using the F9 Key Formula Tab Summary Chapter 13: Megaformulas What Is a Megaformula? Advantages of Megaformulas Disadvantages of Megaformulas Examples of Megaformulas Dynamic Lookup Using INDEX MATCH Functions Create a Name Using the First Letter of the First Name and the Last Name Create a Name Consisting of Only First and Last Name Reverse a Text String Summary Chapter 14: Array Formulas What Is an Array Formula? Advantages of Array Formulas Disadvantages of Array Formulas Examples of Array Formulas Count the Number of Characters in a Cell Range Traditional Excel Version (Array Formula) Excel 365 Version (Array Formula) Without Array Formula Find the Three Largest Numbers Traditional Excel Version Excel 365 Version Sum the Three Largest Numbers Traditional Excel Version Excel 365 Version Find Unique Values Traditional Excel Version Excel 365 Version Multiplication Table in Excel Created Using Array Formulas Traditional Excel Version Excel 365 Version EXCEL 365 Functions SORT Function Syntax SORTBY Function Syntax SEQUENCE Function Syntax Summary Appendix A: Formula Ready Reckoner Index