Basic Arithmetic Formulas
1. Addition: =SUM(A1:A5)
Adds the values in cells A1 through A5.
2. Average: =AVERAGE(B1:B10)
Finds the average of values in cells B1 through B10.
3. Minimum Value: =MIN(C1:C20)
Finds the smallest value in cells C1 through C20.
4. Maximum Value: =MAX(D1:D15)
Finds the largest value in cells D1 through D15.
5. Product: =PRODUCT(E1:E3)
Multiplies the values in cells E1, E2, and E3.
Logical Formulas
1. If Statement: =IF(A1>10, "Yes", "No")
Returns "Yes" if the value in A1 is greater than 10; otherwise, returns "No."
2. AND Function: =AND(A1>10, B1<20)
Returns TRUE if both conditions are met.
3. OR Function: =OR(A1>10, B1<20)
Returns TRUE if at least one condition is met.
4. NOT Function: =NOT(A1>10)
Returns TRUE if A1 is NOT greater than 10.
Text Formulas
1. Concatenate: =CONCAT(A1, " ", B1)
Joins the text in A1 and B1 with a space in between.
2. Extract Left: =LEFT(A1, 3)
Extracts the first 3 characters from the text in A1.
3. Extract Right: =RIGHT(A1, 4)
Extracts the last 4 characters from the text in A1.
4. Find Text: =FIND("Apple", A1)
Finds the position of the word "Apple" in cell A1.
5. Uppercase: =UPPER(A1)
Converts the text in A1 to uppercase.
Lookup & Reference Formulas
1. VLOOKUP: =VLOOKUP(101, A2:D10, 2, FALSE)
Looks for 101 in the first column of A2:D10 and returns the corresponding value from
column 2.
2. HLOOKUP: =HLOOKUP("Product", A1:Z2, 2, TRUE)
Searches for "Product" in row 1 and returns the value from row 2.
3. INDEX: =INDEX(A1:D10, 3, 2)
Returns the value in the 3rd row and 2nd column of A1:D10.
4. MATCH: =MATCH(50, B1:B10, 0)
Returns the position of the value 50 in the range B1:B10.
Date & Time Formulas
1. Today’s Date: =TODAY()
Returns the current date.
2. Current Time: =NOW()
Returns the current date and time.
3. Date Difference: =DATEDIF(A1, A2, "D")
Calculates the difference in days between the dates in A1 and A2.
4. Year Extract: =YEAR(A1)
Returns the year from a date in A1.
5. End of Month: =EOMONTH(A1, 1)
Returns the last day of the next month based on the date in A1.
Statistical Formulas
1. Count Numbers: =COUNT(A1:A20)
Counts the number of numeric values in A1 through A20.
2. Count Non-Empty: =COUNTA(A1:A20)
Counts all non-empty cells in A1 through A20.
3. Count If: =COUNTIF(A1:A10, ">50")
Counts cells in A1:A10 that have a value greater than 50.
Financial Formulas
1. Loan Payment: =PMT(5%/12, 60, -10000)
Calculates the monthly payment for a loan with a 5% annual interest rate over 60 months
for $10,000.
2. Future Value: =FV(5%/12, 60, -200, -5000)
Calculates the future value of an investment with monthly deposits of $200 and an initial
deposit of $5000.
Array Formulas (Excel 365/2021+)
1. Unique Values: =UNIQUE(A1:A20)
Returns a list of unique values from A1 through A20.
2. Filter Data: =FILTER(A1:A10, B1:B10>50)
Returns values in A1:A10 where corresponding values in B1:B10 are greater than 50.
Let me know if you want further explanations for any formula or examples for specific
scenarios!