1. Mathematical Formulas

FormulaDescriptionExample
=SUM(A1:A5)Adds all values from A1 to A5If A1=2, A2=3, result is 5
=AVERAGE(A1:A5)Calculates averageIf A1 to A5 are 1,2,3,4,5 → result is 3
=MIN(A1:A5)Returns minimum valueIf values are 3,1,7 → result is 1
=MAX(A1:A5)Returns maximum valueIf values are 3,1,7 → result is 7
=ROUND(A1, 2)Rounds number to 2 decimal placesA1 = 2.5678 → result = 2.57
=INT(A1)Returns integer partA1 = 5.9 → result = 5

2. Logical Formulas

FormulaDescriptionExample
=IF(A1>10, "Yes", "No")Returns “Yes” if A1 > 10A1 = 15 → Yes
=AND(A1>5, B1<10)TRUE if both are trueA1 = 6, B1 = 9 → TRUE
=OR(A1>5, B1<10)TRUE if either is trueA1 = 3, B1 = 9 → TRUE
=NOT(A1=10)Reverses logical valueA1 = 10 → FALSE

3. Text Functions

FormulaDescriptionExample
=CONCAT(A1, B1)Joins valuesA1 = Hello, B1 = World → HelloWorld
=TEXT(A1, "dd/mm/yyyy")Formats number/dateA1 = 45000 → 18/02/2023
=LEN(A1)Length of stringA1 = Hello → result = 5
=LEFT(A1, 3)First 3 charactersA1 = Excel → Exe
=RIGHT(A1, 2)Last 2 charactersA1 = Excel → el
=MID(A1, 2, 3)Middle charactersA1 = Excel → xce

4. Lookup and Reference

FormulaDescriptionExample
=VLOOKUP(101, A2:B10, 2, FALSE)Vertical lookupLooks for 101 in first column of A2:B10
=HLOOKUP("Jan", A1:D2, 2, FALSE)Horizontal lookupFinds value in 2nd row matching “Jan”
=INDEX(A1:C3, 2, 3)Gets value from 2nd row, 3rd col 
=MATCH(50, A1:A10, 0)Returns position of 50 in rangeIf 50 is in A4 → result = 4

5. Date & Time Functions

FormulaDescriptionExample
=TODAY()Current date23-May-2025
=NOW()Current date & time23-May-2025 10:15 AM
=DAY(A1)Day from dateA1 = 23-May-2025 → result = 23
=MONTH(A1)Month from date→ 5
=YEAR(A1)Year from date→ 2025
=DATEDIF(A1, A2, "d")Difference in days/months/yearsA1 = 01-Jan-2020, A2 = 01-Jan-2025 → 1826 days

6. Financial Functions

FormulaDescriptionExample
=PMT(5%/12, 60, -10000)Monthly loan paymentFor â‚ą10,000 loan, 5% annual rate, 5 years
=FV(5%/12, 60, -2000)Future valueMonthly saving â‚ą2000, 5% interest
=NPV(10%, A1:A5)Net Present ValueA1:A5 = cash flows

7. Counting Functions

FormulaDescriptionExample
=COUNT(A1:A10)Count numbers onlyIf 5 numeric cells → result = 5
=COUNTA(A1:A10)Count all non-emptyIncludes text
=COUNTIF(A1:A10, ">50")Count if > 50 
=COUNTIFS(A1:A10, ">50", B1:B10, "<100")Multiple conditions 

8. Error Handling

FormulaDescriptionExample
=IFERROR(A1/B1, "Error")Avoids #DIV/0!B1 = 0 → “Error”
=ISERROR(A1)TRUE if error 

9. Array and Dynamic Functions (Excel 365+)

FormulaDescriptionExample
=UNIQUE(A1:A10)Removes duplicates 
=SORT(A1:A10)Sorts data 
=FILTER(A1:B10, B1:B10>50)Filters values > 50 
=SEQUENCE(5)Outputs 1 to 5 in cells 
Scroll to Top