Created by @affairsassam

100 MS Excel Formulae

Mathematical Functions

1. =SUM(A1:A10)
o Adds up all numbers in the range A1:A10 .
o Use Case: Calculate total sales, expenses, or revenue.
2. =AVERAGE(A1:A10)
o Calculates the average of numbers in the range A1:A10 .
o Use Case: Find the average score, temperature, or sales.
3. =MIN(A1:A10)
o Finds the smallest number in the range A1:A10 .
o Use Case: Identify the lowest score, temperature, or price.
4. =MAX(A1:A10)
o Finds the largest number in the range A1:A10 .
o Use Case: Identify the highest score, temperature, or price.
5. =COUNT(A1:A10)
o Counts the number of cells with numbers in the range A1:A10 .
o Use Case: Count the number of sales transactions.
6. =COUNTA(A1:A10)
o Counts the number of non-empty cells in the range A1:A10 .
o Use Case: Count the number of entries in a list.
7. =ROUND(A1, 2)
o Rounds a number to 2 decimal places.
o Use Case: Round prices to 2 decimal places.
8. =ROUNDUP(A1, 0)
o Rounds a number up to the nearest integer.
o Use Case: Round up delivery times.
9. =ROUNDDOWN(A1, 0)
o Rounds a number down to the nearest integer.
o Use Case: Round down age to the nearest whole number.
10. =MOD(A1, B1)
o Returns the remainder after division.
o Use Case: Check if a number is even or odd.
11. =ABS(A1)
o Returns the absolute value of a number.
o Use Case: Convert negative numbers to positive.
12. =SQRT(A1)
o Returns the square root of a number.
o Use Case: Calculate standard deviation.
Created by @affairsassam

13. =POWER(A1, 2)
o Raises a number to a specified power.
o Use Case: Calculate squares or cubes.
14. =PRODUCT(A1:A10)
o Multiplies all numbers in the range A1:A10 .
o Use Case: Calculate total product of values.
15. =SUMPRODUCT(A1:A10, B1:B10)
o Multiplies corresponding values and returns the sum.
o Use Case: Calculate total revenue (Quantity × Price).
16. =RAND()
o Generates a random number between 0 and 1.
o Use Case: Randomize data for sampling.
17. =RANDBETWEEN(1, 100)
o Generates a random integer between 1 and 100.
o Use Case: Randomly assign numbers.
18. =FACT(A1)
o Returns the factorial of a number.
o Use Case: Calculate permutations or combinations.
19. =LOG(A1)
o Returns the logarithm of a number.
o Use Case: Analyze exponential data.
20. =LN(A1)
o Returns the natural logarithm of a number.
o Use Case: Analyze growth rates.

Statistical Functions

21. =MEDIAN(A1:A10)
o Returns the median of a dataset.
o Use Case: Find the middle value in a range.
22. =MODE(A1:A10)
o Returns the most frequently occurring value.
o Use Case: Identify the most common value.
23. =STDEV(A1:A10)
o Calculates the standard deviation of a dataset.
o Use Case: Measure data variability.
24. =VAR(A1:A10)
o Calculates the variance of a dataset.
Created by @affairsassam

o Use Case: Analyze data spread.

25. =CORREL(A1:A10, B1:B10)
o Returns the correlation coefficient between two datasets.
o Use Case: Measure the relationship between variables.
26. =FORECAST(A1, B1:B10, C1:C10)
o Predicts a future value based on existing data.
o Use Case: Forecast sales or trends.
27. =TREND(B1:B10, A1:A10, A11)
o Returns values along a linear trend.
o Use Case: Predict future values.
28. =PERCENTILE(A1:A10, 0.9)
o Returns the 90th percentile of a dataset.
o Use Case: Analyze data distribution.
29. =QUARTILE(A1:A10, 3)
o Returns the 3rd quartile of a dataset.
o Use Case: Analyze data distribution.
30. =RANK(A1, A1:A10)
o Returns the rank of a number in a dataset.
o Use Case: Rank sales performance.

Logical Functions

31. =IF(A1>50, "Pass", "Fail")

o Checks if a condition is met and returns a value.
o Use Case: Grade students based on scores.
32. =AND(A1>50, B1<100)
o Returns TRUE if all conditions are met.
o Use Case: Check if a value is within a range.
33. =OR(A1>50, B1<100)
o Returns TRUE if any condition is met.
o Use Case: Check if a value meets at least one condition.
34. =NOT(A1>50)
o Reverses the logic of a condition.
o Use Case: Exclude values above a threshold.
35. =IFERROR(A1/B1, "Error")
o Returns a custom message if a formula results in an error.
o Use Case: Handle division by zero errors.
36. =IFS(A1>90, "A", A1>80, "B", A1>70, "C")
Created by @affairsassam

oChecks multiple conditions and returns a value.

oUse Case: Assign grades based on scores.
37. =SWITCH(A1, 1, "Low", 2, "Medium", 3, "High")
o Returns a value based on a specific input.
o Use Case: Categorize data into groups.
38. =ISBLANK(A1)
o Checks if a cell is empty.
o Use Case: Validate data entry.
39. =ISNUMBER(A1)
o Checks if a cell contains a number.
o Use Case: Validate numeric data.
40. =ISTEXT(A1)
o Checks if a cell contains text.
o Use Case: Validate text data.

Text Functions

41. =LEFT(A1, 5)
o Extracts the first 5 characters from a text string.
o Use Case: Extract area codes from phone numbers.
42. =RIGHT(A1, 3)
o Extracts the last 3 characters from a text string.
o Use Case: Extract file extensions (e.g., .xlsx).
43. =MID(A1, 2, 5)
o Extracts a substring from the middle of a text string.
o Use Case: Extract part of a product code.
44. =LEN(A1)
o Returns the length of a text string.
o Use Case: Validate text input length.
45. =CONCATENATE(A1, " ", B1)
o Combines text from multiple cells.
o Use Case: Merge first and last names.
46. =TEXTJOIN(", ", TRUE, A1:A10)
o Combines text from multiple cells with a delimiter.
o Use Case: Create a comma-separated list.
47. =TRIM(A1)
o Removes extra spaces from text.
o Use Case: Clean up imported data.
Created by @affairsassam

48. =SUBSTITUTE(A1, "old", "new")

o Replaces specific text in a string.
o Use Case: Update product names.
49. =UPPER(A1)
o Converts text to uppercase.
o Use Case: Standardize text formatting.
50. =LOWER(A1)
o Converts text to lowercase.
o Use Case: Standardize text formatting.

Date and Time Functions

51. =TODAY()
o Returns the current date.
o Use Case: Track today’s date in reports.
52. =NOW()
o Returns the current date and time.
o Use Case: Timestamp entries.
53. =DATE(2023, 10, 15)
o Creates a date from year, month, and day.
o Use Case: Generate specific dates.
54. =DAY(A1)
o Extracts the day from a date.
o Use Case: Analyze daily data.
55. =MONTH(A1)
o Extracts the month from a date.
o Use Case: Analyze monthly data.
56. =YEAR(A1)
o Extracts the year from a date.
o Use Case: Analyze yearly data.
57. =EOMONTH(A1, 0)
o Returns the last day of the month.
o Use Case: Calculate month-end deadlines.
58. =WEEKDAY(A1)
o Returns the day of the week for a date.
o Use Case: Check if a date falls on a weekend.
59. =WORKDAY(A1, 5)
o Adds working days to a date.
Created by @affairsassam

o Use Case: Calculate project deadlines.

60. =NETWORKDAYS(A1, B1)
o Calculates the number of working days between two dates.
o Use Case: Calculate project timelines.

Lookup and Reference Functions

61. =VLOOKUP(A1, B1:C10, 2, FALSE)

o Looks up a value in a table and returns a corresponding value.
o Use Case: Find employee details using their ID.
62. =HLOOKUP(A1, B1:F2, 2, FALSE)
o Similar to VLOOKUP but searches horizontally.
o Use Case: Find data in a horizontal table.
63. =INDEX(A1:C10, 2, 3)
o Returns the value at the intersection of a specific row and column.
o Use Case: Retrieve data from a specific cell in a table.
64. =MATCH(A1, B1:B10, 0)
o Finds the position of a value in a range.
o Use Case: Locate the position of a product in a list.
65. =CHOOSE(2, "Red", "Green", "Blue")
o Returns a value from a list based on an index.
o Use Case: Assign categories based on a number.
66. =INDIRECT("A" & B1)
o Returns a reference specified by a text string.
o Use Case: Dynamically reference cells.
67. =OFFSET(A1, 2, 3)
o Returns a reference offset from a starting point.
o Use Case: Create dynamic ranges.
68. =HYPERLINK("https://example.com", "Click Here")
o Creates a clickable hyperlink.
o Use Case: Link to external resources.
69. =TRANSPOSE(A1:C3)
o Transposes rows and columns.
o Use Case: Switch row and column data.
70. =UNIQUE(A1:A10)
o Extracts unique values from a range.
o Use Case: Remove duplicates from a list.
Created by @affairsassam

Financial Functions

71. =PMT(rate, nper, pv)

o Calculates loan payments.
o Use Case: Calculate monthly mortgage payments.
72. =FV(rate, nper, pmt)
o Calculates the future value of an investment.
o Use Case: Plan for retirement savings.
73. =PV(rate, nper, pmt)
o Calculates the present value of an investment.
o Use Case: Evaluate investment opportunities.
74. =NPV(rate, value1, value2)
o Calculates the net present value of cash flows.
o Use Case: Analyze investment profitability.
75. =IRR(values)
o Calculates the internal rate of return for cash flows.
o Use Case: Evaluate investment profitability.
76. =RATE(nper, pmt, pv)
o Calculates the interest rate for a loan or investment.
o Use Case: Compare loan offers.
77. =NPER(rate, pmt, pv)
o Calculates the number of periods for a loan or investment.
o Use Case: Plan loan repayment.
78. =SLN(cost, salvage, life)
o Calculates straight-line depreciation.
o Use Case: Calculate asset depreciation.
79. =DB(cost, salvage, life, period)
o Calculates declining balance depreciation.
o Use Case: Calculate asset depreciation.
80. =SYD(cost, salvage, life, period)
o Calculates sum-of-years' digits depreciation.
o Use Case: Calculate asset depreciation.

Logical and Error Handling Functions

81. =IFNA(A1, "Not Available")

o Returns a custom message if a formula results in #N/A.
Created by @affairsassam

o Use Case: Handle missing data.

82. =ISERROR(A1)
o Checks if a cell contains an error.
o Use Case: Validate formulas.
83. =ISNA(A1)
o Checks if a cell contains #N/A.
o Use Case: Validate lookup results.
84. =IFERROR(A1/B1, "Error")
o Returns a custom message if a formula results in an error.
o Use Case: Handle division by zero errors.
85. =ISEVEN(A1)
o Checks if a number is even.
o Use Case: Categorize data.
86. =ISODD(A1)
o Checks if a number is odd.
o Use Case: Categorize data.
87. =ISNUMBER(A1)
o Checks if a cell contains a number.
o Use Case: Validate numeric data.
88. =ISTEXT(A1)
o Checks if a cell contains text.
o Use Case: Validate text data.
89. =ISBLANK(A1)
o Checks if a cell is empty.
o Use Case: Validate data entry.
90. =ISREF(A1)
o Checks if a cell contains a reference.
o Use Case: Validate formulas.

Array Functions

91. =SUMIF(A1:A10, ">50")

o Adds values in a range based on a condition.
o Use Case: Sum sales for a specific region.
92. =COUNTIF(A1:A10, ">50")
o Counts cells that meet a condition.
o Use Case: Count the number of students who scored above 50.
93. =AVERAGEIF(A1:A10, ">50")
Created by @affairsassam

oCalculates the average of cells that meet a condition.

oUse Case: Find the average sales for high-performing products.
94. =SUMIFS(A1:A10, B1:B10, "North", C1:C10, ">100")
o Adds values based on multiple conditions.
o Use Case: Sum sales for a specific region and product.
95. =COUNTIFS(A1:A10, ">50", B1:B10, "North")
o Counts cells that meet multiple conditions.
o Use Case: Count the number of sales in a specific region.
96. =AVERAGEIFS(A1:A10, B1:B10, "North", C1:C10, ">100")
o Calculates the average based on multiple conditions.
o Use Case: Find the average sales for a specific region and product.
97. =FILTER(A1:C10, B1:B10="North")
o Filters data based on a condition.
o Use Case: Extract sales data for a specific region.
98. =SORT(A1:A10)
o Sorts a range in ascending order.
o Use Case: Sort data alphabetically or numerically.
99. =SORTBY(A1:A10, B1:B10)
o Sorts a range based on another range.
o Use Case: Sort data by a specific column.
100. =UNIQUE(A1:A10)
o Extracts unique values from a range.

Use Case: Remove duplicates from a list

For more: visit email: affairsassam@gmail.com

Join Free Telegram Group
For Details of Paid Batch and Content : Click Here