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!

4 ★ | 1 Vote

May be interested

  • How to write leading zeros in ExcelPhoto of How to write leading zeros in Excel
    in excel, it is not possible to write zeros before a sequence of numbers. but in practice with some work, writing a zero at the beginning of the sequence is required. the following article will guide how to write leading zeros.
  • How to make PowerPoint - Instructions for making reports and presentations using PowerPointPhoto of How to make PowerPoint - Instructions for making reports and presentations using PowerPoint
    powerpoint is an application that helps you create presentations, reports, presentations ... this article will guide step by step making powerpoint for new students.
  • Insert music into PowerPointPhoto of Insert music into PowerPoint
    instructions for inserting music into powerpoint. to make your powerpoint presentation more vivid and suitable for the content and purpose of the presentation, you can insert music into powerpoint.
  • How to create tables, draw tables in WordPhoto of How to create tables, draw tables in Word
    working with word, there will be times when you have to use tables to store data. microsoft word gives you many ways to create tables, draw tables as well as provide you with a lot of tools to help you have a beautiful and easy-to-see data table.
  • Instructions to create a Template for PowerPointPhoto of Instructions to create a Template for PowerPoint
    powerpoint has many templates (templates) available that you can use to create presentation files. but if you want to create your own template to use, it is not difficult.
  • The best PowerPoint templates 2016Photo of The best PowerPoint templates 2016
    below is a summary of the best powerpoint templates 2016, you can download the powerpoint template file at the end of the article.