Microsoft Excel Advanced Functions and Formulas

✍ Scribed by Sheikh, Ahmed

• Year: 2021
• Tongue: English
• Leaves: 92
• Category: Library

✦ Synopsis

Excel has its immense purposeful applications. 95% of the users apply the basic form. There are functions and advanced excel formula that can be used for complex calculations. The functions are designed for easy lookup and formatting of large pool of data whereas the advanced excel formula are implemented to get new information from a given particular set of data.

✦ Table of Contents

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
Example
HLOOKUP Function
Syntax
Example
MATCH Function
Syntax
Example
INDEX Function
Syntax
Example
CHOOSE Function
Syntax
Example
FORMULATEXT Function
Syntax
Example
ADDRESS Function
Syntax
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
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
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
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
Find the Three Largest Numbers
Sum the Three Largest Numbers
Find Unique Values
Multiplication Table in Excel Created Using Array Formulas
EXCEL 365 Functions
SORT Function
SORTBY Function
SEQUENCE Function
Summary
Appendix A:​ Formula Ready Reckoner
Index.

✦ Subjects

Microsoft Excel Advanced Functions and Formulas