Scribd
Upload
40 views2 pages

Advanced Excel Formulas

The document provides a comprehensive overview of advanced Excel formulas categorized into various functions including Lookup & Reference, Logical, Math & Stats, Date & Time, Text, Financial, Array & Dynamic, and Other Useful Functions. Each category includes specific formulas with examples for practical application. Additionally, it features a grade formula to assess performance based on numeric scores.

Uploaded by

jigsv783
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
40 views2 pages

Advanced Excel Formulas

The document provides a comprehensive overview of advanced Excel formulas categorized into various functions including Lookup & Reference, Logical, Math & Stats, Date & Time, Text, Financial, Array & Dynamic, and Other Useful Functions. Each category includes specific formulas with examples for practical application. Additionally, it features a grade formula to assess performance based on numeric scores.

Uploaded by

jigsv783
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Advanced Excel Formulas with Examples

1. Lookup & Reference Functions


INDEX + MATCH: =INDEX(B2:B10, MATCH("John", A2:A10, 0))

XLOOKUP: =XLOOKUP("John", A2:A10, B2:B10, "Not Found")

VLOOKUP: =VLOOKUP("John", A2:C10, 2, FALSE)

HLOOKUP: =HLOOKUP("Q1", A1:D3, 2, FALSE)

2. Logical Functions
IF: =IF(A2>50, "Pass", "Fail")

IF + AND/OR: =IF(AND(A2>50, B2="Yes"), "OK", "Check")

IFS: =IFS(A2<50, "Low", A2<80, "Medium", A2>=80, "High")

3. Math & Stats Functions


SUMIF: =SUMIF(A2:A10, ">100", B2:B10)

COUNTIF: =COUNTIF(A2:A10, "Apple")

AVERAGEIF: =AVERAGEIF(A2:A10, ">50", B2:B10)

SUMPRODUCT: =SUMPRODUCT((A2:A10="Apple")*(B2:B10))

4. Date & Time Functions


TODAY: =TODAY()

NOW: =NOW()

DATEDIF: =DATEDIF(A2, B2, "Y")

TEXT: =TEXT(TODAY(), "dd-mmm-yyyy")

5. Text Functions
LEFT: =LEFT(A2, 4)

RIGHT: =RIGHT(A2, 3)

MID: =MID(A2, 2, 5)

LEN: =LEN(A2)

TRIM: =TRIM(A2)

CONCAT: =CONCAT(A2, " ", B2)

TEXTJOIN: =TEXTJOIN(" ", TRUE, A2:C2)


6. Financial Functions
PMT: =PMT(5%/12, 60, -10000)

NPV: =NPV(0.08, B2:B6)

IRR: =IRR(B2:B7)

7. Array & Dynamic Functions


FILTER: =FILTER(A2:B10, B2:B10="Apple")

UNIQUE: =UNIQUE(A2:A10)

SORT: =SORT(A2:A10)

8. Other Useful Functions


INDIRECT: =INDIRECT("A" & B1)

OFFSET: =OFFSET(A1, 2, 1)

CHOOSE: =CHOOSE(2, "Red", "Green", "Blue")

9. Grade Formula
Grade System: =IF(A2>=90, "A+", IF(A2>=80, "A", IF(A2>=70, "B", IF(A2>=60, "C", "Fail"))))

You might also like