Basic common calculation functions to know in Excel

A. THE USE OR USE

1. Left function

- Syntax: LEFT (text, number of characters to get) .

- Meaning: Returns the number of characters to start from the first character on the left.

- Example: LEFT ("TipsMake.vn", 8) = "thuthuat".

2. VLOOKUP function

- Syntax: VLOOKUP (lookup value, lookup table, return value column, lookup type) .

- For example:

Basic common calculation functions to know in Excel Picture 1

Detector value: bar code column.

Table: TRA TABLE 1.

Return value: column name of company.

Detection type: Leave the value = 0 (accurate detection).

3. LEN function

- Syntax: LEN (text) .

- Meaning: Returns the number of characters in a string.

- Example: LEN ("TipsMake.vn") = 18.

4. MID function

- Syntax: MID (character string, starting position, number of characters to be taken) .

- Meaning: Returns the substring of a character in a given string starting at a certain position.

- Example: MID ("TipsMake.vn", 9, 9) = "phanmem.vn".

5. RIGHT function

- Syntax: RIGHT (text, number of characters to get) .

- Meaning: Returns the number of characters to start from the first character on the right.

- Example: RIGHT ("TipsMake.vn", 10) = "phanmem.vn".

6. The VALUE function

- Syntax: VALUE (text) .

- Meaning: Bringing string values ​​as numbers into mathematical values.

- Example: Value ("0025") = 25.

7. HLOOKUP function

- Syntax: HLOOKUP (lookup value, lookup table, return value row, lookup type) .

B. GENERAL EXAMPLE

The following data sheet is available:

Basic common calculation functions to know in Excel Picture 2

1. Find the names of the items based on TABLE 1

- Use the Vlookup and Left functions to get the name of the item corresponding to the item code in TABLE 1 .

Enter the formula: Vlookup (Left (B3, 1), $ A $ 13: $ B $ 15, 2, 0) .

Basic common calculation functions to know in Excel Picture 3

2. Find the manufacturer based on the last 2 characters of the product's code

If the last 2 characters are "TN" -> "Domestic". If other characters are found in TABLE TRA 2 .

Enter the following statement: IF (RIGHT (B3, 2) = "TN", "Domestic", HLOOKUP (RIGHT (B3, 2), $ F $ 12: $ J $ 13, 2, 0)) .

Basic common calculation functions to know in Excel Picture 4

3. Calculate the unit price based on the first letter of the code and TABLE 1

Basic common calculation functions to know in Excel Picture 5

Results after calculation:

Basic common calculation functions to know in Excel Picture 6

Good luck!