.png)
1. Basic Arithmetic Formulas:
Addition: =A1 + B1
Subtraction: =A1 - B1
Multiplication: =A1 * B1
Division: =A1 / B1
2. Summing Values:
Sum of a range: =SUM(A1:A10)
Sum with criteria (using SUMIF): =SUMIF(A1:A10, ">50")
3. Average:
Calculate the average of a range: =AVERAGE(A1:A10)
4. Counting:
Count numbers in a range: =COUNT(A1:A10)
Count with criteria (using COUNTIF): =COUNTIF(A1:A10, ">50")
5. Text Manipulation:
Concatenation: =A1 & " " & B1
Extracting text (using LEFT, RIGHT, MID):
=LEFT(A1, 5) (Returns the leftmost 5 characters)
=RIGHT(A1, 5) (Returns the rightmost 5 characters)
=MID(A1, 3, 5) (Returns 5 characters starting from the 3rd character
6. Logical Functions:
IF function: =IF(A1 > 10, "Yes", "No")
AND function: =AND(A1 > 10, B1 < 5)
OR function: =OR(A1 > 10, B1 < 5)
7. Date and Time:
Current date: =TODAY()
Current time: =NOW()
Date calculations (e.g., adding days): =A1 + 7
8. Lookup and Reference:
VLOOKUP: =VLOOKUP(A1, B1:D10, 3, FALSE)
HLOOKUP: =HLOOKUP(A1, B1:D10, 3, FALSE)
INDEX and MATCH (for more advanced lookups): =INDEX(B1:D10, MATCH(A1, B1:B10, 0), 3)
9. Financial Formulas:
Calculate the monthly payment for a loan: =PMT(0.05/12, 5*12, 10000)
10. Statistical Functions:
Calculate the standard deviation of a range: =STDEV(A1:A10)
Calculate the median of a range: =MEDIAN(A1:A10)
11. Array Formulas:
Perform calculations on arrays of data using functions like SUMPRODUCT, TRANSPOSE, etc.
12. Conditional Formatting:
Not a formula, but it's a feature that allows you to format cells based on specified conditions. For example, highlight cells with values greater than 50.
13. Named Ranges:
You can name a range of cells and use that name in formulas for better readability and maintainability.
.png)
