Excel Formulas and Examples
1. Basic Mathematical Formulas
1. Addition:
= A1 + B1
Adds values in cells A1 and B1.
2. Subtraction:
= A1 - B1
Subtracts B1 from A1.
3. Multiplication:
= A1 * B1
Multiplies values in A1 and B1.
4. Division:
= A1 / B1
Divides A1 by B1.
5. Sum:
= SUM(A1:A10)
Adds all values from A1 to A10.
6. Average:
= AVERAGE(A1:A10)
Finds the average of values in A1:A10.
2. Logical Formulas
1. IF:
= IF(A1 > 10, "Greater", "Smaller")
Checks if A1 is greater than 10 and returns "Greater" if true, otherwise "Smaller".
2. AND:
= AND(A1 > 10, B1 < 5)
Returns TRUE if both conditions are met (A1 > 10 and B1 < 5), otherwise FALSE.
3. OR:
= OR(A1 > 10, B1 < 5)
Returns TRUE if either condition is met, otherwise FALSE.
4. NOT:
= NOT(A1 > 10)
Reverses the logical value of the condition (returns TRUE if A1 is NOT greater than 10).
3. Lookup & Reference Formulas
1. VLOOKUP:
= VLOOKUP(A1, B1:C10, 2, FALSE)
Looks for the value in A1 in the first column of the range B1:C10 and returns the value in
the second column.
2. HLOOKUP:
= HLOOKUP(A1, B1:F5, 2, FALSE)
Similar to VLOOKUP but works horizontally.
3. INDEX:
= INDEX(A1:C10, 2, 3)
Returns the value from the second row and third column in the range A1:C10.
4. MATCH:
= MATCH(A1, B1:B10, 0)
Returns the relative position of the value in A1 within the range B1:B10.
5. INDEX + MATCH:
= INDEX(B1:B10, MATCH(A1, A1:A10, 0))
Combination of INDEX and MATCH to perform lookups more flexibly.
4. Text Functions
1. CONCATENATE:
= CONCATENATE(A1, " ", B1)
Combines the values in A1 and B1 with a space between them.
2. LEFT:
= LEFT(A1, 3)
Returns the first 3 characters of the string in A1.
3. RIGHT:
= RIGHT(A1, 4)
Returns the last 4 characters of the string in A1.
4. MID:
= MID(A1, 2, 3)
Extracts 3 characters from the string in A1, starting from the second character.
5. LEN:
= LEN(A1)
Returns the length of the string in A1.
6. TRIM:
= TRIM(A1)
Removes extra spaces from the string in A1, leaving only single spaces between words.
7. UPPER:
= UPPER(A1)
Converts the text in A1 to uppercase.
8. LOWER:
= LOWER(A1)
Converts the text in A1 to lowercase.
9. PROPER:
= PROPER(A1)
Capitalizes the first letter of each word in the string in A1.
5. Date and Time Functions
1. TODAY:
= TODAY()
Returns the current date.
2. NOW:
= NOW()
Returns the current date and time.
3. DATE:
= DATE(2025, 1, 15)
Returns the date for January 15, 2025.
4. DAY:
= DAY(A1)
Extracts the day of the month from a date in A1.
5. MONTH:
= MONTH(A1)
Extracts the month from a date in A1.
6. YEAR:
= YEAR(A1)
Extracts the year from a date in A1.
7. DAYS:
= DAYS(A1, B1)
Calculates the number of days between two dates.
8. NETWORKDAYS:
= NETWORKDAYS(A1, B1)
Returns the number of working days between two dates.
6. Statistical Formulas
1. COUNT:
= COUNT(A1:A10)
Counts the number of numerical values in the range A1:A10.
2. COUNTA:
= COUNTA(A1:A10)
Counts the number of non-empty cells in the range A1:A10.
3. COUNTIF:
= COUNTIF(A1:A10, ">5")
Counts the number of cells greater than 5 in the range A1:A10.
4. SUMIF:
= SUMIF(A1:A10, ">5")
Sums the values in A1:A10 that are greater than 5.
5. AVERAGEIF:
= AVERAGEIF(A1:A10, ">5")
Averages the values in A1:A10 that are greater than 5.
6. MAX:
= MAX(A1:A10)
Returns the highest value in A1:A10.
7. MIN:
= MIN(A1:A10)
Returns the lowest value in A1:A10.
8. MEDIAN:
= MEDIAN(A1:A10)
Returns the median value in A1:A10.
7. Financial Formulas
1. PMT:
= PMT(interest_rate, number_of_periods, loan_amount)
Calculates the periodic payment for a loan.
2. FV:
= FV(interest_rate, number_of_periods, payment, [present_value], [type])
Calculates the future value of an investment or loan.
8. Array Formulas
1. SUMPRODUCT:
= SUMPRODUCT(A1:A10, B1:B10)
Multiplies corresponding values in A1:A10 and B1:B10, then sums the results.
