100 Nos Of Formula and Functions
In MSExcel
1-20: Basic Mathematical Functions
1. SUM - Adds up a range of numbers.
o Example: =SUM(A1:A10)
For Detail --->Click here
2. AVERAGE - Calculates the average of a range of numbers.
o Example: =AVERAGE(B1:B10)
For Detail --->Click here
3. MIN - Finds the smallest number in a range.
o Example: =MIN(C1:C10)
For Detail --->Click here
4. MAX - Finds the largest number in a range.
o Example: =MAX(D1:D10)
For Detail --->Click here
5. COUNT - Counts the number of cells with numbers in a range.
o Example: =COUNT(E1:E10)
For Detail --->Click here
6. COUNTA - Counts non-empty cells.
o Example: =COUNTA(F1:F10)
For Detail --->Click here
7. COUNTIF - Counts cells that meet a specific condition.
o Example: =COUNTIF(G1:G10, ">10")
For Detail --->Click here
8. SUMIF - Adds cells based on a condition.
o Example: =SUMIF(H1:H10, ">5")
For Detail --->Click here
9. SUMIFS - Adds cells based on multiple conditions.
o Example: =SUMIFS(I1:I10, J1:J10, ">5", K1:K10, "<20")
For Detail --->Click here
10. ROUND - Rounds a number to a specified number of digits.
o Example: =ROUND(L1, 2)
For Detail --->Click here
11. ROUNDUP - Rounds a number up.
o Example: =ROUNDUP(M1, 0)
For Detail --->Click here
12. ROUNDDOWN - Rounds a number down.
o Example: =ROUNDDOWN(N1, 1)
For Detail --->Click here
13. INT - Rounds a number down to the nearest integer.
o Example: =INT(O1)
14. MOD - Returns the remainder of a division.
o Example: =MOD(P1, 3)
15. PRODUCT - Multiplies a range of numbers.
o Example: =PRODUCT(Q1:Q10)
16. POWER - Raises a number to a power.
o Example: =POWER(R1, 2)
17. SQRT - Calculates the square root.
o Example: =SQRT(S1)
18. EXP - Returns e raised to a power.
o Example: =EXP(T1)
19. ABS - Returns the absolute value.
o Example: =ABS(U1)
20. RAND - Generates a random number between 0 and 1.
o Example: =RAND()
21-40: Logical Functions
21. IF - Tests a condition and returns a value if true or false.
o Example: =IF(A1 > 10, "Yes", "No")
22. AND - Returns TRUE if all conditions are met.
o Example: =AND(A1 > 5, B1 < 10)
23. OR - Returns TRUE if any condition is met.
o Example: =OR(A1 > 5, B1 < 10)
24. NOT - Reverses the logic of an argument.
o Example: =NOT(A1 > 5)
25. IFERROR - Returns a specified value if a formula results in an error.
o Example: =IFERROR(A1/B1, "Error")
26. IFNA - Returns a specified value if #N/A error occurs.
o Example: =IFNA(VLOOKUP(A1, B1:B10, 2, FALSE), "Not Found")
27. XOR - Returns TRUE if an odd number of arguments are TRUE.
o Example: =XOR(A1 > 5, B1 > 5)
28.SWITCH - Chooses a value from a list of options.
o Example: =SWITCH(A1, 1, "One", 2, "Two", "Other")
29.IFS - Tests multiple conditions and returns the first true value.
o Example: =IFS(A1 = "Red", "Stop", A1 = "Yellow", "Slow")
30.CHOOSE - Returns a value from a list based on a position.
o Example: =CHOOSE(2, "Apple", "Banana", "Cherry")
31. TRUE - Represents a TRUE logical value.
o Example: =TRUE()
32.FALSE - Represents a FALSE logical value.
o Example: =FALSE()
33.ISNUMBER - Checks if a value is a number.
o Example: =ISNUMBER(A1)
34.ISEVEN - Checks if a number is even.
o Example: =ISEVEN(A1)
35.ISODD - Checks if a number is odd.
o Example: =ISODD(A1)
36.ISERROR - Checks if a value is an error.
o Example: =ISERROR(A1)
37.ISERR - Checks for any error except #N/A.
o Example: =ISERR(A1)
38.ISBLANK - Checks if a cell is empty.
o Example: =ISBLANK(A1)
39.ISLOGICAL - Checks if a value is TRUE or FALSE.
o Example: =ISLOGICAL(A1)
40.ISFORMULA - Checks if a cell contains a formula.
o Example: =ISFORMULA(A1)
41-60: Text Functions
41. CONCATENATE - Joins multiple strings into one.
o Example: =CONCATENATE(A1, B1)
42.TEXTJOIN - Joins text with a delimiter.
o Example: =TEXTJOIN(", ", TRUE, A1:C1)
43.LEFT - Extracts a specific number of characters from the left.
o Example: =LEFT(A1, 5)
44.RIGHT - Extracts characters from the right.
o Example: =RIGHT(A1, 3)
45.MID - Extracts characters from the middle of a string.
o Example: =MID(A1, 2, 3)
46.LEN - Returns the length of a text string.
o Example: =LEN(A1)
47.TRIM - Removes extra spaces.
o Example: =TRIM(A1)
48.LOWER - Converts text to lowercase.
o Example: =LOWER(A1)
49.UPPER - Converts text to uppercase.
o Example: =UPPER(A1)
50.PROPER - Capitalizes the first letter of each word.
o Example: =PROPER(A1)
51. REPT - Repeats text a specified number of times.
o Example: =REPT("*", 5)
52.SUBSTITUTE - Replaces specific text.
o Example: =SUBSTITUTE(A1, "old", "new")
53.REPLACE - Replaces part of a text string based on position.
o Example: =REPLACE(A1, 2, 3, "new")
54.FIND - Finds the position of text.
o Example: =FIND("text", A1)
55.SEARCH - Finds position, case-insensitive.
o Example: =SEARCH("text", A1)
56.TEXT - Formats a number as text.
o Example: =TEXT(A1, "$#,##0.00")
57.DOLLAR - Formats a number as currency.
o Example: =DOLLAR(A1, 2)
58.VALUE - Converts text to a number.
o Example: =VALUE(A1)
59.EXACT - Checks if two strings are identical.
o Example: =EXACT(A1, B1)
60.CODE - Returns a character code.
o Example: =CODE(A1)
61-80: Date and Time Functions
61. NOW - Returns the current date and time.
o Example: =NOW()
62.TODAY - Returns the current date.
o Example: =TODAY()
63.YEAR - Extracts the year from a date.
o Example: =YEAR(A1)
64.MONTH - Extracts the month from a date.
o Example: =MONTH(A1)
65.DAY - Extracts the day from a date.
o Example: =DAY(A1)
66.HOUR - Extracts the hour from a time.
o Example: =HOUR(A1)
67.MINUTE - Extracts the minute from a time.
o Example: =MINUTE(A1)
68.SECOND - Extracts the second from a time.
o Example: =SECOND(A1)
69.WEEKDAY - Returns the day of the week as a number.
o Example: =WEEKDAY(A1)
70.WEEKNUM - Returns the week number of the year.
o Example: =WEEKNUM(A1)
71. DATEDIF - Calculates the difference between two dates.
o Example: =DATEDIF(A1, B1, "d")
72.EDATE - Adds or subtracts months to/from a date.
o Example: =EDATE(A1, 3)
73.EOMONTH - Returns the last day of the month after a certain number of months.
o Example: =EOMONTH(A1, 1)
74.DATE - Creates a date from year, month, and day.
o Example: =DATE(2024, 10, 25)
75.TIME - Creates a time from hour, minute, and second.
o Example: =TIME(14, 30, 0)
76.YEARFRAC - Calculates the fraction of the year between two dates.
o Example: =YEARFRAC(A1, B1)
77.NETWORKDAYS - Counts the workdays between two dates.
o Example: =NETWORKDAYS(A1, B1)
78.WORKDAY - Calculates the workday after a specified number of days.
o Example: =WORKDAY(A1, 5)
79.ISOWEEKNUM - Returns the ISO week number of the year.
o Example: =ISOWEEKNUM(A1)
80.DATEDIF (alternative use) - With different units, like "y" for years, "m" for months, or "d" for days.
o Example: =DATEDIF(A1, B1, "m")
81-90: Financial Functions
81. PMT - Calculates the payment for a loan based on constant payments and a constant interest rate.
o Example: =PMT(5%/12, 60, -10000)
82.PV - Returns the present value of an investment.
o Example: =PV(5%/12, 60, -200)
83.FV - Calculates the future value of an investment.
o Example: =FV(5%/12, 60, -100)
84.NPV - Returns the net present value of an investment based on periodic cash flows.
o Example: =NPV(0.1, A2:A10)
85.IRR - Returns the internal rate of return for a series of cash flows.
o Example: =IRR(A2:A10)
86.RATE - Returns the interest rate per period of an annuity.
o Example: =RATE(60, -200, 10000)
87.SLN - Returns the straight-line depreciation of an asset.
o Example: =SLN(10000, 2000, 5)
88.DB - Returns the depreciation of an asset using the declining balance method.
o Example: =DB(10000, 1000, 5, 1)
89.DDB - Returns the depreciation of an asset using the double-declining balance method.
o Example: =DDB(10000, 1000, 5, 1)
90.CUMIPMT - Returns the cumulative interest paid on a loan between two periods.
o Example: =CUMIPMT(5%/12, 60, 10000, 1, 12, 0)
91-100: Lookup and Reference Functions
91. VLOOKUP - Searches for a value in the first column of a range and returns a value in the same row from another column.
o Example: =VLOOKUP(A1, B2:D10, 3, FALSE)
92.HLOOKUP - Searches for a value in the first row of a range and returns a value in the same column from another row.
o Example: =HLOOKUP(A1, B1:H10, 4, FALSE)
93.INDEX - Returns a value from within a specified range based on row and column numbers.
o Example: =INDEX(A1:C10, 2, 3)
94.MATCH - Returns the position of a value in a range.
o Example: =MATCH("Apple", A1:A10, 0)
95.LOOKUP - Finds a value in a range and returns a corresponding value.
o Example: =LOOKUP(A1, B1:B10, C1:C10)
96.CHOOSE - Returns a value from a list based on a specified position.
o Example: =CHOOSE(2, "Apple", "Banana", "Cherry")
97.OFFSET - Returns a reference to a range that is a specified number of rows and columns from a cell.
o Example: =OFFSET(A1, 1, 2)
98.INDIRECT - Returns the reference specified by a text string.
o Example: =INDIRECT("A" & B1)
99.TRANSPOSE - Converts a vertical range to a horizontal range and vice versa.
o Example: =TRANSPOSE(A1:A10)
100.FORMULATEXT - Returns the formula in a given cell as text. - Example: =FORMULATEXT(A1)
These functions are essential tools for both beginners and advanced users, offering powerful capabilities for data analysis and calculations in Excel.
