Formulas

Basic Mathematical Functions

1. SUM: Adds a range of numbers.
   Formula: =SUM(A1:A10)
   Example: If A1:A10 contains {1, 2, 3}, it returns 6.

2. AVERAGE: Calculates the average of a range.
   Formula: =AVERAGE(A1:A10)
   Example: If A1:A10 contains {2, 4, 6}, it returns 4.

3. ROUND: Rounds a number to a specified number of digits.
   Formula: =ROUND(A1, 2)
   Example: If A1 = 3.14159, it returns 3.14.

4. PRODUCT: Multiplies all numbers in a range.
   Formula: =PRODUCT(A1:A3)
   Example: If A1:A3 contains {2, 3, 4}, it returns 24.

5. POWER: Returns the result of a number raised to a power.
   Formula: =POWER(2, 3)
   Example: Returns 23=82^3 = 8.

6. SQRT: Returns the square root of a number.
   Formula: =SQRT(16)
   Example: Returns 4.

7. MOD: Returns the remainder of division.
   Formula: =MOD(10, 3)
   Example: Returns 1.

8. ABS: Returns the absolute value of a number.
   Formula: =ABS(-10)
   Example: Returns 10.

Text Manipulation Functions

1. LEN: Counts the number of characters in a text string.
   Formula: =LEN("Excel")
   Example: Returns 5.

2. TRIM: Removes extra spaces from text.
   Formula: =TRIM(" Hello World ")
   Example: Returns "Hello World".

3. LEFT: Extracts characters from the left of a string.
   Formula: =LEFT(A1, 4)
   Example: If A1 = "Microsoft," it returns "Micr".

4. RIGHT: Extracts characters from the right of a string.
   Formula: =RIGHT(A1, 3)
   Example: If A1 = "Microsoft," it returns "oft".

5. MID: Extracts characters from the middle of a string.
   Formula: =MID(A1, 3, 4)
   Example: If A1 = "Microsoft," it returns "cros".

6. FIND: Finds the position of a substring (case-sensitive).
   Formula: =FIND("soft", A1)
   Example: If A1 = "Microsoft," it returns 6.

7. SUBSTITUTE: Replaces occurrences of a text with another.
   Formula: =SUBSTITUTE(A1, "old", "new")
   Example: If A1 = "old text," it returns "new text".

8. CONCATENATE (or CONCAT): Joins multiple text strings.
   Formula: =CONCAT(A1, " ", B1)
   Example: If A1 = "Hello" and B1 = "World," it returns "Hello World".

9. PROPER: Capitalizes the first letter of each word.
   Formula: =PROPER("hello world")
   Example: Returns "Hello World".

10. UPPER: Converts text to uppercase.
    Formula: =UPPER("excel")
    Example: Returns "EXCEL".

11. LOWER: Converts text to lowercase.
    Formula: =LOWER("EXCEL")
    Example: Returns "excel".

12. REPT: Repeats a text string a specified number of times.
    Formula: =REPT("A", 5)
    Example: Returns "AAAAA".

13. TEXTJOIN: Joins text from a range with a delimiter.
    Formula: =TEXTJOIN(", ", TRUE, A1:A5)
    Example: Returns "A, B, C, D, E".

Logical Functions

1. IF: Returns different values based on a condition.
   Formula: =IF(A1>10, "Yes", "No")
   Example: If A1 = 15, it returns "Yes".

2. IFERROR: Returns a custom value if a formula results in an error.
   Formula: =IFERROR(A1/B1, "Error")
   Example: If B1 = 0, it returns "Error".

3. ISNUMBER: Checks if a value is numeric.
   Formula: =ISNUMBER(A1)
   Example: If A1 = "123", it returns TRUE.

4. ISTEXT: Checks if a value is text.
   Formula: =ISTEXT(A1)
   Example: If A1 = "Excel," it returns TRUE.

Date and Time Functions

1. NOW: Returns the current date and time.
   Formula: =NOW()
   Example: Returns "01/05/2025 12:34 PM".

2. TODAY: Returns the current date.
   Formula: =TODAY()
   Example: Returns "01/05/2025".

3. DATEDIF: Calculates the difference between two dates.
   Formula: =DATEDIF(A1, B1, "Y")
   Example: If A1 = "01/01/2000" and B1 = "01/01/2025", it returns 25.

Lookup and Reference Functions

1. VLOOKUP: Searches for a value in the first column and returns a value in the same row.
   Formula: =VLOOKUP(101, A2:D10, 3, FALSE)
   Example: Returns the corresponding value from column 3.

2. HLOOKUP: Searches for a value in the first row and returns a value in the same column.
   Formula: =HLOOKUP("Score", A1:D3, 2, FALSE)
   Example: Returns the corresponding value from row 2.

3. INDEX: Returns the value of a cell in a specific row and column.
   Formula: =INDEX(A1:C10, 3, 2)
   Example: Returns the value in the 3rd row and 2nd column.

4. MATCH: Returns the relative position of a value in a range.
   Formula: =MATCH(50, A1:A10, 0)
   Example: If 50 is the 3rd item, it returns 3.

5. CHOOSE: Returns a value from a list based on a given position.
   Formula: =CHOOSE(2, "Red", "Green", "Blue")
   Example: Returns "Green".

6. OFFSET: Returns the value of a cell offset by rows and columns.
   Formula: =OFFSET(A1, 1, 1)
   Example: Returns the value 1 row down and 1 column to the right.

7. INDIRECT: Returns the value of a cell specified by a text reference.
   Formula: =INDIRECT("A1")
   Example: If A1 = 10, it returns 10.