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:
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:
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) .
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)) .
3. Calculate the unit price based on the first letter of the code and TABLE 1
Results after calculation:
Good luck!
You should read it
- MS Excel 2007 - Lesson 6: Calculation in Excel
- Calculation of percentages in Excel
- How to add computers to Excel
- Common math functions in Excel
- Complete financial functions in Excel you should know
- How to use subtraction in Excel
- Common Excel functions you need to know about accounting
- Instructions on how to create diagonal lines in Excel box
May be interested
- How to write leading zeros in Excelin 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 PowerPointpowerpoint 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 PowerPointinstructions 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 Wordworking 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 PowerPointpowerpoint 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 2016below is a summary of the best powerpoint templates 2016, you can download the powerpoint template file at the end of the article.