Advanced Excel Formulas List - Summary
Advanced Excel formulas list PDF is a 14-page reference guide issued by Ela Area Public Library (eapl.org) for spreadsheet users and learners. It explains how to build complex calculations in Microsoft Excel using logical tests, multi-condition sums, lookups, and financial equations.
The document is written in English and includes hands-on exercises with sample cell coordinates. For beginners who need standard arithmetic operations, refer to the Excel formulas list or review MS Excel shortcut keys to speed up data entry.
Advanced Excel formulas and examples
The table below lists common advanced formulas used for data analysis, conditional math, and lookup tasks in Excel:
| Function | Example formula | What it does |
|---|---|---|
| VLOOKUP | =VLOOKUP(A2, D2:F20, 3, FALSE) | Searches for value A2 in the first column of table D2:F20 and returns the matching value from column 3. |
| INDEX & MATCH | =INDEX(B2:B50, MATCH(E2, A2:A50, 0)) | Finds the row position of E2 in column A and returns the corresponding entry from column B without column order restrictions. |
| SUMIFS | =SUMIFS(C2:C50, A2:A50, “North”, B2:B50, “>500”) | Sums numbers in column C only when region in column A is North and sales in column B exceed 500. |
| COUNTIFS | =COUNTIFS(A2:A50, “Completed”, B2:B50, “<=30”) | Counts total rows that meet both criteria across specified cell ranges. |
| IF with AND | =IF(AND(B2>=50, C2>=75), “Pass”, “Fail”) | Returns Pass when both score tests are true, otherwise returns Fail. |
| XLOOKUP | =XLOOKUP(F2, A2:A100, C2:C100, “Not Found”) | Looks up value F2 in column A and returns the item from column C, with custom text if no match exists. |
| PMT | =PMT(7.5%/12, 60, -500000) | Calculates the fixed monthly installment for a 5-lakh loan over 60 months at 7.5 percent annual interest. |
What is covered in the PDF
The 14 pages of this training guide cover step-by-step instructions for these workbook topics:
- Logical operators and nested IF statements with AND conditions.
- SUMIF and SUMIFS calculations across dated transaction records.
- VLOOKUP and HLOOKUP table arrays, relative column index numbers, and absolute cell references using dollar signs.
- Loan amortization terms including payment schedules (PMT), interest portions (IPMT), and number of periods (NPER).
- Capital valuation through Net Present Value (NPV) and Present Value (PV) comparisons.
- Straight-line and declining depreciation schedules alongside date conversion functions.
