functions misc

 Here are 10 Excel value functions, expanded to include examples with data and formulas:

---

1. SUM

Adds up a range of numbers.

• Formula: =SUM(A1:A10)

• Example Data: A1:A10 contains numbers from 1 to 10.

• Result: 55

---

2. AVERAGE

Calculates the average of a range of numbers.

• Formula: =AVERAGE(A1:A10)

• Example Data: A1:A10 contains numbers from 1 to 10.

• Result: 5.5

---

3. MAX

Finds the maximum value in a range.

• Formula: =MAX(A1:A10)

• Example Data: A1:A10 contains numbers from 1 to 10.

• Result: 10

---

4. MIN

Finds the minimum value in a range.

• Formula: =MIN(A1:A10)

• Example Data: A1:A10 contains numbers from 1 to 10.

• Result: 1

---

5. COUNT

Counts the number of cells in a range that contain numbers.

• Formula: =COUNT(A1:A10)

• Example Data: A1:A10 contains numbers from 1 to 10.

• Result: 10

---

6. COUNTA

Counts the number of non-empty cells in a range.

• Formula: =COUNTA(A1:A10)

• Example Data: A1:A10 contains numbers from 1 to 10 and one empty cell.

• Result: 9

---

7. IF

Performs a logical test and returns one value if true, another if false.

• Formula: =IF(A1>5, "Yes", "No")

• Example Data: A1 contains 7.

• Result: Yes

---

8. VLOOKUP

Searches for a value in the first column of a table and returns a value in the same row from a specified column.

• Formula: =VLOOKUP("Apple", A1:B3, 2, FALSE)

• Example Data:

  Copy

  A1: Apple  B1: 10
  A2: Banana B2: 20
  A3: Orange B3: 30

• Result: 10

---

9. HLOOKUP

Searches for a value in the first row of a table and returns a value in the same column from a specified row.

• Formula: =HLOOKUP("Banana", A1:C2, 2, FALSE)

• Example Data:

  Copy

  A1: Apple  B1: Banana  C1: Orange
  A2: 10     B2: 20      C2: 30

• Result: 20

---

10. SUMIF

Adds up values in a range that meet a specific condition.

• Formula: =SUMIF(A1:A10, ">5")

• Example Data: A1:A10 contains numbers from 1 to 10.

• Result: 40 (sum of 6, 7, 8, 9, 10)

Excel provides a variety of text functions that allow you to manipulate and analyze text data. Below are 10 expanded examples of Excel text functions, including sample data and formulas:

---

Sample Data

A	B	C	D
John Doe	john@doe.com	123-456-7890	$1,234.56
Jane Smith	jane@smith.com	987-654-3210	$5,678.90
Alice Brown	alice@brown.com	555-123-4567	$9,876.54

---

1. LEFT: Extract the first name

Extract the first word (first name) from the full name in column A.

Formula:

excel

Copy

=LEFT(A2, FIND(" ", A2) - 1)

Result:

• For John Doe, the result is John.

---

2. RIGHT: Extract the domain from an email

Extract the domain part of the email address in column B.

Formula:

excel

Copy

=RIGHT(B2, LEN(B2) - FIND("@", B2))

Result:

• For john@doe.com, the result is doe.com.

---

3. MID: Extract the middle part of a phone number

Extract the middle three digits of the phone number in column C.

Formula:

excel

Copy

=MID(C2, 5, 3)

Result:

• For 123-456-7890, the result is 456.

---

4. CONCATENATE (or CONCAT): Combine first and last name

Combine the first name and last name with a space in between.

Formula:

excel

Copy

=CONCATENATE(LEFT(A2, FIND(" ", A2) - 1), " ", RIGHT(A2, LEN(A2) - FIND(" ", A2)))

Result:

• For John Doe, the result is John Doe.

---

5. LEN: Count characters in a string

Count the number of characters in the email address in column B.

Formula:

excel

Copy

=LEN(B2)

Result:

• For john@doe.com, the result is 12.

---

6. SUBSTITUTE: Replace hyphens in a phone number

Remove hyphens from the phone number in column C.

Formula:

excel

Copy

=SUBSTITUTE(C2, "-", "")

Result:

• For 123-456-7890, the result is 1234567890.

---

7. TEXT: Format a number as currency

Format the value in column D as currency with a dollar sign.

Formula:

excel

Copy

=TEXT(D2, "$#,##0.00")

Result:

• For 1234.56, the result is $1,234.56.

---

8. TRIM: Remove extra spaces

Remove leading, trailing, and extra spaces from a string.

Formula:

excel

Copy

=TRIM("  John  Doe  ")

Result:

• For John Doe , the result is John Doe.

---

9. UPPER and LOWER: Change text case

Convert text to uppercase or lowercase.

Formula (UPPER):

excel

Copy

=UPPER(A2)

Formula (LOWER):

excel

Copy

=LOWER(A2)

Result:

• For John Doe, UPPER returns JOHN DOE, and LOWER returns john doe.

---

10. FIND and SEARCH: Locate a substring

Find the position of the "@" symbol in an email address.

Formula (FIND):

excel

Copy

=FIND("@", B2)

Formula (SEARCH):

excel

Copy

=SEARCH("@", B2)

Result:

• For john@doe.com, both return 5 (the position of "@").

---

Bonus: Combining Functions

Extract the last name from the full name in column A.

Formula:

excel

Copy

=RIGHT(A2, LEN(A2) - FIND(" ", A2))

Result:

• For John Doe, the result is Doe.

---

These examples demonstrate how to use Excel's text functions to manipulate and analyze text data effectively. You can combine these functions to create more complex formulas tailored to your specific needs.

---

These functions are fundamental in Excel and can be combined with other formulas for more advanced calculations.