Here are 30 useful Excel formulas along with a brief explanation of what each one does:
1. SUM(range)
- Formula:
`=SUM(A1:A10)`
- Example: Adds up
the numbers in cells A1 through A10.
2. AVERAGE(range)
- Formula:
`=AVERAGE(A1:A10)`
- Example:
Calculates the average of the numbers in cells A1 through A10.
3. COUNT(range)
- Formula:
`=COUNT(A1:A10)`
- Example: Counts
the number of cells that contain numbers in cells A1 through A10.
4. COUNTA(range)
- Formula:
`=COUNTA(A1:A10)`
- Example: Counts
the number of non-empty cells in cells A1 through A10.
5. MAX(range)
- Formula:
`=MAX(A1:A10)`
- Example: Finds
the maximum value in cells A1 through A10.
6. MIN(range)
- Formula:
`=MIN(A1:A10)`
- Example: Finds
the minimum value in cells A1 through A10.
7. IF(condition, value_if_true, value_if_false)
- Formula:
`=IF(A1>10, "Yes", "No")`
- Example: Returns
"Yes" if the value in A1 is greater than 10, otherwise returns
"No".
8. SUMIF(range, criteria, [sum_range])
- Formula:
`=SUMIF(A1:A10, ">10", B1:B10)`
- Example: Sums the
values in B1:B10 where the corresponding values in A1:A10 are greater than 10.
9. AVERAGEIF(range, criteria, [average_range])
- Formula:
`=AVERAGEIF(A1:A10, ">10", B1:B10)`
- Example: Averages
the values in B1:B10 where the corresponding values in A1:A10 are greater than
10.
10. COUNTIF(range, criteria)
- Formula:
`=COUNTIF(A1:A10, ">10")`
- Example: Counts
the number of cells in A1:A10 that are greater than 10.
11. VLOOKUP(lookup_value, table_array, col_index_num,
[range_lookup])
- Formula:
`=VLOOKUP("John", A1:C10, 2, FALSE)`
- Example: Looks
for "John" in the first column of A1:C10 and returns the value in the
second column of the found row.
12. HLOOKUP(lookup_value, table_array, row_index_num,
[range_lookup])
- Formula:
`=HLOOKUP("Q1", A1:E4, 3, FALSE)`
- Example: Looks
for "Q1" in the first row of A1:E4 and returns the value in the third
row of the found column.
13. MATCH(lookup_value, lookup_array, [match_type])
- Formula:
`=MATCH(25, A1:A10, 0)`
- Example: Finds
the position of 25 in the range A1:A10 with an exact match.
14. INDEX(array, row_num, [column_num])
- Formula:
`=INDEX(A1:C10, 2, 3)`
- Example: Returns
the value in the second row and third column of the range A1:C10.
15. CONCATENATE(text1, [text2], ...)
- Formula:
`=CONCATENATE(A1, " ", B1)`
- Example:
Combines the values of A1 and B1 with a space in between.
16. LEN(text)
- Formula:
`=LEN(A1)`
- Example: Returns
the length of the text in cell A1.
17. TRIM(text)
- Formula:
`=TRIM(A1)`
- Example: Removes
extra spaces from the text in cell A1.
18. LEFT(text, [num_chars])
- Formula:
`=LEFT(A1, 3)`
- Example: Returns
the first three characters of the text in cell A1.
19. RIGHT(text, [num_chars])
- Formula:
`=RIGHT(A1, 3)`
- Example: Returns
the last three characters of the text in cell A1.
20. MID(text, start_num, num_chars)
- Formula:
`=MID(A1, 2, 3)`
- Example: Returns
three characters from the text in cell A1, starting at the second character.
21. UPPER(text)
- Formula:
`=UPPER(A1)`
- Example:
Converts the text in cell A1 to uppercase.
22. LOWER(text)
- Formula:
`=LOWER(A1)`
- Example:
Converts the text in cell A1 to lowercase.
23. PROPER(text)
- Formula:
`=PROPER(A1)`
- Example:
Capitalizes the first letter of each word in the text in cell A1.
24. NOW()
- Formula:
`=NOW()`
- Example: Returns
the current date and time.
25. TODAY()
- Formula:
`=TODAY()`
- Example: Returns
the current date.
26. DATEDIF(start_date, end_date, unit)
- Formula:
`=DATEDIF(A1, B1, "d")`
- Example: Returns
the number of days between the dates in cells A1 and B1.
27. YEAR(date)
- Formula:
`=YEAR(A1)`
- Example: Returns
the year of the date in cell A1.
28. MONTH(date)
- Formula:
`=MONTH(A1)`
- Example: Returns
the month of the date in cell A1.
29. DAY(date)
- Formula:
`=DAY(A1)`
- Example: Returns
the day of the date in cell A1.
30. TEXT(value, format_text)
- Formula:
`=TEXT(A1, "mm/dd/yyyy")`
- Example:
Converts the value in cell A1 to text in the specified date format.
.jpg)
1 Comments
informative
ReplyDelete