How to make excel formulas

You can use Ctrl+Enter instead of Enter to do this, as follows:

1. Select the rows (or columns) to enter the formula, how many rows (or columns) need to be entered, that is, how many are selected;

2. Enter the formula, make sure that the correct formula is entered in the first cell on the selected row (or column);

3. Press the Ctrl+Enter key to confirm (press and hold the Ctrl key while pressing the Enter key).

Note: When you press Ctrl+Enter at the same time, the same content will be entered in all selected cells (when entering a formula, the position of the relative referenced cells changes with the position of the cell itself).

Function formulas and how to use them.

1. Number processing 1, the formula for taking the absolute value function: =abs (number) 2, the formula for the integer function: =int (number) 3, the formula for the comma base rounding function:

round(number, number of decimal places) 2. Judgment formula 1, if the calculated result value is wrong, then it will be displayed as emptyFormula: =iferror(number number,) Description: If the result of the calculation is wrong, it will be displayed as empty, otherwise it will be displayed normally.

2. The formula for multi-condition judgment and return value of if statement: if(and(cell (logical operator) value, specified cell = return value 1), return value 2,) Description: When all conditions are established at the same time, the crack base is used with and, and the or function is used for any one of them is established.

3. Commonly used statistical formula 1, the same content formula for statistics in two **: b2=countif (data source: location, specified, target location) Description:

If the return value is greater than 0, it means that the limb trillion exists in another table, and 0 does not exist. If, the formula used in this example is: b2=countif(sheet15!).

a:a,a2)2. The total data formula of statistical calendar rent is not repeated: C2=SumProduct(1 COU

Commonly used functions are sum, left, text, vlookup, if, etc., and these functions can be used to form the basic formulas of Tannian as follows:1Use the sum function to write the summing formula:

Usage = sum(sum region), e.g. sum(a1:b2,d1:e2), is to find a1:

B2 and D1:E2 are the sum of all values in the two regions; 2.Use the left function to write a formula that takes a string of a specified length from the left:

Usage=left(string or cell, length), such as left(a1,1), is to take a bit of text from the left side of a1, for example, "abc" in a1, then the result of this formula is a3Use the text function to write a formula to change the text formatting: usage=text(string or cell, text format), e.g. text(a1,"000"), that is, the content in a1 becomes the format "000", if it is 1 in a1, it becomes 0014

Use vlookup to write the formula to find and return the specified value: usage=vlookup(find value, find region, return the number of sail infiltration in the search area of the trembling car core, match the square ruler macro formula), such as vlookup(a1

The use of the formula commonly used in excel is like a bird blocking the dust: 1. Number processing 1, take the absolute value = abs (number) 2, round = int (number) 3, round = round (number, number of decimal places) 2, judge the formula 1, display the error value generated by the formula as an empty formula: c2 = iferror(a2 b2,""Description:

If it is an error value, it will be displayed as empty, otherwise the loss date will be displayed normally. 2. The formula for the return value of if multi-condition balance width judgment is: c2=if(and(a2<500,b2="Not expired"), supplements","Description:

If both conditions are true, use and, and if either is true, use the or function. Three Zen, statistical formula 1, statistical two ** repeated content formula: b2=countif(sheet15!

a:a,a2) note: If the return value is greater than 0, it exists in another table, and 0 does not exist.

2. The formula for counting the total number of non-repeating people: C2=SumProduct(1 CountIF(A2:A8,A2:

a8)) Instructions: Use countif

Fill in the box "=a1+b1" and you will get the result... This is the simplest application, and it is also the most commonly used and effective method, and there is no need to master the rest, unless you are demanding and determined to become a master...

Click to insert a function, and a dialog box will pop up, select the function in the dialog box, and it will tell you what the function is used for, how to use it, and the usage of each parameter.

Find the unit price corresponding to d4 (product name) in the average price of the product.

The average price is a defined name, and you can find its reference position by pressing Ctrl+F3.

In general, this is a query and calculation formula, which is divided into 2 steps: query the average price of goods corresponding to d4 (the data in the last column of the notebook in the first table); Multiply the query result of by the sales volume of E4 to get the result of F4. The specific value f4 = x 200 is displayed

vlookup(d4,average price of goods, 2,0) means that in the definition of "average price", the data of the second column in the name of the first column corresponding to d4 is queried. If d4 is changed to keyboard, then f4 = x 200

Hope it helps.