office - - brighter - NIRVANA

A ,10-20 two-digit multiplication and the power-speed calculation (not including the 10 and 20). .


Methods: the mantissa is multiplied, multipliers and multiplier of the mantissa (decimal).


【Example 1】 12. .


　 X 1 3 。.


----。 .


1-5-6.


(1) mantissa multiply 2X3 = 6. .


(2) is the mantissa of the multipliers and multiplier 12+3=15..


(3) connected the two calculations is the result of the request. .


(In the multiplication calculation, is multiplied by the number of before called multiplicand, after x number of multiplier).


【Example 2】 1 5. .


X 1 4。.


----。 .


2 1 0.


(1) mantissa multiply 5X4 = 20 (full decimal). .


(2) is the mantissa of the multipliers and multiplier 15+4=19, plus a bit into the last 2 19+2=21 ...


(3) connected the two calculations is the result of the request. .


Second, any two-digit multiplication ...


If any two-digit multiplication, then the product of a bit is the product of two numbers a bit. Ten is the cross product of two-digit and different bit, high scores of the product of the two, and follow a carry forward the full ten. If a formula should be written. .


Like this: ...


Product = (product of ten) (multiplicand ten digit + multiplicand × multiplier × by dozens of bits) (bits product) | | |. .


High ten (decimal) single digits (decimal).


Formulas: the product of different spaces and residents cross the middle of the product of stations on both sides of the same place. .


【例】 3 7。.


X 6 2. .


———。.


2294. .


(1), the mantissa is multiplied 7X2=14 (decimal) into 1.


(2) of the angle multiplied by 3X2 = 6; 7X6 = 42, sum of the two plot 6 +42 = 48 (full decimal) into the 4. .


(3) multiplied by the number 4 on the 3X6=18 plus ten to 18+4=22 ...


(4) the result is that all the requirements linked to the results (18 +4) + (8 +1) +4 = 2294. .


Third, any two-digit square operations..


Methods: The ending of the square, the first expansion of the number of mantissa by 2 times, the first square of the number. .


[]2 3.


X 2 3. .


——— 。.


529. .


(1) square 3X3=9 of the mantissa (decimal).


(2) The sum of multiplying the number of 2X3 = 6 to triple to 12 written on the ten (full decimal). .


(3) the first square of the number of 2X2=4 plus ten on a 1 to 5..


(4) connected to the calculation is the result of the request. .


Three-digit squares and squares of double-digit rapid calculation method for the same ? note: three-digit number of the first number refers to the first two digits! 〗..


Fourth, large numbers (nearly 100 the number of) the square of the speed of calculation. .


Methods: 100 difference in large numbers, a difference of several known as difference;..


The square of the first difference operator written in a bit and ten on the (absence of zeros). .


Reuse topic minus the difference a good result; the last two results shall be attached to the results you seek ...


【Example】. .


9-4.


X 9 4. .


———– 。.


8836. .


(1) the difference between 94 and 100 for 6.


(2) The difference between the square of the number 36 written in the 6-digit and ten spaces. .


(3) 94 minus the margin on write 6 per cent in the hundreds and thousands ...


(4) connected to the calculation is the result of the request. .


Same as the number five, the first, plus the mantissa and the ten multiplication of two-or three-digit number ...


Methods: mantissa multiplication, the first increase the number of repeated multiplication. .


【例1】 5 4 。.


X 5 6. .


———。.


3024. .


(1) the mantissa is multiplied 4X6=24 write directly on the ten-digit and bit ...


(2) the first few 5 plus 1 to 6, two multiplying the number of 6X5 = 30. .


(3) results shall be attached to the two find results ...


【Example 2】 1 2 5. .


X 1 2 5。.


----。 .


1 5 6 2 5.


(1) 5X5 = 25 multiplication mantissa written directly in ten and a bit on. .


(2), number 12, plus 1 per cent, and then multiplies two numerical 13X12=156..


(3) connected the two results. .


D have a B2302-20051020-EO-05-A310-34-022R0 folder, which has an ABCD ... PDF files, and now want to put this file to "B2302-20051020-EO-05-A310-34-022R0" naming, folder, there are hundreds of asked members how to batch convert?..


I generally talk about it, if you do not have it Pity the TC. .


Assuming that all of your directory on drive d,..


1. . In the D * search the root directory. . Pdf, the result "output to the list." .


2. Select list all the files, Ctrl+M batch rename ...


3. . With [= tc. . Path] instead of [N] (provided that you plug in, I think there should be it ... ...), and search and replace instead of using = number can not appear in the file name in the \ sign (backslash). .


4.. Be shaped after a rename "d ... =B2302-20051020-EO-05-A310-34-022R0 ... PDF" file..


5. . Repeat 1 and 2. .


6. When the batch rename [n] instead of [=TC.. path] to find "d ... =", to be replaced by "clear.".


Get. .


Lots of friends old and QQ can not open, summed up the following workaround:..


1, first install Macromedia Flash Player 9. .


Download: http://www ... greendown ... CN/soft/675 ... html..


2, then open IE-> Tools menu -> INTERNET Options -> Security -> Custom Level -> ActiveX column were selected. .


Disable – enable – disable-enable-disable-enable – enable ...


3, check whether your IE 6. .0, I found that IE7 is not supported by QQ space. .


4, if the above has been done, or is not open, and also hint that your browser does not support QQ space features, follow the steps below to do: it's probably a Microsoft XML problem ...


Download Microsoft XML Parser (MSXML) 3. .0 Service Pack 4 (SP4). .


Download: ...


http://www. . Microsoft. .com / downloads / details. . Aspx? Familyid = C0F86022-2D4C-4162-8FB8-66BFC12F32B0 & displaylang = zh-cn. .


If the error still occurs, try the following methods:..


Clear web browser cache, the browser menu select Tools -> Interner Options -> Delete Cookies and Delete Files and then OK;. . Please click on your IE browser "Tools", select "internet options" and enter "security" page, click the "Custom Level", set your security settings set to "low." .


Print a calendar ...


Applies to: Microsoft Office Outlook 2003. .


Show all ...


All hidden. .


Print a calendar with all appointment information ...


In the "File" menu, click "Print", then "Print Style" box, click the desired print style (print style: a combination of paper and page settings, the way the decision to print the project. Outlook provides built-in print style, you can create your own printing style..) .


To print the details of appointments and meetings, in the "print styles" box, click the "calendar details style.".


In the "From" and "to" list, enter the first and last days to print. .


To set other print options (such as paper orientation or font), click "page setup", then select the options you want ...


Comments. .


To print the details of private appointments, clear the "hide details of private appointments" check box.


To print non-adjacent dates, please switch to the "Week" or "Month" view (view: Through the arrangement of information and formats in different place, view a variety of ways for you to view the information in the same folder. Each have a standard folder view. You can also create custom views.), select a date to be printed, and then in the "File" menu, click "Print." .


Some parameters in the function as TRUE and FALSE, such as parameter range_lookup HLOOKUP and VLOOKUP functions. When you want to specify as FALSE when 0lai can be used instead, or don't even write 0 and only occupy the location parameter with commas. 3 formulas are equivalent to the following: ...


= VLOOKUP (A1, B1: C10, 2, FALSE). .


=VLOOKUP(A1,B1:C10,2,0)。.


= VLOOKUP (A1, B1: C10, 2,). .


In addition, there are a lot of you want to specify a parameter to 0 can also be a shorthand for value of the position of the parameter a comma, for example:..


= MAX (A1, 0) can be written: = MAX (A1,). .


=IF (B2=A2,1,0) can be written as: =IF (B2=A2,1,).


= OFFSET (A1, 0,0,10) can be written as: = OFFSET (A1,,, 10). .


Not all this abbreviation indicates that the parameter is 0. For example, in the SUBSTITUTE function to REPLACE text function function, new_text is a new character that you want to replace, if only to keep it in front of a comma, it means "" empty text rather than 0.


Attention! . .


Omitted argument is based on the default rule for the function through parameters (including the comma it need to interval) remove the formulas from the function, so only in accordance with the order from the right side of the bracket to the left of the function is omitted. Parameter is shorthand for the corresponding location reservation parameters by commas, constant that represents the parameter, such as 0,FALSE or an empty text ...


To retrieve information about a named range, you can use the Name property of each class. The following list describes some of the most common members:. .


Name returns the name specified as a named range ...


RefersTo standard format ("= SheetName! $ B $ 25") returns a string containing the actual destination address. .


RefersToR1C1 "R1C1" format ("=SheetName! R25C2 ") returns the destination address ...


Value returns a range of content analysis as a reference to a named range. .


ActiveWorkbook。. Names。. Add Name:="myName", RefersToR1C1:="=Sheet1! R3C4 "' define names for the cells ...


cells (1,2) = [myName] 'assignment to another cell. .


Word: magical function "replace empty rows.".


In their daily work, we often download some written material from the Internet, often more so because of the number of pages blank lines remain high. On the "blank lines replace" articles also read some, this approach generally is: In the "Edit" menu, open the "Find and Replace" dialog box, in the "Find what" type "^ p ^ p" " Replace "enter" ^ p ", then" Replace All. " .


　　And now a second-level c-paper, try using the above method is the award-winning the award-winning defeat, every Tip: "Word has finished searching the document and has completed 0 replacements." Why blank lines are here, Word and they knew it? ...


After a careful search, find ways to remove the air by replacing line should pay attention to two questions:. .


　　1. distinguish between files using a manual line break ("shift + return"), is also a paragraph mark. "^P^p" replaces the "^ p" does not replace all blank lines. If the file is used for manual line breaks, you come to "^l^l" replace into "^l" (there is not 1, but l-letter lowercase, and can also be used directly in the Replace dialog box "special character", "manual line breaks"), blank lines can be removed ...


I use the paper it is with a manual line break for the line. But I also used the "^ l ^ l" replaced with "^ l" means, with the "Replace All" when you are still prompted to "Word has finished searching the document and has completed 0 replace" Why is this it? I find myself becoming confused, in order to figure out what went wrong in the end, I was in the Word document were line breaks and carriage returns manually create some blank lines, use "^ p ^ p" Replace "^ p" and "^ l ^ l" replaced with "^ l" to verify the results of the successful completion of blank lines removed. Why do I input can be replaced, but not downloaded it? Is there is also "authority"? .


　　2. Note spaces exist. Desperation, I had to manually delete. In the process of removing, I discovered the secret of it: every blank line there is a space before the newline. Spaces to toss my "culprit!" and "^l^l" (note that there is a space between two wraps), replace "^l", replace has finally got the results. Click Replace all, and changed several times, "find what", such as "^p^l" replace into "^l". No blank lines in my page ...


It seems with the replacement method to remove the empty line, to be flexible towards looking for, otherwise, Word can not understand your heart. Computer is intelligent and the most "stubborn" and. .


Excel functions tutorial ...


[Date :2005-07-02]. .


From: author: Lu Yuanjie..


[Font: medium and small]. .


[Dvnews_page=Excel functions introduction the functions of data processing functions of]Excel in the existing word processing software can be said to be in top, there is little software is able to match with it.. .


Please follow my journey to start Excel functions. Here, the author assumes that you have for understanding of the basics of Excel have a certain amount. First of all we have to learn about some of the functions related to knowledge ...


First, what is the function. .


Excel's functions are predefined formulas that use some specific values called arguments are evaluated in a specific order or structure. Users can use them directly to a series of operations within a range of values, such as analysis and handling of date and time values, determine the loan payments, determine the type of data in a cell, calculate the average, sort text display and operation data, and so on. For example, the SUM function to a cell or cell range addition ...


Whether the function can be multiple it? That is whether a function is another function of the parameters? Certainly, this is the meaning of nested functions. The so-called nesting function, means that in some cases, you may need to function as another function of a parameter. For example, the formula shown in Figure 1 using nested AVERAGE function and the results compared with the 50. This formula means: if the cell F2 to F5 on average greater than 50, then find and F2 to F5, or display the value 0. .


　。.


Figure 1 nested functions. .


Before you learn Excel function, we need to do to the necessary knowledge for the structure of a function. As shown in Figure 2, the structure of a function begins with the function name, followed by a left parenthesis, comma-separated list of parameter and the right parenthesis. If the function appears as a formula, in front of the function name, type an equal sign (=). When you create a formula that contains a function, the formula palette will be provided to help ...


. .


Figure 2 structure of a function ...


Formula Palette - helping to create or edit a formula for the tool, but also provide the function and its parameters. Click the Edit bar "Edit Formula" button, or click the "common" toolbar "Paste Function" button, it will appear in the formula bar the following option boards. The whole process shown in Figure 3. .


　。.


Figure 3 Formula Palette. .


Second, use the function procedure ...


In excel how to use the function? . .


1. click the cell function is required, as shown in Figure 4, click cell C1, the editing bar appears ...


. .


Figure 4 editing a cell ...


2. . Click the bar "Edit Formula" button will appear in the edit field below a "formula for option board", then "Name" box will become a "function" button, shown in Figure 3. .


3. click on "function" button the right side of the arrow, open the function list box, select the desired function; ...


. .


Figure 5 function list box.


4. . Elected after the desired function, excel 2000 to open the "Formula Palette." Users can enter this option boards function parameters, when finished entering parameters in the "Formula Palette" in the function of the calculated results will be displayed;. .


5. click "OK" button to complete the function of the input;..


6. . If you do not have the function of the list, you can click "other function" option, open the "Paste Function" dialog box, users can select the desired function, and then click "OK" button to return to the "formula options Board "dialog box. .


Understand the basics of functions and methods of use, please follow the author looking for Excel provides a variety of functions. You can use the Insert bar, click in the "function" to see all the functions ...


. .


Figure 6 paste function list ...


Third, the function of the species. .


Excel functions in all 11 classes, namely the database function, date and time functions, engineering, finance, information functions, logic function, lookup and reference functions, mathematical and trigonometric functions, statistical functions, text, and user-defined functions ...


1. . Database functions - need to analyze data when the value in the list meets certain conditions, you can use a database worksheet function. For example, sales information contained in a data list, you can calculate all the sales value is greater than 1,000 and less than 2,500 the total number of rows or records.。 This parameter points to a worksheet range used by the database functions. Where the parameter database is on a worksheet range containing the list. Parameters field to require a summary column flags. Parameters on the criteria for the worksheet that contains the specified conditions of regional ...


2. . Date and time functions - by date and time functions, can be analyzed and processed in the formula date value and time value. .


3. engineering functions – engineering worksheet functions for engineering analysis. Most of these functions can be divided into three types: complex processing functions, a variety of digital systems (system such as decimal, hexadecimal, octal and binary systems) between numeric conversion functions, numeric conversion functions in different systems of measurement ...


4. . Financial functions - financial functions can be general financial terms, such as determining loan payments, future value or net present value investments, and the value of bonds or coupons. Common financial parameters of the function:. .


Future value (fv) – all payments after the value of the investments or loans..


During the few (nper) - The total investment in the number of pay periods. .


The payment (PMT) – an investment or loan payment amounts on a regular basis ...


Present value (pv) - beginning in the investment value of the investment or loan. For example, the present value of the loan principal amount borrowed. .


Interest rate (rate) – an investment or loan interest rate or discount rates ...


Type (type) - Payment to make payments during the interval, as in the beginning or end of the month. .


5. information functions – you can use the information function determines the type of data that is stored in the cell. Information worksheet function contains a set called IS a function, returns TRUE when the cells that satisfy the condition. For example, if a cell contains an even number, ISEVEN worksheet function returns TRUE. If you need to determine if there are blank cells in a range of cells, you can use the COUNTBLANK worksheet function to count the blank cells in a range of cells, or you can use ISBLANK worksheet function determines whether a cell is empty in the region ...


6. . Logic function - using the logic functions can be judged true or false value, or the composite test. For example, you can use the IF function to determine the condition is true or false, and thus return different values. .


7. lookup and reference functions – when you need to find a specific value in a list or table, or when you need to find a cell reference, you can use the query and the referenced worksheet function. For example, if you want to find in the table with values that match the values in the first column, you can use VLOOKUP worksheet function. If you need to determine where the data values in the list, you can use the MATCH worksheet function ...


8. . Math and trigonometric functions - through math and trigonometric functions, can handle a simple calculation, for example, rounding of numbers, calculate the sum of the values in the range of cells or complex calculations. .


9. the. statistic functions – statistical worksheet function is used for statistical analysis on the data region. For example, statistical worksheet function to provide straight line drawn by a given set of related information, such as a line of the slope and intercept the y axis, or constitutes the actual value of the line ...


10. . Text functions - through a text function that can handle text strings in the formula. For example, you can change the case or to determine the length of the text string. Date can be connected into the text string or text string on. The following formula is an example to illustrate how to use the TEXT function TODAY and function to create a message, the message contains the current date and date "dd-mm-yy" in the format. .


11. user-defined function – if you want to use especially complex calculations in a formula or calculated, and the worksheet function is unable to meet your needs, you will need to create a user-defined function. These functions, called user-defined function, you can create by using Visual Basic for Applications ...


More knowledge of excel functions and a brief introduction, the author of articles in the future will be introduced one by one use of each type of function and application of skills. However, due to the function quite a lot of excel, and therefore may only introduce some commonly used functions to use, and other more functions you can excel in the online help feature for more detailed information. [Dvnews_page = excel application of mathematical and trigonometric functions] to learn excel function, we from the "math and trigonometric functions" to start.。.


First of all, let us look at what excel math and trigonometric functions provided. The author listed here in the form of a list of all excel math and trigonometric functions provided a detailed look at the form note. .


It is not difficult to find from a table, Excel provides the mathematical and trigonometric functions has been basically covers what we normally get in a variety of mathematical formulas and trigonometric functions. More uses of these functions, I do not dwell on them one by one here, the following application demonstrates the use of these functions for us in terms of ...


First, with the sum of the functions of the application. .


SUM function is the most used functions in Excel, use it to sum operations can ignore there are data cells, such as text, a space, the syntax is simple, easy to use. Believe that this is also one of the first to learn how to use Excel functions. But in fact, not only SUM a sum provided by the Excel function, also including SUBTOTAL, SUM, SUMIF, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2 function ...


Here I will be an example of a unit focused on payroll SUM (a set of parameters and calculation), SUMIF (range of cells that meet certain conditions, sum) are used. (Note: To be simple, the example calculations ignore taxes.). .


　。.


Figure 1, the function sum. .


SUM。.


1, row or column summation. .


The most common payroll (as above), for example, it is characterized by a need to sum cells in the rows or columns..


For example, find that the unit in May 2001 the total amount of wages actually paid, you can enter the formula H13:. .


=SUM(H3:H12)。.


2, the regional sum. .


Summed area is often used to seek total all data in a worksheet. At this point you can rest the pointer on the cell storage cell of the results, and then enter the formula in the Excel formula bar "=SUM ()", using the mouse to click in the middle bracket, and finally drag the required sum of all cells. If these cells are not contiguous, you can hold down the Ctrl key drag through them separately. For cells that require less, you can hold down the Ctrl key to select them one by one, and then manually in a formula references a cell with the minus sign. Of course you can also use the formula palette do these things, but manually for the SUM function are faster.. .


=SUM(D3:D12,F3:F12)-SUM(G3:G12)。.


3, note. .


SUM parameters in the function, is summing cells or cell ranges must not exceed 30. In other words, the SUM function brackets appear in the separator (comma) cannot be more than 29 or Excel prompts are too many arguments. Needs to be involved in the sum of a constant, use "= sum (a range of cells, constants)" directly in the form of references, and generally do not have to place this constant in an absolute reference cell ...


SUMIF. .


SUMIF function sum for a range of cells that satisfy a condition, the condition can be a number, text, or an expression, you can apply in personnel, salaries and performance statistics ...


Still above the map, for example, in the payroll department needs to calculate the payroll of each situation. .


Sales Department in May 2001, overtime is to be calculated. F15 species enter the formula for ...


= SUMIF ($ C $ 3: $ C $ 12, "Sales", $ F $ 3: $ F $ 12). .


"$C$3:$C$12" to provide logic to determine which cells, "sales" for the judging criteria which statistical $C$3:$C$12 only Department in the region as "Sales Department" of the cell, $F$3:$F$12 for the sum of the actual cell range ...


Second, the image-related functions and function applications. .


I think we must also remember that when we are learning mathematics, images often need to draw a variety of functions. Graph paper for at that time was a little bit of paint, often because it's negligence, stroke no smoothing function is curve. Now, we already know that Excel almost covers our various mathematical and trigonometric functions, that can take advantage of Excel functions with Excel charting function images depicted? of course ...


Here, the author of sine and cosine functions function image depicts an example method. .


　。.


Figure 2 function for image rendering. .


– As shown in Figure 1, input data, and data entry in the table first, since the cells at a 30 degree increments between B1 and N1 entry numbers from 0 to 360, a total of 13 numbers ...


2, find the function value - in line 2 and the third line enter the SIN and COS functions, respectively, to note here is: SIN and other trigonometric functions as defined in the excel value to radians, the angle value must first be converted to radians value. Written for the specific formula (for example D2): = SIN (D1 * PI () / 180). .


3, and select image type – first selected making function image by needs of table in the data, using Excel Tools bar Shang of Chart Wizard button (also can using "insert"/"chart"), in "chart type" in the select "XY bulk points figure", again in right of "child chart type" in the select "countless stronghold smooth line bulk points figure", click [next], appears "chart data source" window, does not for any operations, directly click [next]..


4, Chart Options Operation - Chart Options operation is making an important step function curve, the "Chart Options" window to (Figure 3), followed by the operation of the project are:. .


Title – taking the title for the chart, in this case named "sine and cosine images"; for the horizontal axis and vertical axis access titles ...


Axis - can not do anything;. .


Gridlines — can make a similar graph paper online, you can also cancel the gridlines;..


Legend - Legend of the cases selected on the image on the right, this can be selected with the specific situation;. .


Data – in this case the data is not logo on the image, mainly affects artisticly. If you have particular requirements for exceptions. 5, complete image – the end of the operation, and then click [finish], an image is inserted Excel Workspace ...


6, edit the image - the image generated, the font, image size, position, not necessarily appropriate. Select the appropriate option to be modified. All these operations can first use the mouse to select the relevant parts, and then right-click pop-up shortcut menu, the shortcut menu for operation of the project. .


At this point, a sine and cosine image produced. Using the same method, you can also make a quadratic curve, logarithmic, images, and so on ...


Third, the common mathematical functions using the technique - to rounding. .


In the actual work of mathematical operations, in particular financial problems often encountered in calculations are rounded. Although Excel cells format allows you to define the number of decimal places, but in practice, we found that, in fact, rounds the number itself does not really, just display the results seem to be rounded. If you take this rounded approach, often occurs in financial operations for a few minutes of error, which is a financial operation is not allowed.. .


Excel provides "mathematical and trigonometric functions" are provided in the ROUND (number,Num_digits) of the function, whose function is based on the specified number of digits, numbers are rounded. This function has two parameters, namely the number, Num_digits. Where number is the number of rounds will be; num_digits is the desired number of digits after the decimal point. As shown in Figure 3: ...


Cell B2 for the initial data 0. .123456, B3 of the initial data 0. .234567 Would be rounded them. In cell C2, enter "= ROUND (B2, 2)", to retain two significant figures after the decimal point to get 0. .12,0. .23. In cell D2, enter "= ROUND (B2, 4)", the decimal point to retain four significant figures, are 0. .1235,0. .2346. .


　。.


Figure 3 on rounded figures. .


Numbers are rounded off, you can also use the INT (rounded function), but as is the definition of this function returns the integer value after rounding of real numbers. Therefore, use the INT function rounds still need some skills, that is, add 0 ... 5, in order to achieve the aim of rounding. In Figure 3, for example, if you use INT function, then C2 formula should be written as: "=INT (B2*100+0 ... 5)/100.".


A final note is: This article and all formulas are in excel97 excel2000 verify passed, modify the conditions of cell references and logical values that can be used similar to other occasions. .


Note: the Excel list of mathematical and trigonometric functions ...


ABS worksheet function. .


Returns the absolute value of the argument ...


ACOS worksheet function. .


Returns the arccosine of a number ...


ACOSH worksheet function. .


Returns the inverse hyperbolic cosine of the argument ...


ASIN worksheet function. .


Returns the arcsine of the argument ...


ASINH worksheet function. .


Returns the inverse hyperbolic sine of the argument ...


ATAN worksheet function. .


Returns the arctangent argument..


ATAN2 worksheet function. .


Returns the arctangent of x and y coordinate values of ...


ATANH worksheet function. .


Returns the inverse hyperbolic tangent of the argument ...


CEILING worksheet function. .


The Number increases along the absolute value of the parameter direction, rounded to the nearest integer, or base ...


COMBIN worksheet function. .


Calculated from the given number of objects in the collection to extract combinatorial number of several objects ...


COS worksheet function. .


Returns the cosine of the given angle ...


COSH worksheet function. .


Returns the hyperbolic cosine of the argument ...


COUNTIF worksheet function. .


Calculations for a given number of cells that meet specific conditions within the region ...


DEGREES worksheet function. .


Converts radians to degrees ...


EVEN worksheet function. .


Returns absolute value increases after rounding to the nearest even number..


EXP worksheet function. .


Return e e n a power of constant equal to 2.71,828,182,845,904, is the base of natural logarithms..


FACT worksheet function. .


Returns the factorial of the number, the factorial of a number equal to the number * 1*2*3* sth..


FACTDOUBLE worksheet function. .


Half of the return parameter Number factorial..


FLOOR worksheet function. .


Reduce the parameter Number in absolute terms to go in the direction of rounding, so that it is equal to the nearest multiple of significance ...


GCD worksheet function. .


Returns the greatest common divisor of two or more integers..


INT worksheet function. .


Returns the integer value after rounding of real numbers ...


LCM worksheet function. .


Returns the least common multiple of integers ...


LN worksheet function. .


Returns the natural logarithm of the natural logarithm of a number to the constant e (2.71,828,182,845,904) for the end of ...


LOG worksheet function. .


According to the specified base, and returns the logarithm of a number ...


LOG10 worksheet function. .


Returns the base 10 logarithm of a..


MDETERM worksheet function. .


Returns the matrix determinant of an array of values ...


MINVERSE worksheet function. .


Returns the matrix inverse of an array from the array..


MMULT worksheet function. .


Returns the matrix product of two arrays the result ...


MOD worksheet function. .


Returns the remainder divide two numbers the result of the same sign as the divisor in..


MROUND worksheet function. .


Returns the values of parameters in a specified base after rounding ...


MULTINOMIAL worksheet function. .


Returns the factorial of parameter and argument the ratio of the product of factorials..


ODD worksheet function. .


Returns the specified value after rounding odd ...


PI worksheet function. .


Returns the number 3 ... 14,159,265,358,979, that is, the mathematical constant PI, accurate to 15 digits after the decimal point ...


POWER worksheet function. .


Returns a given number of the power ...


PRODUCT worksheet function. .


Multiplies all the numbers given as arguments, and returns the product ...


QUOTIENT worksheet function. .


Back to the integer part of the business, this function can be used to House part of the number ...


RADIANS worksheet function. .


To convert the angle to radians ...


RAND worksheet function. .


Returns a uniformly distributed random number greater than or equal to 0 and less than 1 ...


RANDBETWEEN worksheet function. .


Returns a random number between two specified number ...


ROMAN worksheet function. .


Converts an Arabic numeral to Roman numerals in text form ...


ROUND worksheet function. .


Returns a number by the specified number of digits after rounding numbers ...


ROUNDDOWN worksheet function. .


Close to zero, down (absolute decrease) rounding numbers ...


ROUNDUP worksheet function. .


Away from zero upward (absolute increase) rounding numbers ...


SERIESSUM worksheet function. .


Returns based on the following formula for the sum of:..


SIGN worksheet function. .


Returns the number of returns 1 when the symbol when a number is positive, returns 0 for zero, a negative return-1.


SIN worksheet function. .


Returns the sine of the given angle ...


SINH worksheet function. .


Returns the hyperbolic sine of a number ...


SQRT worksheet function. .


Returns a positive square root ...


SQRTPI worksheet function. .


Returns the number pi multiplied by the square root of a..


SUBTOTAL worksheet function. .


Returns the subtotals in a list or database ...


SUM worksheet function. .


Returns a sum of all numbers in a range of cells ...


SUMIF worksheet function. .


Adds the cells specified based on the specified criteria ...


SUMPRODUCT worksheet function. .


In the given array of groups, will be multiplied by the corresponding element of the array, and returns the product of, and..


SUMSQ worksheet function. .


Returns the sum of squares of all the parameters ...


SUMX2MY2 worksheet function. .


Returns the sum of the difference of squares of corresponding values in two arrays ...


SUMX2PY2 worksheet function. .


Returns the sum of squares of corresponding values in two arrays, squares and often used in the aggregate statistics ...


SUMXMY2 worksheet function. .


Returns the sum of squares of differences of corresponding values in two arrays ...


TAN worksheet function. .


Returns the tangent of the given angle ...


TANH worksheet function. .


Returns the hyperbolic tangent of a number ...


TRUNC worksheet function. .


Number of a second section, returns an integer..


[Dvnews_page = excel function application of the logic function] is used to determine the truth value, or the composite test excel function, we call the logic function. Provided in the excel six logic functions. The AND, OR, NOT, FALSE, IF, TRUE function. .


First, AND, OR, NOT function ...


These three functions are used to return the argument logical values. Detailed below:. .


(A) AND function ...


The logic is true for all parameter returns TRUE; as long as the logic of an argument is false, return FALSE. In short, all the parameters when the AND meet certain conditions, the return result is TRUE, otherwise FALSE. .


Grammar AND (Logical1,logical2 and up) , Where Logical1, logical2, sth Represents the value for the detection of 1 to 30 conditions, the condition value may be TRUE, could be FALSE. Argument must be a logical value, or arrays, or references that contain logical values. For example:..


1, in cell B2 enter the number 50, write the formula in C2 = AND (B2> 30, B2 <60). Indeed, the B2 is equal to 50 than 30, less than 60. Therefore, the value of the two conditions (logical) are true, the return result is TRUE. .


　。.


Example 1 Figure 1 AND function. .


2, if the B1-B3 cell values are TRUE, FALSE, TRUE, it is clear that the three parameters is not true, so the formula in cell B4 =AND (B1:B3) is equal to FALSE ...


. .


Figure 2 sample AND function 2.


(B) OR function. .


OR function refers to its arguments in the group, any of the parameters of a logical value of TRUE, that is, return TRUE. Its difference with the AND function, AND function requires that all of the functions of the logical value true, the result is true. OR can function only needs any one of them is true is true. For example, the above example 2, if the formula in cell B4 is written as =OR (B1:B3), then the result is equal to TRUE..


. .


Figure 3 sample OR function ...


(C) NOT function. .


NOT negates the function used in parameter values. When you want to make sure that when a value is not equal to a specific value, you can use the NOT function. In short, that is, when the parameter when the value is TRUE, NOT function returns the results on the other hand, the result is FALSE ...


Such as NOT (2 +2 = 4), as indeed the result of 2 +2 4, the parameters of the result is TRUE, because it is NOT the function, so the return function results contrast, is FALSE. .


Second, TRUE, FALSE function ...


TRUE, FALSE logic function to return parameter values, as can be directly or formula in a cell type in the value of TRUE or FALSE. Therefore these two functions can usually not used. .


Third, IF a function ...


(A) IF function description. .


IF after the function is used to implement truth value judgments, different results based on a logical test of true and false values, so If the function is called conditional function. Its application is very wide, you can use the function IF the values and formulas for conditional testing..


Its syntax is IF (logical_test, value_if_true, value_if_false). Which evaluates to TRUE Logical_test expressed or FALSE any value or expression. This argument can use any comparison operator. .


Value_if_true is displayed in the value returned if logical_test is TRUE and Value_if_true can be another formula. Value_if_false value returned if logical_test is FALSE. Value_if_false can be another formula ...


In short, if the first argument is true logical_test returned results, then the implementation of the results of the second parameter Value_if_true, or the implementation of the results of the third parameter Value_if_false. Seven IF functions can be nested, with value_if_false and value_if_true parameters can construct complex test conditions. .


Excel also provides other functions to analyze data based on a condition. For example, if you want to calculate a string of text in a cell range or number of occurrences, you can use the COUNTIF worksheet function. If you are based on the cell a string of text or numbers in a range, you can use the SUMIF worksheet function ...


(B) IF function application. .


With formula 1, the output of the blank form ...


. .


Figure 5 personnel analyze table 1.


Personnel to the situation shown in the figure of the table, for example, the various departments on the personnel composition of the data has not yet completed, the total field (in case the cell G5) formula is:. .


=SUM(C5:F5)。.


We see the results of calculations for the 0. If you print out this form to see the beauty of the page display is not satisfactory. Is there a way to remove a total bar to 0? You might say, do not write the formula is not on the line. Of course, this is one way, but if we use the IF function, he can write the formula in the case, the same does not show the 0. How to achieve it? Just a total column of the formula (G5 only cell example) to read:. .


=IF(SUM(C5:F5),SUM(C5:F5),"")。.


Popular explanation is: If the SUM (C5: F5) is not equal to zero, then displayed in the cell SUM (C5: F5) results, or show string. .


Several explanations:..


(1) SUM (C5: F5) is not equal to the formal written SUM (C5: F5) <> 0, in excel you can omit the <> 0;. .


(2) "" indicates that the contents of the string is empty, so the result of the execution is does not display any character in a cell ...


. .


Figure 4.


2, the different conditions return different results. .


If on after the above example has a very good understanding, we can easily use the IF function applied to a wide range of areas. For example, depending on the results in the results tables distinguish between qualified and unqualified. Now we are in a class of English, for example specifying usage ...


. .


Figure 6.


The results of a class as shown in Figure 6, in order to make the final comprehensive assessment, we set in accordance with the average student achievement to determine the eligibility rules. If the subjects more than 60 points average is considered to be qualified, otherwise recorded as failed. .


According to this rule, we write formulas in the comprehensive evaluation (cell B12 as an example): ...


= IF (B11> 60, "qualified", "failure"). .


Grammar explanations, if the value is greater than 60 cell B11, performing the second parameter is displayed in the cell B12 qualifying words, otherwise the third parameter is displayed in the cell B12 unqualified word..


Column can be seen in the comprehensive assessment of students listed as C divided into 54 sub-branches, comprehensive assessment to be ineligible. The rest are qualified. .


3, multiple layers of nested function application ...


In the above example, we just simply divided into scores of qualified and unqualified, in practice, the results usually have multiple levels, such as excellent, good, medium, pass, fail and so on. There are ways to distinguish between a one-time it? You can use nested approach to implementation. Still more cases, for example, we set the rules for the comprehensive assessment when the average score of more than 90 subjects when assessed as excellent. Figure 7. .


　。.


Figure 7. .


Note: in order to explain it more convenient, here we are only two examples of nested, nested you can be more severe in accordance with the actual situation, however, be aware that Excel allows up to seven IF functions nested ...


Under this rule, we write the formula in the comprehensive assessment (in cell F12 for example):. .


=IF (F11>60,IF (AND (F11>90), "very good", "qualified"), "not qualified").


Grammar explanations, if the cell F11 is greater than 60, then the implementation of the second parameter, where the nested function, to determine the value of cell F11 is greater than 90 (in order to let everyone taste AND function application, written AND (F11> 90), can actually write only F11> 90), if in cell F12 shows good character, does not meet display qualifying words, if the value of F11 above conditions are not met, then the implementation of the third parameter that is displayed in cell F12 failed message. .


Comprehensive assessment, you can see because of f students average score of 92 points, comprehensive assessment as an outstanding ...


(C) calculated according to the conditions. .


After you learn ways to use the IF function, we'll take a look at similar Excel provides additional functions to analyze the data based on a condition. For example, if you want to calculate a string of text in a cell range or number of occurrences, you can use the COUNTIF worksheet function. If you are based on the cell a string of text or numbers in a range, you can use the SUMIF worksheet function. About the SUMIF function in the mathematical and trigonometric functions for a more detailed introduction. Here focuses on application of COUNTIF..


COUNTIF can be used to calculate a given area to meet the specific conditions of the cell number. For example, in calculating each student transcripts obtained in the course number of outstanding results. Find all of the wage table in the basic wage of more than 2,000 employees. .


Syntax for COUNTIF (range,criteria). One Range for which you want to count cells that satisfy the condition number of a cell range. Criteria to determine which cells will be counted in the conditions, in the form of a number, expression, or text. For example, the condition can be expressed as 32, "32", ">32", "apples.".


1, the results table. .


Here some examples to illustrate the application of the above results. We need to calculate is: the number of courses per student has achieved excellent results. Rules to score greater than 90 points in mind to do good. As shown in Figure 8..


. .


Figure 8.


Under this rule, we write the formula in good gates (in cell B13 as an example):. .


=COUNTIF(B4:B10,">90")。.


Grammar explanations as to calculate the range B4 to B10, which results in a branch jarry number of values greater than 90 cells. .


Excellent bar, you can see Jarry's excellent door number two. Others can then see ...


2, sales table. .


Sales performance tables can be integrated using IF, SUMIF, COUNTIF is a typical example. For example, you might want to calculate the sales order number, and total sales for each salesperson, and depending on the total shipping amount each sale should be given bonuses ...


The original data table shown in Figure 9 (a single form of raw data is listed in the water, that is arranged by order number). .


　。.


Figure 9, the original data table. .


Sales summary table as shown in Figure 10..


. .


Figure 10 Summary of sales ...


The table shown in Figure 10 is completely automated using the function method of calculating the aggregate data. First, a summary of sales personnel in accordance with the form style, as shown. Then calculate the number of orders, total orders and sales bonuses. .


(1) the number of orders – COUNTIF calculating the number of sales orders ...


To the number of sales orders ANNIE formula as an example. Formula:. .


=COUNTIF($C$2:$C$13,A17)。.


Grammar explained the purpose of calculating the cell A17 (the sales staff ANNIE) in the "sales" list $ C $ 2: $ C $ 13 range (that is, as shown in Figure 9, the original data table) the number of occurrences. .


This can be considered to be occurrences of the number of sales orders for ANNIE ...


(2) The order total - for each sales person with SUMIF aggregate sales. .


Sales order total formula of ANNIE, for example. Formula:..


= SUMIF ($ C $ 2: $ C $ 13, A17, $ B $ 2: $ B $ 13). .


This formula is "sales," checking text in cell A17 in the list $C$2:$C$13 (that is, ANNIE sales staff), then "order amount" column ($B$2:$B$13) and of the corresponding amount in..


And the corresponding amount is the total sales orders ANNIE. .


(3) IF according to the order total for sales bonuses – decide each sale should be given bonuses ...


Assume that the company's sales incentive rules when the order total more than 50,000 yuan, the reward rate for the fifteen percent, ten percent otherwise. According to this rule is still an example sales staff ANNIE. Formula is:. .


=IF(C17<50000,10%,15%)*C17。.


If the order is less than 50,000 total prize money of 10%; if the order amount greater than or equal 50000, then the bonus is 15%. .


At this point, we fully understand the logical function of Excel functions, I think we will find more useful in practical work using ...


[Dvnews_page = excel function application of the text / date / time function of] the so-called text functions, is that it can handle text strings in a formula function. For example, you can change the case or to determine the length of the text string; can replace some of the characters or the removal of some of the characters and so on. The date and time functions can be analyzed and processed in the formula date value and time value. These two functions on the list see the details, here some commonly used functions only a brief introduction. .


First, the text function ...


(A) case conversion. .


LOWER – Converts all uppercase letters in a text string to lowercase letters ...


UPPER-text converted to uppercase. .


PROPER – the first letter of a text string and converts the first letter of any non-alphanumeric characters to uppercase. The remaining letters to lowercase letters ...


These three functions are the basic grammatical form of the function name (text). Sample Description:. .


String: pLease ComE Here! Due to irregular input can be seen, this sentence capitalization ...


Through the above three functions can convert text to display style makes the text become norms. See Figure 1. .


Lower（pLease ComE Here!）= please come here!。.


upper (pLease ComE Here!) = PLEASE COME HERE!. .


proper（pLease ComE Here!）= Please Come Here!。.


. .


Figure 1.


(B) remove the string part of the character. .


You can use the Mid, Left, Right, and other functions from the inner part Gets the character long string. Specific grammar format is..


LEFT function: LEFT (text, num_chars) Text which is the text that contains the character string to be extracted. Num_chars up to the LEFT of the specified number of characters extracted. .


MID function: MID (text,start_num,num_chars) where Text is the text string containing the characters you want to extract. Start_num is the position of the first character of the text you want to extract ...


RIGHT function: RIGHT (text, num_chars) where Text is the text that contains the string of characters to extract. Num_chars specify that you want RIGHT number of characters extracted. .


For example, the string "This is an Apple." Remove characters, respectively, "This", "Apple", "is" written for the specific function of ...


LEFT ("This is an apple", 4) = This. .


RIGHT("This is an apple",5)=apple。.


MID ("This is an apple", 6,2) = is. .


　。.


Figure 2. .


(C) removal of blank string ...


In the string form, the blank is a valid character, but if a blank character string, it is easy to judge or compare the data is in error, in excel, you can use the Trim function remove the empty string. .


Syntax: TRIM (text), Text with text that needs to clear its hollow ...


Note that, Trim function does not remove a single space between words, if even this part of the spaces are to be removed, I suggest using the Replace function. For example, from the string "My name is Mary" written in the clear function of space as: TRIM ("My name is Mary") = My name is Mary See Figure 3. .


　。.


Figure 3. .


(D) comparison of the strings ...


Often in the data table comparing the different strings, then you can use the EXACT function to compare two strings are the same. This function tests whether two strings are identical. If they are identical, then return TRUE; otherwise, returns FALSE. EXACT function can be case sensitive, but ignores formatting differences. EXACT function to test the use of text within the input document. Syntax is: EXACT (text1, text2) Text1 is the first string to compare. Text2 is the second string to be compared. For example: See Figure 4. .


EXACT("China","china")=False。.


. .


Figure 4.


Second, the date and time functions. .


During processing of the data table, date and time functions is a very important basis. And Excel in this area also provides a rich set of functions for your class


(A) remove the current system time / date information. .


Used to remove the current system time/date information of the main functions are NOW, TODAY ...


Syntax are the function name (). .


(B) the acquisition of part of date/time field value ...


If you need a separate year, month, days or hours of data, you can use the HOUR, DAY, MONTH, YEAR functions directly from the date / time, remove the required data. Specific examples see Figure 5. .


For example, you need to return 2001-5-30 12:30 PM number of year, month, and day and number of hours, you can use the appropriate function ...


YEAR (E5) = 2001. .


MONTH(E5)=5。.


DAY (E5) = 30. .


HOUR(E5)=12。.


. .


Figure 5.


There are also more useful date / time functions, you can access the Schedule. Now we will be a concrete example to illustrate the text functions excel date functions and purposes. .


C example: make a nice concise analysis of personal data tables ...


1, the example shows. .


As shown in Figure 6 of the personal data of a company in the table, in addition to numbers, employee names, social security numbers, and participate in the working time is manually filled, a function for remaining bullets are calculated ...


. .


Figure 6.


In this case, we will find the detailed description of how the function:. .


(1) automatically extracted from the social security number date of birth, gender, information.


(2) automatically extracted from the length of service time to work information. .


2 related knowledge, identity card numbers ...


Learn how the identity card numbers automatically extracted from the date of birth, gender information, first need to understand the meaning of the identity card number. We know that today there are 15/18 bit ID number of points. Issued ID card number is an early 15's, and now ID cards issued by the expansion of the year (four by two changes) and added at the end of efficacy code, it becomes 18. Both ID numbers will be quite a long period of coexistence. Two ID numbers have the following meanings:. .


(1) 15-digit ID number: 1~6-digit area code, and 7~8 for the year of birth (2), 9~10 for the month of birth, 11~12 as date of birth, di13~15wei order number, and be able to determine sex, odd for male, even for women ...


(2) 18-bit ID number: 1 ~ 6 for the area code, 7 to 10 for the year of birth (4), 11 ~ 12 for the month of birth, 13 to 14 as the date of birth, 15 ~ 17 bit sequence number, and the ability to determine sex, odd for men, even for women. For the efficacy of 18-bit. .


3, the application functions ...


In this case, in order to achieve automatic data extraction, application of the following few excel functions. .


(1) IF function: based on logical expressions to test the results, return the appropriate value. IF functions allow nesting..


Syntax is: IF (logical_test, value_if_true, value_if_false). .


(2) CONCATENATE: to merge several text items into one text item..


Syntax is: CONCATENATE (text1, text2 ... ...). .


(3) MID: from the starting position of the text specified in the string, returns a specified length of characters ...


Syntax is: MID (text, start_num, num_chars). .


(4) TODAY: Returns the current date inside the computer systems ...


Syntax is: TODAY (). .


(5) the DATEDIF: calculating the number of days between two dates, months, or years ...


Syntax is: DATEDIF (start_date, end_date, unit). .


(6) VALUE: Converts the text string representations of numbers into figures..


Grammatical form: VALUE (text). .


(7) RIGHT: Returns the number of characters specified according to the last character or characters in the string of text ...


Syntax is: RIGHT (text, num_chars). .


(8) INT: Returns the integer value after rounding of real numbers. Syntax form is: INT (number).


4, formula writing and explain the (staff Andy as an example.) .


Note: in order to avoid too many nested formula, where the ID numbers are limited to 15. If you know the formula, you can perform simple modification applies to 18-bit ID number, or even apply to coexist among 15, ...


(1) According to the ID number seeking sex. .


=IF (VALUE (RIGHT (E4,3),/2=INT (VALUE (RIGHT (E4,3))/2), "female", "the man").


Formula explanation: a. . RIGHT (E4, 3) find the ID number for the representation of other figures, the actual figures obtained for the representatives of the string. .


b。. VALUE (RIGHT (E4,3) are used to from the string representations of numbers in the previous step into a digital ...


c. . VALUE (RIGHT (E4, 3)) / 2 = INT (VALUE (RIGHT (E4, 3)) / 2 is used to determine the identity card number is odd or even, of course, you can also use the Mod function to make judgments. .


d。. =IF (VALUE (RIGHT (E4,3),/2=INT (VALUE (RIGHT (E4,3))/2), "female", "the man") and if such a formula to figure out when this number is an even number, display "female", otherwise, this number is odd, or "man.".


(2) According to the ID number request date of birth. .


=CONCATENATE("19",MID(E4,7,2),"/",MID(E4,9,2),"/",MID(E4,11,2))。.


Formula explanation: a. . MID (E4, 7,2) identity card number in the number of years to get that string. .


b。. MID (E4,9,2) for the object that represents the month number in the ID number of a string ...


c. . MID (E4, 11,2) identity card number in the date to get that string of numbers. .


d。. CONCATENATE ("19", MID (E4,7,2), "/" and MID (E4,9,2), "/" and MID (E4,11,2)) purpose is to combine multiple string displayed with the ...


(3) to work according to time experience (ie seniority). .


=CONCATENATE (DATEDIF (F4,TODAY (), "y"), "year", DATEDIF (F4,TODAY (), "YM"), "months").


Formula explanation:. .


a。. TODAY () is used to find the system's current time ...


b. . DATEDIF (F4, TODAY (), "y") is used to calculate the current system time to work with the difference in time of the year. .


c。. DATEDIF (F4,TODAY (), "YM") is used to calculate the time difference between the current system time and participate in the work of the month, day and year of the dates are ignored ...


d. . = CONCATENATE (DATEDIF (F4, TODAY (), "y"), "Year", DATEDIF (F4, TODAY (), "ym"), "months") purpose is to display multiple strings are combined . .


5. Additional instructions on ...


Personnel information in this table, we also found that creation date :31 -05-2001 to display in the same cell. How is this possible? Is manually add it? Not, in fact, this date is still changing, it shows the current system time. Here is the TEXT function by function, together TODAY and create a message, the message contains the current date and date "dd-mm-yyyy" in the format. .


Specific formulas written as: = "creation date:" &TEXT (TODAY (), "dd-mm-yyyy").



At this point, we text functions, date and time functions already have a general understanding, but also envisaged a number of applications. We believe that with the ongoing research in this area, there will be more widely used. .


Report: text functions ...


Function name. .


Function description ...


Syntax. .


ASC。.


String in the full-width (double-byte) English letters changed to half-width (single byte) characters. .


ASC(text)。.


CHAR. .


Returns the character code corresponding to a number, the functions CHAR codes in other types of computer files can be converted to a character ...


CHAR (number). .


CLEAN。.


Delete the text does not print the characters. Other applications from the input string using the CLEAN function to remove the current operating system which contains not printable characters. For example, you can delete data files usually appear in the head or tail, can not print the low-level computer code. .


CLEAN(text)。.


CODE. .


Returns the numeric code of the first character in a text string. Returned code corresponds to the character set of the computer is currently using ...


CODE (text). .


CONCATENATE。.


Of certain text string into a text string. .


CONCATENATE (text1,text2,…)。.


DOLLAR. .


In accordance with the currency format, with the decimals rounded to the specified number of bits and convert text ...


DOLLAR or RMB (number, decimals). .


EXACT。.


This function tests whether two strings are identical. If they are identical, then return TRUE; otherwise, returns FALSE. EXACT function can be case sensitive, but ignores formatting differences. EXACT function to test the use of text within the input document. .


EXACT(text1,text2)。.


FIND. .


FIND used to find a different text string (within_text) within the text string (find_text), and starts from the first character of within_text returns the starting position of find_text number ...


FIND (find_text, within_text, start_num). .


FIXED。.


Specified number of decimal places to round, use a period and comma to the decimal number format to format and returns the results to a text string. .


FIXED(number,decimals,no_commas)。.


JIS. .


In the string to half-width (single-byte) English letters or katakana to change to full-width (double-byte) characters ...


JIS (text). .


LEFT。.


LEFT based on the number of characters specified text string to return the first or first few characters. .


LEFTB returns the number of bytes specified by the first or last few characters in a text string. This function is used to double-byte characters ...


LEFT (text, num_chars). .


LEFTB(text,num_bytes)。.


LEN. .


LEN returns the number of characters in a string of text ...


LENB returns text string of characters used to represent the number of bytes. This function is used to double-byte characters. .


LEN(text)。.


LENB (text). .


LOWER。.


A text string in all uppercase letters to lowercase letters. .


LOWER(text)。.


MID. .


MID returns a text string starting at the specified position in a specified number of characters, the number specified by the user ...


MIDB text string returned from the specified position in a specific number of characters, the number specified by the user. This function is used to double-byte characters. .


MID(text,start_num,num_chars)。.


MIDB (text, start_num, num_bytes). .


PHONETIC。.


Extract text strings in the alphabet (furigana) characters. .


PHONETIC(reference)。.


PROPER. .


The first letter of a text string and converts the first letter of any non-alphanumeric characters to uppercase. The remaining letters to lowercase letters ...


PROPER (text). .


REPLACE。.


REPLACE with other text strings based on the number of characters specified by replacing part of a text string in the text. .


REPLACEB with a different text string based on the specified number of characters to replace a portion of text in a text string. This function is designed for double-byte characters using ...


REPLACE (old_text, start_num, num_chars, new_text). .


REPLACEB(old_text,start_num,num_bytes,new_text)。.


REPT. .


In accordance with a given number of times to repeat text. You can repeatedly by the REPT function displays a text string, to fill the cell ...


REPT (text, number_times). .


RIGHT。.


RIGHT according to the specified number of characters to return a text string in the last one or more characters. .


RIGHTB returns the number of characters specified according to the last character or characters in the string of text. This function is used to double-byte characters ...


RIGHT (text, num_chars). .


RIGHTB(text,num_bytes)。.


SEARCH. .


SEARCH returned from start_num began to find a specific character or text string for the first time on the location of a specific number of characters. Use SEARCH to determine the character or string of text in a different position within the string of text, so that you can use the MID or REPLACE function to change the text ...


SEARCHB also available in other text string (within_text) in the Find text string (find_text), and return to the starting position find_text number. The result is that each character based on the number of bytes used, and from start_num began. This function is used to double-byte characters. In addition, it can be used FINDB text string to find in other text string. .


SEARCH(find_text,within_text,start_num)。.


SEARCHB (find_text, within_text, start_num). .


SUBSTITUTE。.


Alternative in the text string using new_text old_text. If you need to replace a specified text string in the text, use the function SUBSTITUTE; If you need to replace a text string in any text at the specified location, use the function REPLACE. .


SUBSTITUTE(text,old_text,new_text,instance_num)。.


T. .


Converts the value into the text ...


T (value). .


TEXT。.


To a specified value is converted to digital format text. .


TEXT(value,format_text)。.


TRIM. .


In addition to a single blank between words, clear all the spaces in the text. Obtained from other applications with irregular spaces in text, you can use the TRIM function ...


TRIM (text). .


UPPER。.


Converting text to uppercase. .


UPPER(text)。.


VALUE. .


Converts a text string representing the digital digital ...


VALUE (text). .


WIDECHAR。.


Single-byte characters will be converted to double-byte characters. .


WIDECHAR(text)。.


YEN. .


￥ (JPY) currency format will be converted into text and figures are rounded to the specified location ...


YEN (number, decimals). .


Second, the date and time functions ...


Function name. .


Function description ...


Syntax. .


DATE。.


Returns the number of representatives of a series of specific dates. .


DATE(year,month,day)。.


DATEDIF. .


Calculating the number of days between two dates, months, or years ...


DATEDIF (start_date, end_date, unit). .


DATEVALUE。.


The main function is to function DATEVALUE that date will be written into a series of numbers. .


DATEVALUE(date_text)。.


DAY. .


Certain days of the date of return expressed as a serial number, an integer from 1 to 31..


DAY (serial_number). .


DAYS360。.


According to the algorithm 360 days a year (total of 30 days each month, one year a total of 12 months), the return difference between the two the number of days. .


DAYS360(start_date,end_date,method)。.


EDATE. .


Returns the specified date (the start_date) before or after a specified number of months the date serial number. Use the EDATE function can be evaluated in January as the first day of the same day of maturity dates ...


EDATE (start_date, months). .


EOMONTH。.


Back to start-date in the month before or after the specified number of the last day of the series. Computable function EOMONTH particular month by the last day of the time series in the number of maturity dates for the calculation of the securities. .


EOMONTH(start_date,months)。.


HOUR. .


Returns the number of hours of time values. That is, a value between 0 and (12:00 a.. M。.) 23 (' p.. M。.) Integer, between ...


HOUR (serial_number). .


MINUTE。.


Return the time value of minutes. That is, a range of integers between 0 and 59. .


MINUTE(serial_number)。.


MONTH. .


Months of the date of return expressed as a serial number. Month is between 1 (January) and 12 (December), an integer between ...


MONTH (serial_number). .


NETWORKDAYS。.


Returns the start-data and end-data values between the full working day. Working days not including weekends and holidays specifically designated. .


NETWORKDAYS(start_date,end_date,holidays)。.


NOW. .


Returns the serial number for the current date and time ...


NOW (). .


SECOND。.


Return time value in seconds. Returns the number of seconds from 0 to 59 integers. .


SECOND(serial_number)。.


TIME. .


Returns the decimal value of a particular time, TIME function returns a decimal value from 0 through 0. number between 99,999,999, representatives from 0:00:00 (12:00:00 a.. M) and 23:59:59 (11:59:59 p.. M) between the time ...


TIME (hour, minute, second). .


TIMEVALUE。.


Text string returned by the time represented a small value. The small value is from 0 to 0. .999999999 Value, representatives from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 PM) the time between. .


TIMEVALUE(time_text)。.


TODAY. .


Returns the serial number of the current date, a serial number is a Microsoft Excel for date and time date-time code ...


TODAY (). .


WEEKDAY。.


Returns a date for the week. By default, the value is 1 (Sunday) to 7 (Saturday) between the integers. .


WEEKDAY(serial_number,return_type)。.


WEEKNUM. .


Returns a number, this number represents the week ordinal of a year ...


WEEKNUM (serial_num, return_type). .


WORKDAY。.


Returns a date (start date) working days before or after a date specified intervals the date value. Working days not including weekends and holidays specifically designated. .


WORKDAY(start_date,days,holidays)。.


YEAR. .


Returns a date year. Returns an integer value is 1900 to 9,999..


YEAR (serial_number). .


YEARFRAC。.


Start_date and end_date return the number of days between the percentage of total number of days throughout the year. .


YEARFRAC(start_date,end_date,basis)。.


[Dvnews_page = excel function application of the query and reference function], introducing the query and reference functions, we first come to learn more about the reference to knowledge. .


Role of 1, referencing ...


The role of reference in the excel worksheet is to identify a cell or range of cells, and the formula specified in the location of data used. By reference, you can use the worksheet in a formula different parts of the data, or use the same formula in a number of cell values. You can also refer to the same workbook with different sheet of cells, different cells in the workbook, or even data in other applications. .


2, reference, meaning ...


Reference need to know about the meaning of the following situations:. .


External reference – cells in different workbooks reference is called an outer reference ...


Remote references - references the data in other programs is called a remote reference. .


Relative references – when you create a formula, the cell reference or range of cells is usually relative to the relative position of the cell that contains the formula ...


Absolute references - if you copy the formula does not want to adjust the reference to excel, then use absolute references. By adding a dollar sign, such as $ C $ 1. .


3, reference, ...


Expressed about the reference, there are two methods, namely A1 and R1C1 reference style. .


(1) reference style (default) – A1 ...


A1 reference style is the default reference types excel. This type of reference to mark out the letters (from A to IV, a total of 256 columns), and digital signage line (from 1 to 65536). These letters and numbers are called row and column headings. If you want to refer to cells, enter the order of the column letter and row number. For example, C25 refers to column C and row intersection cells 25. If you want to refer to a range of cells, enter the upper-left cell of the reference area, a colon (:) and the lower-right corner of the cell reference, such as A20: C35. .


(2) reference style – the second R1C1..


In the R1C1 reference style, excel using the "R" plus the line number and "C" to add numbers to indicate the location of the cell. For example, the absolute cell reference R1C1 and A1 reference style of the absolute reference $ A $ 1 is equivalent. If the active cell is A1, the cell relative to reference R [1] C [1] to refer to the following line and right column of cells, or B2. .


After you understand the concept of references, we look at the query and references provided by the Excel function. Function can be used to query and references to find a specific value in a list or table, or you need to find a cell reference. Total in Excel provide its ADDRESS, AREAS, CHOOSE, COLUMN, COLUMNS, and HLOOKUP, and HYPERLINK, and INDEX, and INDIRECT, and LOOKUP, and MATCH, and OFFSET and the ROW and the ROWS, TRANSPOSE, VLOOKUP 16 queries referencing function.. .


One, and ADDRESS, COLUMN, ROW ...


1, ADDRESS for the given row and column headings, the establishment of the cell address of the text type. .


Its grammatical forms to: ADDRESS (row_num,column_num,Abs_num,a1,Sheet_text).


Row_num cell reference refers to the use of the line number. .


Column_num refers to cell references with column labels in..


Abs_num reference type specified in the return, a representative of an absolute reference to row number 2 represents the absolute and relative column headings, row number 3 stands for relative and absolute column headings and 4 is relative. .


A1 to A1 or R1C1 reference style of the specified logical values. If A1 is TRUE or omitted, the function returns ADDRESS A1-style reference; if A1 is FALSE, the function return ADDRESS R1C1 reference style ...


Sheet_text a text, specified as an external reference the name of the worksheet, if you omit sheet_text, not to use any work table. .


In short, the ADDRESS (line number, column number, reference type, referenced style sheet names) ...


For example, ADDRESS (4,5,1, FALSE, "[Book1] Sheet1") equal to "[Book1] Sheet1! R4C5" See Figure 1. .


　。.


Figure 1. .


2, COLUMN returns the column headers of the given reference ...


Syntax is: COLUMN (reference). .


Reference for which you want the column header cell or cell range. If reference is omitted, it is assumed to be a reference to the cell containing the function COLUMN. If the reference to a cell range, and enter the function COLUMN as a horizontal array, the function column headings in the COLUMN reference returned as a horizontal array. Reference cannot refer to multiple areas ...


3, ROW reference to return for a given line number. .


Syntax: ROW (reference).


Reference line number for the need of the cell or range of cells. If you omit reference, is assumed to be the cell where the function ROW reference. If the reference to a range of cells, and ROW functions as a vertical array input, the function ROW line number to reference the form of a vertical array to return. But the Reference area on a number of references can not. .


II, and AREAS, and COLUMNS, INDEX, ROWS ...


1, AREAS used to return the number of references contained in the region. One area that contiguous cells or a cell group. .


The syntax forms for the AREAS (reference).


Reference to a cell or range of a reference, you can also refer to multiple areas. If you need to specify the number of reference as a parameter, you must use the brackets. .


2 columns, COLUMNS are used to return an array or reference number ...


The syntax of the form COLUMNS (array). .


Array is an array of arrays, number of columns for which you want the formula, or a cell reference to a range of ...


3, ROWS used to return a reference or an array of rows. .


Its grammatical form ROWS (array).


Array is the number of lines need to be array, the array formula or a reference to a range of cells. .


Above example function shown in Figure 2.


. .


Figure 2.


4, INDEX returns table or area for the values or reference values. .


Function INDEX () comes in two forms: array and reference. The array form always returns a value or an array of values, and reference form always returns a reference ...


(1) INDEX (array, row_num, column_num) Returns an array of specified cell or cell array of values. .


Array cell range or an array constant. Row_num is an array of a row in the row number, the function returns the row number. Column_num a column ordinal of the column in the array, the function returns a numeric value from the column. Pay attention to is the Row_num and column_num must point to a cell in the array, otherwise, the function INDEX returns an error value #REF! ...


(2) INDEX (reference, row_num, column_num, area_num) return a reference to specify a reference to a cell or range of cells. .


Reference is a reference to one or more ranges of cells ...


Row_num for the reference number of rows in a row, the function returns a reference from the bank. .


Column_num is a reference to a column in the column ordinal function to return a reference from the column ...


Must be noted that Row_num, column_num and area_num must point to the reference in the cell; Otherwise, INDEX returns the # REF!. If you omit row_num and column_num, INDEX function returns the area specified by the area_num. .


Third, INDIRECT, OFFSET ...


1, INDIRECT returns the text string for the specified reference. .


When you need to change the cell reference in a formula, without changing the formula itself, use the INDIRECT function ...


The syntax of the form: INDIRECT (ref_text, a1). .


Where Ref_text is a reference to a cell, this cell can contain references to references, R1C1-style A1-style, is defined as the reference name of the reference to a cell, or text string. If ref_text is not a valid cell reference, INDIRECT function returns an error value #REF! ...


A1 is a logical value that specifies the cell contains a reference in the type of ref_text. If a1 is TRUE or omitted, ref_text be interpreted as A1-style reference. If a1 is FALSE, ref_text be interpreted as R1C1-style reference. .


Is important to note: If ref_text is a reference to another workbook (external reference), the workbook must be open. If the source workbook is not open, INDIRECT function returns an error value #REF! ...


2, OFFSET function is used to refer to as the reference specified by the given offset by the new reference. .


References returned can be a cell or range of cells, and you can specify the number of columns or rows returned ...


The basic syntax is: OFFSET (reference, rows, cols, height, width). .


Where the reference offset of the variable as a frame of reference (reference must be a reference to a cell or contiguous range of cells, otherwise, the OFFSET function returns an error value #VALUE!).


rows variables that frame of reference relative to offset the upper-left cell upward (downward) shift in the number of rows (eg rows using 2 as a parameter, the target reference to the upper-left cell in lower than the reference line 2), line number can be positive (representing the cell in the bottom of the starting reference) or negative (representing the cell in the initial quote above) or 0 (representing the starting reference cell). .


Cols offset is relative to the upper-left cell of the frames of the left (right) to offset the number of columns (for example use 4 cols parameter, that represents the destination refer to a range on the upper left corner cell of Gebbie reference to the right of 4 columns), the number of columns can be positive (on behalf of the right side of the starting reference cell) or negative (on behalf of the starting reference cell left.).


If the number of rows or columns than offset the edge of the worksheet, OFFSET function returns the error value # REF! . variable height, said height, ie the region to return a reference to the number of rows (height must be positive). variable width, said width, ie the region to return a reference to the number of columns (width must be positive.) If you omit the height or width, is assumed to reference the same height or width. For example, the formula OFFSET (A1, 2,3,4,5), said the lower than in cell A1 and the right side line 2 column 3 column 4, line 5 (ie the area D3: H7 region). .


This shows that the OFFSET function doesn't actually move any cells or change selections, it simply returns a reference ...


Fourth, HLOOKUP, LOOKUP, MATCH, VLOOKUP. .


1, LOOKUP and MATCH function ...


LOOKUP function returns the vector (row or column range) or array values. This series of functions or values in the table for the first line of the array to find the specified value, and thus return to the current column in the table or array value at the specified row. When comparing the value of the first row in the data table and want to find the following line in the given data, use the function HLOOKUP. When comparing the data to find values in the left column, use the function VLOOKUP. .


If you need to find out the position of the matched elements rather than matching the elements themselves, you should use the MATCH function instead of the function LOOKUP. MATCH function to return a number in the specified mode with the specified array of matching elements in the appropriate location. From the above analysis we can see, looks for the function of function, is based on search criteria and return to your search area within a data value of the data; the second is based on search criteria, return to your search area within a certain location of the data values. Take advantage of these two features, not only for data querying, but also to address such as "rated" practical issues, such as..


2, LOOKUP to return vector (row or column range) or array values. .


LOOKUP function has two syntax forms: vectors and arrays ...


(1) vector form. .


LOOKUP function vector form-row or one-column range (vector) find value in, and then returns the second row or one-column range in the same position value ...


The basic syntax is the form of LOOKUP (lookup_value, lookup_vector, result_vector). .


Lookup_value LOOKUP to the function you're looking for in the first vector value. Lookup_value can be numbers, text, logical values, or contains the name or numerical references..


Lookup_vector to contain only one row or column. Lookup_vector values can be text, numbers, or logical values. .


Keep in mind that numerical Lookup_vector must be sorted in ascending order: sth,-2,-1, 0, 1, 2, a-z, FALSE, and TRUE, and sth; otherwise, the LOOKUP function does not return the correct results. Text is case-insensitive..


Result_vector contains only one row or column of the region, its size must be the same with the lookup_vector. .


If LOOKUP can't find the lookup_value, it finds largest value in lookup_vector that is less than or equal to lookup_value..


If lookup_value lookup_vector in less than the minimum, the function LOOKUP returns the error value # N / A. .


Sample as described in Figure 3.


. .


Figure 3.


(2) array. .


Function array form of LOOKUP in an array of the first row or column to find the specified value, and then returns an array of the last row or column in the same location as the value. Under normal circumstances, it is best to use the function VLOOKUP or HLOOKUP function to replace the function array form of LOOKUP. Function LOOKUP of this form is primarily for compatibility with other spreadsheet. On the use of the array form of LOOKUP will not repeat them here, interested can refer to the help for Excel ...


3, HLOOKUP and VLOOKUP. .


HLOOKUP to in the first row of a table or an array of finds the specified value, and then returns a table or an array at the specified row in the current column value..


VLOOKUP is used to form the first column or the value of the array to find the specified value, and thus returns the current row in a table or array value at the specified column. .


When comparing values in the first row of the datasheet, and you want to find the following data in a given row, use the HLOOKUP function ...


When comparing the data to find values in the left column, use the function VLOOKUP. .


Syntax is:..


HLOOKUP (lookup_value, table_array, row_index_num, range_lookup). .


VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。.


Which, Lookup_value that you are looking for value, it must be defined in the left column to find area. Lookup_value can be a value, reference or text string. .


Table_array area you are looking for locating data area, above the left-most column of the lookup value must be in the region. Reference to a range or a range name can be used ...


Row_index_num be returned to table_array matching value in the row number. Row_index_num to 1, the return value of the first row table_array, row_index_num is 2, the return value table_array the second line, and so on. .


Col_index_num the column number. Left-most column is 1, the right column is 2, and so on ...


Range_lookup is a logical value that specifies when the function HLOOKUP to find an exact match or approximate match. .


VLOOKUP function are described in detail below ...


In short, VLOOKUP function, the search area according to the value of the left column, to find other columns of data within the region and returns the column's data, for the letters, the search is not case sensitive. Therefore, the function VLOOKUP lookup can be achieved two purposes: first, accurate lookups. Second, the approximate search. The following are instructions. .


(1) accurate find – the value of the left column, to accurately find other columns of data ...


Example: Creating a payroll and pay slips. .


First set up an employee payroll ...


. .


Figure 4.


Then, according to the payroll of each employee's salary be created, the wages for the application of Article Vlookup function established. The employee Sandy (No. A001) to create an example the wages section. .


The first step, copy the title bar..


The second step, the number at (A21) to write A001. .


The third step, in the name (B21) create formulas ...


= VLOOKUP ($ A21, $ A $ 3: $ H $ 12,2, FALSE). .


Syntax explains: in the $A$3:$H$12 range (wage table) exact match find to cell A21 of the line, and the content included in the second column in the row the cell ...


The fourth step, and so on, in the next cell to write the appropriate formula. .


　。.


Figure 5. .


(2) the approximate lookup – according to defined areas of value for the left-most column, does not find the exact value of the other columns data..


Example: According to project the corresponding proportion of the total prize money of different extraction. .


The first step, the establishment of a proportion of total items and bonuses comparison, as shown in Figure 6. Item numbers are greater than the total amount. When projects totalling $ 0~5000, prizes for 1%, and so on ...


. .


Figure 6.


The second step assumes that a project of the project amounted to 13,000 yuan, enter the formula in cell B11. .


=VLOOKUP(A11,$A$4:$B$8,2,TRUE)。.


Specific bonuses can be obtained for 5%, as shown in Figure 7. .


　。.


Figure 7. .


4, MATCH function ...


MATCH function has two functions, the two operations return to a position value. .


Is to determine a value in a column in a range of exactly where, this exact query sorts has nothing to do with the list of ...


The second is to determine the value at a given position in the sorted list, which does not require exact match. .


Syntax structure is: MATCH (lookup_value,lookup_array,match_type).


lookup_value value to be searched. .


Lookup_array: you're looking for (you must be a row or column).


match_type: matching form, there are 0,1, and -1 are three options: "0" indicates that an accurate search. "1" indicates that the search for values less than or equal to the maximum of search, lookup zone must be in ascending order. "-1" Indicates that the search for a value greater than or equal to the minimum, look for areas to be arranged in descending order. More than one search, if no matching value, it returns # N / A. .


Five, HYPERLINK ...


The so-called HYPERLINK, that is, create a shortcut to open the document or a network drive, or even INTERNET address. More simply, is to enter a cell, after this function, can go to any location you want to go. In one excel document, perhaps you need to reference another document or word document excel, etc., the steps and methods like this:. .


　　(1) select the cells you want to enter this function, such as B6..


(2) used in the toolbar click the "Paste Function" icon will appear "Paste Function" dialog box, in the "Function Category" box, select "common" in the "function name" box, select HYPERLINK, at this time the bottom of the dialog box will appear a brief explanation of the function. .


　　(3) click "OK" HYPERLINK function parameter settings dialog box will pop up after ...


(4) "Link_location" the file type you want to link or INTERNET address, such as: "c: \ my documents \ excel functions.. Doc"; in the "Friendly_name" type "excel function" (This is assuming we want to open The document is located in c: \ my documents under the file "excel function.. doc"). .


(5) click "OK" to return to the Excel document you are editing, and then click cell B6 to immediately open the word meeting minutes document editing ...


HYPERLINK function is used to create a variety of shortcuts, such as opening a document or a network drive, go to a website and so on. Put a little exaggerated, in a cell, enter this function, you can jump to any position we want to go. . .


VI other (CHOOSE, and TRANSPOSE).


1, CHOOSE function. .


Function CHOOSE uses index_num to return a numeric value in the parameter list. Using functions to CHOOSE can be based on the index number returned up to 29 in the selected values of any numeric ...


Grammatical form: CHOOSE (index_num, value1, value2, ...). .


Index_num to specify parameter values for the selected parameter ordinal. Index_num must be a number between 1 and 29, or a formula or cells that contain numbers from 1 to 29 references..


Value1, value2, ... the 1-29 numeric arguments, the function CHOOSE-based index_num, select a value or perform the appropriate action. Parameter can be numbers, cell references, defined names, formulas, functions, or text. .


2, the TRANSPOSE function ...


TRANSPOSE transpose used to return the region. TRANSPOSE must be a region function as an array formula in the form of input, the region the number of rows and columns, respectively, and the array of columns and rows the same. Can be changed using the TRANSPOSE worksheet function or macro table array of vertical or horizontal direction. .


Grammatical forms to TRANSPOSE (array).


Array for the need to transpose the array or range of cells in the worksheet. Is the so-called transpose the array, the first row of the array as a new array of the first column, second row of the array as a new array of the second column, and so on. .


Example, have been arranged in transverse transpose a performance table for vertical arrangement ...


. .


Figure 8.


The first step, the need to transpose the form for more than one cell, so the methods need to enter the formula an array formula. So first of all need to transpose the selected range. Here we set the switch to set the range of storage after the A9. . B14. .


The second step, in the standard toolbar, click the "paste function" icon appears "paste function" dialog box, in the "classification of functions" box, select the "lookup and reference functions" box, select TRANSPOSE, at this point in the dialog box will appear at the bottom of the brief explanation of the function. Click "OK" TRANSPOSE function parameter settings dialog box will pop up after ...


. .


Figure 9.


The third step is to select the range of that array A2. . F3. .


The fourth step, because here is entered as an array formula, so you need to press CRTL+SHIFT+ENTER to determine if an array formula, are displayed in the formula "{}". Is transposed successfully, as shown in Figure 10..


. .


Figure 10.


We introduced the excel above the search and reference functions, the flexible application of such functions to reduce duplication of data entry for is helpful. Here only some examples of better ideas, I believe we will come up with a more practical application in the practical application of methods. [Dvnews_page = excel function application of statistical functions] excel worksheet functions for the statistical area for statistical analysis of data. For example, the statistics table function can be used statistical sample variance, the frequency distribution of interval data.。 In this article, focuses on some of the common statistical functions, belonging to the areas of statistics functions do not repeat, detailed usage can refer to Excel Help and related books ...


Before introducing the statistical functions, please take a look at details of the function name. Is found that some functions are very similar, but more in the name of a letter A? For example, AVERAGE and AVERAGEA; COUNT and COUNTA. Basically, the function name with A the time of enumeration not only statistics, and text and logical values (such as TRUE and FALSE) will also be included. In the following I will focus on non-A, the use of several common functions. .


One, for averaging the AVERAGE statistical functions, TRIMMEAN..


1, find the parameters of the arithmetic mean of the function AVERAGE. .


Syntax for the AVERAGE (Number1,number2, ...)..


One Number1, number2, ... In order to calculate the average of 1 to 30 parameters. These parameters can be numbers, names or numbers involved, arrays, or references. If the array or cell reference parameter in the text, logical values, or empty cells, its value is ignored. However, if the cell contains a zero value is taken into account. .


2 average, and internal data sets TRIMMEAN..


Function TRIMMEAN start data set to remove the head and tail of a certain percentage of data points, and then averaged. When hope is removed in the analysis part of the calculation of the data, you can use this function. For example, we calculate the average score in the athletes used to remove a highest score, get rid of a minimum score, XX score of contestant number, you can use this function to calculate. .


TRIMMEAN syntax form (array,percent).


Array screening is required of them and averaging the array or data area. Percent to be removed for the calculation of the proportion of data points, for example, if percent = 0. .2, 20 data points in the set, we must remove the four data points (20 x 0. .2), Remove the two head and tail removed 2. Function TRIMMEAN will remove the number of data points rounded down to the nearest multiple of 2. .


3, for example: example also lists with a method for solving the AVERAGEA function ...


Annie players seeking the entry points. Here, we first assume that the players have scores from high in the end of the sort described in the following order, we will learn more about the method. .


　。.


Figure 1. .


Second, for the sake of statistical functions cell number COUNT ...


Grammatical form COUNT (value1, value2, ...). .


Where Value1, value2, sth Parameters that contain or refer to various types of data (1~30), but only numeric data types are counted. Function COUNT at the time of counting, are numbers, empty, logical values, dates, or text representations of numbers calculation; error values or text cannot be translated into numbers are ignored ...


If the parameter is an array or reference, only the statistical array or reference number; array or reference, empty cells, logical values, text or error values are ignored. If you want statistics logical values, text or error values, you should use the function COUNTA. .


For example uses the COUNT function, example also lists a function COUNTA. Above cases, for example, to count the total number of judges involved in the score (using function COUNTA), and valid scores are given a few judges (with function COUNT).


. .


Figure 2.


Third, find the frequency distribution of the data area FREQUENCY. .


Because the FREQUENCY function returns an array that must be entered as an array formula ...


Grammatical form FREQUENCY (data_array, bins_array). .


Data_array is an array or a reference to a set of values used to calculate frequencies. If data_array contains no values, FREQUENCY function returns a null array. Bins_array is an array or an array reference, calculates the frequency setting data_array points. If bins_array contains no values, FREQUENCY function returns the number of elements data_array..


FREQUENCY usage seems quite complex, but has proved very useful. Wages can be calculated in different segments such as the distribution of persons, the age distribution of employees, such as the distribution of student achievement. Specific examples here to explain the basic usage. .


To calculate the age distribution of employees a company description as an example. In the job list lists the employee's age. The age of 28, 25, 31, 21, 44, 33, and 35, and enter into the cell C4:C11. This age is data_array. Bins_array is another used to age interval of the grouped values. In this case, Bins_array is an C13:C16 cell, respectively, containing a value of 25, 30, 35, and 40.. Selected in this case 5, after the vertical adjacent cells, ie, enter the following formula an array formula. Returns the number of elements in the array than bins_array (array) the number of elements in the more than 1. The fifth number 1 greater than the maximum interval (40) values (44) number. Function FREQUENCY ignores blank cells and text values. .


{=FREQUENCY (C4:C11,C13:C16)} {2;2;2;1;1} ...


. .


Figure 3.


Fourth, a set of requirements to meet the different requirements of the data set the value of the function. .


1, and maximum values of a data set MAX and min MIN..


These two functions MAX, MIN data set is used to solve the extreme (ie maximum, minimum). Function usage is very simple. Syntax for the function (number1, number2, ...), which Number1, number2, ... need to find the maximum value for the 1 to 30 values. If the array or reference to calculate the blank cells, logical values or text will be ignored. So if the logical values and text can not be ignored, use the function with A or MAXA MINA instead. .


2, and the k-th largest value in a LARGE data set with the k-th smallest value in a SMALL ...


These two functions LARGE, SMALL AND MAX, MIN is to imagine, the difference is not extreme they return, but the first K values. Syntax is: function (array, k), where k-Array is a need to find the minimum area array or numeric data. K for the returned data in the array or range of data in the location (if it is for the descending row LARGE, SMALL function if the row is from small to large.) .


Speaking of which, we can get it. If K=1 or K=n (assuming that the dataset has n-data), whether you can return the data had set the maximum or minimum values..


3, find the median data set MEDIAN. .


MEDIAN function returns the median of the values collection. So-called median mean median number in a set of data, in other words, in this set of data, data values that are greater than half, half of the data values that are less than ...


Grammatical form MEDIAN (number1, number2, ...) which Number1, number2, ... is the need to find the median of the 1-30 numeric argument. If the array or reference argument contains text, logical values, or empty cells, those values are ignored, but its value is zero, the cell will be taken into account. .


Is important to note, if the arguments contained in the collection has an even number of numbers, MEDIAN function returns the average of the two numbers in the middle ...


4, find the highest frequency of the data set the number of MODE. .


MODE function is used to return an array or most frequently occurring value in the data area. As with MEDIAN, MODE is also a position measurement functions ...


Grammatical form of MODE (number1, number2, ...) which Number1, number2, ... are used for the plural (the plural refers to a set of values in the highest frequency values) are 1 to 30 arguments, you can use a single array (ie, a reference to the array area) instead of the parameters separated by commas. .


5, the function example above ...


Year-end bonus to a unit allocation table as an example. In the example, we will use these functions to solve the year-end bonus in the distribution of units of the maximum amount, the minimum amount, the average amount, the amount of intermediate, mode, amount and the second highest amount. .


Detailed formula of writing can be clearly seen from the figure, this will not go into ...


. .


Figure 4.


Fifth, to rank the function RANK, PERCENTRANK. .


1, a numeric value in a set of values in the function of rank RANK ...


Data values in the list ranking is the relative size of the other values, of course, if the data list has been sorted out, the value of the ranking is its current location. Data can be used to sort the list provided excel sorting complete. .


Syntax for RANK (number,REF,order), where Number is the need to find a rank number Ref are arrays, or references that contain a set of numbers. Order is a number used to indicate the rank of ...


If the order is 0 or omitted, the ref excel data as a list in descending order according to rank. .


If the order is not zero, Microsoft Excel will be ref to rank in ascending order of the list ...


It should be noted that the RANK function in qualifying for the same number of repeats. However, the number of repeated values would affect subsequent ranking. Ah, this concept seems to parallel the first of several ah. For example, an integer, if a number 10 appears twice, and its rank is 5, the 11 qualifying for the 7 (not qualifying for the 6 value.) .


2, and specific percentile of values in a dataset of the PERCENTRANK function ...


This PERCENTRANK function can be used to view specific data in the data centralized location. For example, you can use a particular computing function PERCENTRANK ability test scores at all proficiency test scores in the position. .


Syntax for PERCENTRANK (array,x,significance) where the Array is determined by the relative position between them numeric array or range of numbers. X is an array for which you want the rank values. Significance is optional, that represents the returned percentage value of the number of significant digits. If omitted, PERCENTRANK function reserved 3 decimals ...


3, and ranking the example. .


Description in a year-end bonus distribution, for example, employee Annie's ranking as an example to demonstrate the formula written..


Bonus ranking formula written as:. .


=RANK(C3,$C$3:$C$12)。.


Written as a percentage ranking of the formula:. .


=PERCENTRANK($C$3:$C$12,C3)。.


. .


Figure 5.


We introduced excel above the more commonly used statistical functions of several functions, more specialized areas related to statistical functions can be found in the statistical details and all related books. .


Schedule: ...


Function name. .


Function description ...


Grammatical forms. .


AVEDEV。.


Returns a set of data with the average of the mean absolute deviation, that is dispersion. .


AVEDEV(number1,number2, …)。.


AVERAGE. .


Arithmetic mean return parameters ...


AVERAGE (number1, number2, ...). .


AVERAGEA。.


Parameter list of values in the calculation of average (arithmetic mean). Not only numbers, and text and logical values (such as TRUE and FALSE) will also be included. .


AVERAGEA(value1,value2,…)。.


BETADIST. .


Returns the Beta cumulative distribution function of function values. Beta distribution cumulative function is commonly used to study samples and changes in the collection of things ...


BETADIST (x, alpha, beta, A, B). .


BETAINV。.


Returns the cumulative beta distribution function of the inverse function value. That is, if the probability = BETADIST (x, ...), then BETAINV (probability, ...) = x. beta cumulative distribution function can be used for project design, given the expected completion time and change parameters, simulate the possible completion time. .


BETAINV(probability,alpha,beta,A,B)。.


BINOMDIST. .


Return $ a binomial distribution probability ...


BINOMDIST (number_s, trials, probability_s, cumulative). .


CHIDIST。.


Back to γ2-tailed probability distribution. inspection-related distribution and γ2 γ2. Γ2 test can be compared with observations and expectations. .


CHIDIST(x,degrees_freedom)。.


CHIINV. .


Returns the inverse of the gamma distribution of 2 one-tailed probability function ...


CHIINV (probability, degrees_freedom). .


CHITEST。.


Back independence test value. Γ2 distribution function CHITEST return statistics and the corresponding degrees of freedom. .


CHITEST(actual_range,expected_range)。.


CONFIDENCE. .


Returns the confidence interval of the population mean. The confidence interval is the sample mean on either side of the area ...


CONFIDENCE (alpha, standard_dev, size). .


CORREL。.


Return array1 and array2 cell ranges between the correlation coefficient. The correlation coefficient to determine the relationship between the two properties. .


CORREL(array1,array2)。.


COUNT. .


Returns the number of arguments. Function COUNT to calculate figures the number of items in the array or cell range ...


COUNT (value1, value2, ...). .


COUNTA。.


Return null parameter set the number of Central Africa. COUNTA function can be calculated using an array or range of cells in the number of data items. .


COUNTA(value1,value2, …)。.


COVAR. .


Returns covariance, logarithmic base product of the deviation of the mean, covariance can decide the relationship between the two data sets ...


COVAR (array1, array2). .


CRITBINOM。.


Back to the cumulative binomial distribution is greater than the critical value equal to the minimum. This function can be used for quality inspection. .


CRITBINOM(trials,probability_s,alpha)。.


DEVSQ. .


Return to relate to their sample mean values of squares of deviations of data points and..


DEVSQ (number1, number2, ...). .


EXPONDIST。.


Back to exponential distribution. Events can be created using the function EXPONDIST the time interval between the models. .


EXPONDIST(x,lambda,cumulative)。.


FDIST. .


Returns the f probability distribution. Use this function to determine whether two data series have different degrees of ...


FDIST (x, degrees_freedom1, degrees_freedom2). .


FINV。.


Back to the inverse of the probability distribution function F value. .


FINV(probability,degrees_freedom1,degrees_freedom2)。.


FISHER. .


Returns the Fisher transformation of x. This transformation produces a function that is approximately normally distributed rather than skewed..


FISHER (x). .


FISHERINV。.


Back to the Inverse Fisher Transform. This transformation can be analyzed using the data range or the correlation between array. .


FISHERINV(y)。.


FORECAST. .


According to the given data to calculate or predict the future value..


FORECAST (x, known_y's, known_x's). .


FREQUENCY。.


Return to a vertical array of data in an area frequency distribution. .


FREQUENCY(data_array,bins_array)。.


FTEST. .


Returns the result of an f-test. F-test is returned when an array 1, array 2 when there is no significant difference in variance of the one-tailed probability. You can use this function to determine whether the variances of the two samples of different ...


FTEST (array1, array2). .


GAMMADIST。.


Back to the gamma distribution. You can use this function to study variables with skewed distribution. Gamma distribution is often used queue. .


GAMMADIST(x,alpha,beta,cumulative)。.


GAMMAINV. .


Returns the inverse of the gamma cumulative distribution function function ...


GAMMAINV (probability, alpha, beta). .


GAMMALN。.


Returns the natural logarithm of the Gamma function, Γ (x). .


GAMMALN(x)。.


GEOMEAN. .


Returns the geometric mean of an array of positive numbers or range of data ...


GEOMEAN (number1, number2, ...). .


GROWTH。.


According to the data given the exponential growth predicted value. .


GROWTH(known_y’s,known_x’s,new_x’s,const)。.


HARMEAN. .


Returns the harmonic mean of the data collection. The harmonic mean arithmetic mean to each other with countdown countdown ...


HARMEAN (number1, number2, ...). .


HYPGEOMDIST。.


Returns the hypergeometric distribution. .


HYPGEOMDIST(sample_s,number_sample,。.


population_s, number_population). .


INTERCEPT。.


Using the known x values and y values and y-axis intercept of a straight line. .


INTERCEPT(known_y’s,known_x’s)。.


KURT. .


Peak of the returned data set ...


KURT (number1, number2, ...). .


LARGE。.


Back to the first data set in the k-max. Use this function to choose according to the relative standard value. .


LARGE(array,k)。.


LINEST. .


Uses the least-squares method to calculate optimal line fitting to the known data, and returns an array describing this line ...


LINEST (known_y's, known_x's, const, stats). .


LOGEST。.


In the regression analysis, calculated the most observation data set exponential regression curve fitting, and returns an array describing the curve. .


LOGEST(known_y’s,known_x’s,const,stats)。.


LOGINV. .


Returns the inverse of the lognormal cumulative function is a function of x..


LOGINV (probability, mean, standard_dev). .


LOGNORMDIST。.


Return x, the cumulative lognormal distribution function. .


LOGNORMDIST(x,mean,standard_dev)。.


MAX. .


Returns the maximum value in a DataSet..


MAX (number1, number2, ...). .


MAXA。.


Returns the maximum value of the parameter list. .


MAXA(value1,value2,…)。.


MEDIAN. .


Returns the median of the values collection. The median is the median of a set of data ...


MEDIAN (number1, number2, ...). .


MIN。.


Returns the minimum value of the parameter table. .


MIN(number1,number2, …)。.


MINA. .


Returns the smallest value in a parameter list ...


MINA (value1, value2, ...). .


MODE。.


Returns an array or data in a frequency region the most value. .


MODE(number1,number2, …)。.


NEGBINOMDIST. .


Returns the negative binomial distribution ...


NEGBINOMDIST (number_f, number_s, probability_s). .


NORMDIST。.


Returns the mean and standard deviation of the cumulative normal distribution function. .


NORMDIST(x,mean,standard_dev,cumulative)。.


NORMINV. .


Returns the mean and standard deviation of the inverse of the cumulative normal distribution function function ...


NORMINV (probability, mean, standard_dev). .


NORMSDIST。.


Back to the cumulative standard normal distribution function, the distribution of mean 0 and standard deviation of 1. .


NORMSDIST(z)。.


NORMSINV. .


Inverse of the cumulative function returns the standard normal distribution function. The distribution has a mean of 0 and a standard deviation of 1.


NORMSINV (probability). .


PEARSON。.


Back to Pearson (Peel Health) the product moment correlation coefficient, r, which is a range of -1. .0 To 1. Between .0 (including -1. .0 And 1. .0 Included), the dimensionless index, the two data sets reflect the linear correlation between the degree. .


PEARSON(array1,array2)。.


PERCENTILE. .


Returns the k-percentage-point of the range of values. You can use this function to establish acceptable thresholds. For example, you can determine the ranking score above 90% test candidates ...


PERCENTILE (array, k). .


PERCENTRANK。.


Returns a specific value in a data set the percentage of qualifying. This function can be used to view specific data in the data centralized location. For example, you can use a particular computing function PERCENTRANK ability test scores at all proficiency test scores in the position. .


PERCENTRANK(array,x,significance)。.


PERMUT. .


Collection returned from the given number of objects in a select number of several objects arranged. May arrange to have the internal order of any set or subset of objects or events. Permutations and combinations of different, the combination of internal order is not meaningful. This function can be used for Lottery calculations of probability in..


PERMUT (number, number_chosen). .


POISSON。.


Back to Poisson distribution. Poisson distribution is often used to predict over time the number of events, such as through the toll station within a minute the number of cars. .


POISSON(x,mean,cumulative)。.


PROB. .


Returns an array of a probability event falls within a specified area in the sum of the probabilities of events. If no Upper_limit is given, it returns the probability values in x_range is equal to lower_limit..


PROB (x_range, prob_range, lower_limit, upper_limit). .


QUARTILE。.


Quartile returns a dataset. Quartile is commonly used in sales and the overall measurement data set to group. For example, you can use QUARTILE function obtained in the top 25% of total revenue value. .


QUARTILE(array,quart)。.


RANK. .


Returns a numeric value in a set of values in the row. Numerical rank is the size and relative to other values in the list (if the sequence has been row in the list, then the numerical rank is in its current location).


RANK (number, ref, order). .


RSQ。.


Back known_y's and known_x's according to the data points calculated Pearson product moment correlation coefficient squared. For more information, see the function REARSON. R-squared value y can be interpreted as the proportion of variance with x variances. .


RSQ(known_y’s,known_x’s)。.


SKEW. .


Returns the skewness of the distribution. Skewness reflects the average degree of asymmetry of the distribution center. Positive skewness indicates that distribution of asymmetric tail extending toward more positive. Negative skewness indicates that asymmetric distribution tail extending toward more negative ...


SKEW (number1, number2, ...). .


SLOPE。.


Back known_y's and known_x's according to the data points in fitting the slope of the linear regression line. Any two points on the slope of the heavy line straight away and the ratio of horizontal distance, that is, rate of change of the regression line. .


SLOPE(known_y’s,known_x’s)。.


SMALL. .


Returns the k-th smallest value in a DataSet. Use this function to return a DataSet values on a specific location ...


SMALL (array, k). .


STANDARDIZE。.


Back to the mean as the average of the standard-dev as the standard deviation of the distribution of normalized values. .


STANDARDIZE(x,mean,standard_dev)。.


STDEV. .


Estimates the standard deviation. Standard deviation reflects the relative to the average (mean) of the extent to ...


STDEV (number1, number2, ...). .


STDEVA。.


Estimates based on a sample standard deviation. The standard deviation value relative to the average (mean) the degree of dispersion. Text values and logical values (such as TRUE or FALSE) will also be included. .


STDEVA(value1,value2,…)。.


STDEVP. .


Return out of the entire population given as arguments of the standard deviation. Standard deviation reflects the relative to the average (mean) of the extent to ...


STDEVP (number1, number2, ...). .


STDEVPA。.


Calculate the standard deviation of the overall sample. The standard deviation value relative to the average (mean) the degree of dispersion. .


STDEVPA(value1,value2,…)。.


STEYX. .


Returns the value returned by linear regression method to calculate the standard error of the predicted y values generated by. Standard error is a measure on the basis of a single x variables to calculate the amount of error of the predicted y values ...


STEYX (known_y's, known_x's). .


TDIST。.


Back to the percentage of students t-distribution (probability), t distribution value (x) is the calculated value of t (to calculate the percentage points). t distribution for small sample data set of hypothesis testing. Use this function to replace the t distribution critical values table. .


TDIST(x,degrees_freedom,tails)。.


TINV. .


Returns as student's t distribution of the probability and the degree of function of t..


TINV (probability, degrees_freedom). .


TREND。.


A linear regression line to return a set of vertical coordinates (y value). Find that a given array known_y's and known_x's line (least square method) and returns the value of the specified array new_x's on the line corresponding y values. .


TREND(known_y’s,known_x’s,new_x’s,const)。.


TRIMMEAN. .


Returns the average within the DataSet. TRIMMEAN function removed from the head and tail of a data set a percentage of the data points and then averages. When you want to exclude a subset of the data in the analysis of your calculations, you can use this function ...


TRIMMEAN (array, percent). .


TTEST。.


Student's return - t test related to probability. TTEST function can be used to determine whether two samples may come from two with the same overall mean. .


TTEST(array1,array2,tails,type)。.


VAR. .


Estimates the variance in..


VAR (number1, number2, ...). .


VARA。.


Estimates based on a sample of the variance. Not only numbers, text values and logical values (such as TRUE and FALSE) will also be included. .


VARA(value1,value2,…)。.


VARP. .


Calculates the variance of a..


VARP (number1, number2, ...). .


VARPA。.


Calculate the overall sample variance. Not only numbers, text values and logical values (such as TRUE and FALSE) will also be included. .


VARPA(value1,value2,…)。.


WEIBULL. .


Returns the Weibull distribution. This function is used for reliability analysis, for example, calculate the mean time to failure of equipment ...


WEIBULL (x, alpha, beta, cumulative). .


ZTEST。.


Back to the two-tailed z test P value. Z test data set or array generated according to the standard score of x, and returns the normal distribution of the two-tailed probability. You can use this function returns the total extracted from a specific observation likelihood estimates. .


ZTEST(array,x,sigma)。.


[Dvnews_page = excel function application engineering function] excel in the engineering functions and statistical functions similar to belong to the more specialized areas of function. Therefore, in the text the author also describes only the works of several commonly used functions, please refer to excel more help and professional books. As the name implies, project worksheet function is used for engineering analysis functions. excel in providing a total of nearly 40 works functions. Project worksheet function by the "Analysis ToolPak" provides. If you can not find such a function, it may need to install the "Analysis ToolPak." .


First, the "Analysis ToolPak" installation ...


As shown. .


　。.


Figure 1. .


(1) in the "Tools" menu, click the "add-ins" command ...


(2) If the "Add-Ins" dialog box, there is no "Analysis ToolPak", click "Browse" button, navigate to the "Analysis ToolPak" add-in file "Analys32.. Xll" the drive and folder (usually located in the "Microsoft office \ office \ Library \ Analysis" folder); If you do not find the file, you run the "install" program. .


(3) select "Analysis ToolPak" check box.


Second, the engineering function classification. .


In the Excel Help system engineering function in General can be divided into three types, namely:..


(1) of the complex processing functions. .


(2) in a variety of digital systems (system such as decimal, hexadecimal, octal and binary systems) to numeric conversion functions between ...


(3) measurement system in different numerical conversion functions. .


In the article to explain more clearly to the function, the author function is divided into the following six types of projects, namely:..


(1) Bessel (Bessel) function. .


(2) numeric conversion functions between different number systems ...


(3) function to filter data. .


(4) weights and measures conversion functions ...


(5) Integration with related functions. .


(6) to handle the complex functions ...


For each of these projects the following functions are introduced. .


1, Bezier (Bessel) function ...


Bessel (Bessel) function is a special function as a function of the most widely used in theoretical physics, applied mathematics, atmospheric science, and have a wide range of radio and other engineering applications. In excel in providing a total of four functions, namely: BESSELI, BESSELJ, BESSELK, BESSELY. .


Syntax: function (x,n) where x values for the parameter, n is a function of the order. If n is not an integer, are truncated to integers. Demand notes is, if x is nonnumeric, Bezier (Bessel) function returns the error value #VALUE!. If n is nonnumeric, Bezier (Bessel) function returns the error value #VALUE!. If n<0, then the Bezier (Bessel) function returns the NUM! error value..


2, the number of systems in different numerical conversion functions. .


Excel engineering functions provided in binary, octal, decimal and hexadecimal numeric conversion functions between ...


Name of the function of such projects is very easy to remember, just remember that binary as BIN, octal for the OCT, the decimal for the DEC, to hex HEX. Remember the function name and then the middle digit 2 can easily remember these values convert the function. For example, if you need to convert a binary number to decimal, the application of the function in front of BIN, the middle of a 2, followed by the DEC, together, this function is BIN2DEC. .


Simple list:..


. .


Figure 2.


The syntax of such form of transfer function value is also very easy to remember. .


For example, different binary values will be converted to decimal syntax is: function (number), where Number is a certain binary number to convert ...


In another example, different decimal value is converted to the syntax of other binary form: function (number, places) where Number is the number to be converted. Places for the number of characters to be used. When you need to return to pre-zero value is particularly useful places. .


DELTA 3, used to filter the data functions and GESTEP..


(1) to test the function of the two values are equal DELTA. .


DELTA to test whether two values are equal. If number1=number2, it returns 1, otherwise 0. You can use this function to filter a set of data, for example, by sum for a several DELTA functions, you can calculate the equivalent data on the number of. This function is also called a Kronecker Delta function ...


Grammatical form of DELTA (number1, number2) as the first parameter which Number1, Number2 as the second parameter. If omitted, assumed Number2 value is zero. If number1 or number2 non-numeric, the function returns the error value DELTA # VALUE!. .


(2) you can filter the data function GESTEP..


Filter the data using GESTEP function. If the Number greater than or equal step, returns 1, otherwise returns 0. For example, multiple functions GESTEP by calculating the return value of the data set can detect more than a certain threshold number of data. .


Syntax: GESTEP (number,step) Number for test values. Step thresholds. If you omit the step, GESTEP function assumes that it is zero. To note is that if either parameter is non-numeric, GESTEP returns an error value of the function #VALUE!..


(3) The function of test scores as an example the use of statistics. .


Case study: mathematical simulation of a college exam, are scheduled. Evaluation programme for the sake of performance proposed by more than 90 min of candidates who ...


Here we use GEStep function to do the statistics, the first results of the candidates for each mark. More than 90 points marked as 1 and 0 otherwise, then all the candidates on the summary tag, you can calculate how many people have more than 90 points. .


　。.


Figure 3. .


Results 1th, Annie, for example, results for 98, Super-90 min. Formula:..


= GESTEP (C4, 90). .


4, weights and measures conversion functions to CONVERT ...


CONVERT function to convert numbers from one system to another metric measurement system. .


Syntax for CONVERT (number,from_unit,To_unit) in from_units units where Number is the value needs to be translated. From_unit is a numeric number. To_unit is the result of ...


CONVERT function, the parameters in the from_unit and to_unit the text of the Schedule to the value received. .


Weight and quality ...


From_unit or to_unit. .


Energy ...


From_unit or to_unit. .


G..


"G". .


J..


"J". .


Geepound ...


"Sg". .


ERG ...


"E". .


Pound (avoirdupois weight).


"Lbm". .


Thermodynamics of cards ...


"C". .


U (atomic mass units).


"U". .


IT card..


"Cal". .


Ounce (avoirdupois weight).


"Ozm". .


Electron Volt ...


"EV". .


Distance from ...


From_unit or to_unit. .


Horsepower-hours ...


"HPh". .


M..


"M". .


Gigawatt-hour ...


"Wh". .


Official ...


"Mi". .


Foot pounds ...


"Flb". .


The sea ...


"Nmi". .


BTU。.


"BTU". .


Inches ...


"In". .


Power ...


From_unit or to_unit. .


FT..


"Ft". .


HP..


"HP". .


Code..


`. .


W..


"W". .


El..


"Ang". .


Magnetic ...


From_unit or to_unit. .


Pick-up trucks (one-seventy second inches).


"Pica". .


Tesla ...


"T". .


Time ...


From_unit or to_unit. .


Gauss..


"Ga". .


Years ...


"Yr". .


Temperature ...


From_unit or to_unit. .


Day ...


"Day". .


C..


"C". .


Hours ...


"Hr". .


Fahrenheit ...


"F". .


Minutes ...


"Mn". .


Kelvin, degree ...


"K". .


Sec..


"Sec". .


Liquid measure ...


From_unit or to_unit. .


Pressure ...


From_unit or to_unit. .


Teaspoon ...


"Tsp". .


HR..


"Pa". .


Spoon ...


"Tbs". .


Atmospheric pressure ...


"Atm". .


Fluid ounce..


"Oz". .


Mm Hg..


"MmHg". .


Cup ...


"Cup". .


..


From_unit or to_unit. .


U。. S。. Pint ...


"Pt". .


Newton ...


"N". .


U。. K。. Pint ...


"Uk_pt". .


Dyne ...


"Dyn". .


Quart ...


"Qt". .


Pound-force ...


"Lbf". .


Gallon ...


"Gal". .


　。.


. .


L..


"L". .


5, with respect to the integral operation function ERF and ERFC..


ERF error function to return the integral between the upper and lower limits. .


Its syntax is: ERF (Lower_limit,Upper_limit), Lower_limit ERF function points lower. Upper_limit is integral upper limit of the ERF function. If omitted, defaults to zero ...


ERFC to return from x to ∞ (infinity) integral function of the error function ERF. The syntax is:. .


ERFC (x) where x is the lower bound of the ERF function integrated in..


6, with the complex operations related functions. .


Remember the afternoon school learned? do you remember when finding the complex modulus of a complex number calculations, such as? Excel engineering functions available in a variety of functions associated with the complex operation, you can use to verify the correctness of the result of the operation. What are the functions associated with complex operation, you can see in the attached table. Here are ways to use simple examples to demonstrate the function. Notes in the project did some functions prefixed with IM functions? these are the functions associated with the complex operation ...


For example, are known to complex 5 +12 i, please use the function to solve the complex conjugate of complex, real coefficients and imaginary coefficients, modulus and so on. .


　。.


Figure 4. .


Appendix: Excel engineering functions ...


Function name. .


Function description ...


Grammatical forms. .


BESSELI。.


Return the modified Bessel function value, it pure imaginary argument Bessel function values when computing the same. .


BESSELI(x,n)。.


BESSELJ. .


Returns the Bessel function ...


BESSELJ (x, n). .


BESSELK。.


Return the modified Bessel function value, it pure imaginary argument Bessel function values when computing the same. .


BESSELK(x,n)。.


BESSELY. .


Returns the Bessel function values, also called the Weber or Neumann function ...


BESSELY (x, n). .


BIN2DEC。.


The binary number to decimal number. .


BIN2DEC(number)。.


BIN2HEX. .


Converts a binary number to hexadecimal..


BIN2HEX (number, places). .


BIN2OCT。.


The binary number to octal number. .


BIN2OCT(number,places)。.


COMPLEX. .


Real coefficient and coefficient of false converts complex numbers in x + yi or x + yj its forms ...


COMPLEX (real_num, i_num, suffix). .


CONVERT。.


The numbers from one system to another metric measurement system. .


CONVERT(number,from_unit,to_unit)。.


DEC2BIN. .


Converts a decimal to binary number ...


DEC2BIN (number, places). .


DEC2HEX。.


Converts a decimal number to hexadecimal number. .


DEC2HEX(number,places)。.


DEC2OCT. .


Converts a decimal number to an octal number..


DEC2OCT (number, places). .


DELTA。.


Test two values are equal. If number1 = number2, returns 1, otherwise returns 0. .


DELTA(number1,number2)。.


ERF. .


Returns the error function between the upper and lower limits of integration ...


ERF (lower_limit, upper_limit). .


ERFC。.


Back from x to ∞ (infinity) function integral error function ERF. .


ERFC(x)。.


GESTEP. .


If a Number greater than or equal to step, returns 1, otherwise 0. Use this function to filter data..


GESTEP (number, step). .


HEX2BIN。.


A hexadecimal number to binary number. .


HEX2BIN(number,places)。.


HEX2DEC. .


Converts a hexadecimal number to decimal number..


HEX2DEC (number). .


HEX2OCT。.


A hexadecimal number to octal number. .


HEX2OCT(number,places)。.


IMABS. .


Returns the absolute value of a complex number in x + yi or x + yj text format (mold).


IMABS (inumber). .


IMAGINARY。.


Back to x + yi or x + yj text format of the imaginary coefficient. .


IMAGINARY(inumber)。.


IMARGUMENT. .


Returns the angle, in radians ...


IMARGUMENT (inumber). .


IMCONJUGATE。.


Back to x + yi or x + yj text format complex conjugate of the complex. .


IMCONJUGATE(inumber)。.


IMCOS. .


Returns the cosine of a complex number in x + yi or x + yj text format..


IMCOS (inumber). .


IMDIV。.


Back to x + yi or x + yj text format quotient of two complex numbers. .


IMDIV(inumber1,inumber2)。.


IMEXP. .


Return to the index of a complex number in x + yi or x + yj text format..


IMEXP (inumber). .


IMLN。.


Back to x + yi or x + yj text format of the complex natural logarithm. .


IMLN(inumber)。.


IMLOG10. .


Returns the common logarithm of a complex number in x + yi or x + yj text format (with 10 as base).


IMLOG10 (inumber). .


IMLOG2。.


Back to x + yi or x + yj text format of the plural to the logarithm base 2. .


IMLOG2(inumber)。.


IMPOWER. .


Returns a complex number in x + yi or x + yj text format n power ...


IMPOWER (inumber, number). .


IMPRODUCT。.


Back to x + yi or x + yj text format of the 2-29 complex product. .


IMPRODUCT(inumber1,inumber2,…)。.


IMREAL. .


Returns a complex number in x + yi or x + yj text format of the real coefficient..


IMREAL (inumber). .


IMSIN。.


Back to x + yi or x + yj text format of the complex sine value. .


IMSIN(inumber)。.


IMSQRT. .


Returns a complex number in x + yi or x + yj text format the square root of a..


IMSQRT (inumber). .


IMSUB。.


Back to x + yi or x + yj text format the difference between the two complex numbers. .


IMSUB(inumber1,inumber2)。.


IMSUM. .


Returns x + yi or x + yj text format of two or more complex numbers and..


IMSUM (inumber1, inumber2, ...). .


OCT2BIN。.


Octal numbers will be converted to a binary number. .


OCT2BIN(number,places)。.


OCT2DEC. .


Converts an octal number to decimal number..


OCT2DEC (number). .


OCT2HEX。.


The octal number to hexadecimal number. .


OCT2HEX(number,places)。.


[Dvnews_page = excel function application of the financial function] as the statistical functions, engineering functions, as in excel also provides a number of financial functions. Financial functions can be general financial terms, such as determining loan payments, future value or net present value of the investment and the value of bonds or coupons. These financial functions can be divided into four categories: investment calculation functions, depreciation calculation functions, calculation functions reimbursement rates, bonds and other financial functions. For the financial analysis which provides a great convenience. Using these functions do not have to understand advanced financial knowledge, just fill in the variable value on it.。.


In introducing specific financial functions, we first look at the financial function to the common parameters:. .


Future value (fv) – all payments after the value of the investments or loans..


During the few (nper) - the total investment (or loan) of that of the investment (or loan) the payment of the total. .


The payment (PMT) – an investment or loan payment amounts on a regular basis. Their values remain unchanged in the annuity. Typically, PMT includes principal and interest but no other fees or taxes ...


Present value (pv) - beginning in the investment value of the investment or loan. For example, the present value of the loan principal amount borrowed. .


Interest rate (rate) – an investment or loan interest rate or discount rates ...


Type (type) - Payment to make payments during the interval, such as at the beginning or end of the month, with 0 or 1 indicates. .


Day count basis types (basis)-the day count basis types. Basis is 0 or omitted on behalf of US (NASD) thirty-three hundred and sixtieths, 1 representative of the actual days/actual days 2 represents actual/360, 3 represents the actual/365, per cent on behalf of the European 4 ...


Next, we will illustrate a variety of financial functions of the application. In this article introduces the various types of typical financial functions, financial functions see more details and related books. If the functions described below are not available, return an error value # NAME?, Install and load the "Analysis ToolPak" add-in. To do so:. .


1, in the "Tools" menu, click the "add-ins.".


2, in the "Add-Ins available" list, select "Analysis ToolPak" box, then click "OK." .


Calculation function, investments ...


Function can be divided in terms of investment and the future value of fv is related to the relevant and payment pmt, pv concerned with the present value, and compounded with the period related to the number and types of related functions. .


1, with respect to the future value of FV function – FV, FVSCHEDULE..


2 pmt-related functions and payment-IPMT, ISPMT, PMT, PPMT. .


3, the present value PV-related functions – NPV, PV, XNPV..


4, and calculating the compound interest function-EFFECT, NOMINAL. .


5, with respect to the number of periods function – NPER..


Function in terms of investment, I will focus on the FV, NPV, PMT, PV function. .


(A) seek the future value of an investment FV..


In their daily work and life, we often encounter to calculate the future value of an investment situation, this time calculated using excel Hanshu FV, you can help us carry out some planned, purpose, cost-effective investment. FV function is based on fixed interest rate and equal installments, the future value of an investment return. .


Syntax for FV (rate,nper,pmt,PV,type).. .


For example, if two years after the person need to study a large sum of expenditure plans deposited in early 2000 Yuan a month from now, if the annual interest 2.. 25% monthly interest (the interest rate per month for 2 ... 25%/12), two years after the account of the deposit amount is how much? ...


Formula written as: FV (2. .25% / 12, 24, -2000,0,1). .


　。.


Figure 1. .


(B) seek the net present value NPV of investment ...


NPV function is fixed based on a series of cash flow and all of the discount rate the net present value of an investment return. Net present value of future investment is the period of payments (negative values) and income (positive) sum of the current value. .


Syntax: NPV (rate,value1,value2 and up), and rate for each period and a discount rate, is a fixed value value1,value2, sth Represents parameter value of 1 through 29 expense and revenue, value1,value2, sth Which period must be equal in length, and payment and revenue occurs at the end of the time. Is important to note: NPV in order to use value1,value2, to note the order of cash flows. So make sure the amount entered in the correct order of payments and receipts.. Ignore the blank array or reference cells, logical values, text, and the error value. .


For example, suppose you open a electrical equipment dealership. Initial investment of $ 200,000, and hoped that in the next five years the annual income is $ 20,000, is $ 40,000, respectively, is $ 50,000, is $ 80,000 and $ 120,000. Assumed discount rate is 8% per year (equivalent to the rate of general inflation rate or competitive investment), the net present value of an investment formula is:..


= NPV (A2, A4: A8) + A3. .


In the example above, the first investment of USD 200,000 in the v parameter does not contain because the payment occurs in the first phase of the beginning of the period. Assumption of the electrical shop is open until the sixth year, decorated facade, is estimated to pay $ 40,000, net present value of an investment is six years after the Bookstore:..


= NPV (A2, A4: A8, A9) + A3. .


If the payment occurs at the end of early stage investments, the net present value of an investment formula is:..


= NPV (A2, A3: A8). .


　。.


Figure 2. .


(C) seeking loan amortization amount of PMT..


PMT function is based on fixed interest rate and equal installments, the return of each investment or loan payments. PMT function can be calculated as repayment of a loan, requiring payment of a certain period end, the time required to pay the repayment amount is what we usually call "installment." For example, by purchase loans or other loans, the repayment of the amount of each can be calculated. .


Its syntax forms for: PMT (rate,nper,PV,fv,type) which, rate for all period interest rate, is a fixed value, nper for total investment (or loan) period, is the items investment (or loan) of payment period total, PV for are value, or series future payment current value of cumulative and, also called principal, FV for future value, or in last once payment Hou hope get of cash balances, if omitted FV, is assumptions its value for zero (such as, a pen loan of future value is for zero), type for 0 or 1, to specifies all period of payment time is in period early also is final. If type is omitted, it is assumed that the value is zero ...


For example, take 10 months to pay the annual interest rate of 8% of the ? 10,000 monthly support amount of the loan:. .


PMT (8%/12,10,10000) evaluates to:-$ 1,037.03.


(D) Find the present value of an investment PV. .


PV function is used to calculate the present value of an investment. Present value of annuities is the sum of the value of future anniversary gold now. If the current value is greater than the value of your investment payback, the proceeds of this investment is ...


The syntax is: PV (rate, nper, pmt, fv, type) Rate for the period in which the interest rate. Nper the total investment (or loan) of that of the investment (or loan) the payment of the total. Pmt to be paid for the amount of its value remained unchanged during the entire annuity. Pmt includes principal and interest are usually, but not including other fees and taxes. Fv is the future value, or want to attain after the last payment of a cash balance, if you omit the fv, its value is assumed to be zero (the future value of a loan that is zero). Type of payment for the designated period of time is due. .


For example, suppose you want to purchase an insurance annuity, the insurance can be at the end of the monthly returns over the next 20 years is $ 600. This annuity purchase cost of 80,000, assuming the return on investment is 8%. So the present value of annuities:..


PV (0. .08/12, 12 * 20,600,0) evaluates to: ? -71,732. .58. .


A negative value indicates that this is a lump sum payment, that is, spending cash flow. Annuities ($-71,732.. 58) of the present value is less than the actual payments ($ 80,000). Therefore, it is not a cost-effective investment ...


. .


Figure 3.


Second, the depreciation function. .


Depreciation calculation functions mainly includes AMORDEGRC, AMORLINC, DB, DDB, SLN, and SYD and the VDB. These functions are used to calculate the depreciation of assets, but uses different calculation methods. Here, for a specific calculation formula will not go into, specific selection which depreciation method, then the units shall be treated as the case may be..


Third, the repayment rate functions. .


Reimbursement rate function is mainly used for the calculation of internal rate of return, including the IRR, MIRR, RATE and XIRR functions ...


(A) function returns the internal rate of return-IRR. .


IRR function to return a number that represents a set of internal rate of return of cash flows. The cash flow does not have to be balanced, but as an annuity, which must occur at fixed intervals, such as monthly or yearly. Recycling rate of the internal rate of return for investments that includes regular payments (negative values) and income (positive values).


. .00001%, If the function IRR after 20 iterations, the results have not yet found, it returns the error value # NUM! In most cases, does not require the calculation of IRR for the function to provide guess values if omitted guess, assuming it is 0. .1 (10%). If the function returns an error value of the IRR # NUM! , Or the results are not close to the expected value, you can guess for a value to try again. .


For example, if you want to run a clothing store, expected investment of $ 110,000, and is expected to net income for the next five years is: is $ 15,000, is $ 21,000, is $ 28,000, is $ 36,000 and $ 45,000. Seek out investment in two years, four years respectively, and internal rate of return after five years of ...


. .


Figure 4.


In the worksheet B1: B6 input data "function.. Xls" below, calculate the investment's internal rate of return after four years of IRR (B1: B5) is -3. .27%; Calculate the investment's internal rate of return after five years IRR (B1: B6) is 8. .35%; Calculated internal rate of return after two years must be included in the function guess, the IRR (B1: B3, -10%) to -48. .96%. .


(B) the RATE function to calculate the actual profit of an investment ...


In economic life, often to assess the current operation of an investment, or a new business status quo. For example, a contractor recommended that you lend him 30,000 yuan, construction funds for public works, and you agree to pay 9,000 yuan every year, paying a total of five years, as a minimum return of the loan. So how do you go to making this investment? How do I know this investment rate of return? Reimbursement for such periodic payment or a completed investment can quickly use the RATE function to calculate the actual profit. The syntax of the form RATE (nper, pmt, pv, fv, type, guess). .


The steps are as follows: ...


1, select the cells to store data in accordance with the similar method to this cell is designated as "percentage" of the format. .


RATE 2, insert function, open the "paste function" dialog box.


3, in the "Paste Function" dialog box, in the "Nper" Enter the pay period 5 (years), in the "Pmt" enter 7000 (the amount of annual return), in the "Pv" enter -30,000 (investment amount) . The formula is = RATE (5,9000, -30000). .


4, after the calculation result is 15.24%. This is the actual profit every year this investment, you can determine this value if the profit is satisfactory, or decided to invest in other projects, or renegotiation of the annual return on ...


Fourth, bonds and other financial functions. .


Bonds and other financial functions can be divided into functions that calculate principal, interest, and interest payments on time-related functions, yields of interest rate-related functions, and revision of time-related functions, with respect to the Securities and the securities prices that their functions ...


1, calculate the principal, interest, function-CUMPRINC, ACCRINT, ACCRINTM, CUMIPMT, COUPNUM. .


2, interest payments on time-related functions – COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPPCD..


3, with the interest rate yield on the function-INTRATE, ODDFYIELD, ODDLYIELD, TBILLEQ, TBILLPRICE, TBILLYIELD, YIELD, YIELDDISC, YIELDMAT. .


4, with the amendments relating to the period – DURATION, MDURATION function ...


5, and securities-related functions-DISC, ODDFPRICE, ODDLPRICE, PRICE, PRICEDISC, PRICEMAT, RECEIVED. .


6, with the price of a security-related functions – DOLLARDE, DOLLARFR..


In bonds and other financial functions, the author focuses on the function ACCRINT, CUMPRINC, DISC. .


(A) calculating the accrued interest for periodic servicing portfolio of ACCRINT function ...


ACCRINT function can return regularly accrued interest interest securities. .


ACCRINT its grammatical forms (issue,first_interest,settlement,rate,par,frequency,basis).


One issue for the securities of the issue date, first_interest value date for the securities, settlement of securities transactions for the day, that is the issue date, the securities sold to the purchaser's date, rate for the portfolio The annual coupon rate, par for the face value of securities, if omitted par, par function ACCRINT will automatically be set to ? 1000, frequency of interest for the number, basis for the type of day count basis. .


For example, a treasury transactions: release date is March 1, 2008; value date is August 31, 2008; the settlement date is May 1, 2008, coupon rate is 10 ... 0%, par value is $ 1,000; according to the semi-annual coupon; day count basis is thirty-three hundred and sixtieths, then the accrued interest for: ...


. .


Figure 5.


(B) Find the principal amount CUMPRINC. .


CUMPRINC function is used to return a sum of money in a given amount St Trojan and repayment of principal during the en. CUMPRINC its grammatical forms (rate,nper,PV,Start_period,end_period,type) where the rate is the interest rate, Nper is the total number of payment periods, PV is the present value, Start_period is calculated in the first phase, number starting with 1 count of payments, End_period is calculated in a end, type for payment types..


For example, a mortgage loan transaction as follows: interest rate of 9. .00%; Period of 30 years; the present value of ? 125,000. Known from the above conditions can be calculated: r = 9. .00% / 12 = 0. .0075, Np = 30 * 12 = 360. .


　。.


Figure 6. .


The loan is repaid in the second half of the total principal amount (7th to 12th): =CUMPRINC (A2/12,A3*12,A4,7,12,0) evaluates to:-436.. 568,194.


Repayment of loan principal in the first month is: = CUMPRINC (A2/12, A3 * 12, A4, 1,1,0) The results are as follows: -68. .27827118. .


(C) seeking security's discount rate DISC ...


DISC function returns the discount rate securities. .


Its syntax forms for DISC (settlement,maturity,PR,redemption,basis) which settlement for portfolio of sold for day, is in release day zhihou, portfolio sold purchase who of date, maturity for portfolio of to date, due day is portfolio effective period due Shi of date, PR for par value for "is $ 100" of portfolio of price, redemption for par value for "is $ 100" of portfolio of settlement price, basis for day count benchmark type..


For example: a bond transaction is as follows: Closing Date of 99 years on March 18, maturity 99 years on August 7, price is ? 48. .834, Settlement price is ? 52, day count basis is Actual / 360. Then the bond discount rate: DISC ("99/3/18", "99/8/7", 48. .834,52,2) Calculated as follows: 0. .154355363. .


The name of the function ...


Function description. .


Grammatical forms..


ACCRINT. .


Returns the accrued interest of regular servicing portfolio ...


ACCRINT (issue, first_interest, settlement, rate, par, frequency, basis). .


ACCRINTM。.


One-time payment of interest due to return accrued interest securities. .


ACCRINTM(issue,maturity,rate, par,basis)。.


AMORDEGRC. .


Returns the depreciation for each accounting period. This function is for France accounting system providing ...


AMORDEGRC (cost, date_purchased, first_period, salvage, period, rate, basis). .


AMORLINC。.


Back to the depreciation of the value of each accounting period, the function for the French accounting system. .


AMORLINC(cost,date_purchased, first_period,salvage,period, rate,basis)。.


COUPDAYBS. .


Returns the current coupon period of days as of the date of ...


COUPDAYBS (settlement, maturity, frequency, basis). .


COUPDAYS。.


Closing Date where the interest return on the number of days. .


COUPDAYS(settlement,maturity, frequency, basis)。.


COUPDAYSNC. .


Returns the days between the settlement date to the next coupon date..


COUPDAYSNC (settlement, maturity, frequency, basis). .


COUPNCD。.


Back to the Closing Date after the date of the next interest payment date. .


COUPNCD(settlement,maturity, frequency, basis)。.


COUPNUM. .


Returns the interest payable between the settlement date and maturity date number, rounded up to the nearest integer..


COUPNUM (settlement, maturity, frequency, basis). .


COUPPCD。.


Back to the previous trading days before the date of interest payment date. .


COUPPCD(settlement,maturity, frequency, basis)。.


CUMIPMT. .


Return for a loan during a given period of start-period to end-period cumulative interest amount of the reimbursement of ...


CUMIPMT (rate, nper, pv, start_period, end_period, type). .


CUMPRINC。.


Back a loan in a given start-period to the end-period repayment of the principal amount of the cumulative period. .


CUMPRINC(rate,nper,pv,start_period, end_period,type)。.


DB. .


Use the fixed-declining balance method, calculate depreciation of an asset in a given period ...


DB (cost, salvage, life, period, month). .


DDB。.


Using the double declining balance method or other specified method to calculate an asset in a given period of depreciation. .


DDB(cost,salvage,life,period,factor)。.


DISC. .


Returns the discount rate portfolio ...


DISC (settlement, maturity, pr, redemption, basis). .


DOLLARDE。.


Fraction of the price will be converted to decimal representation by price, such as securities prices, converted to a decimal number. .


DOLLARDE(fractional_dollar, fraction)。.


DOLLARFR. .


Decimal price converts fractional price. Such as securities prices, converted score digital ...


DOLLARFR (decimal_dollar, fraction). .


DURATION。.


Back to the regular face value of $ 100 Fixed interest securities period. Period cash flow is defined as the present value of a weighted average of prices for the bonds used to measure the sensitivity of yield changes. .


DURATION(settlement,maturity, coupon yld,frequency,basis)。.


EFFECT. .


Given the nominal annual interest rates and compounding periods of the year, calculate the actual annual interest rates ...


EFFECT (nominal_rate, npery). .


FV。.


Based on fixed interest rate and equal installments, the future value of an investment return. .


FV(rate,nper,pmt,pv,type)。.


FVSCHEDULE. .


Based on a series of compound interest returns the future value of the principal sum. Function FVSCHDULE is used to calculate an investment in the future value of the variable or adjustable interest rate ...


FVSCHEDULE (principal, schedule). .


INTRATE。.


Back to one-time interest rate securities. .


INTRATE(settlement,maturity, investment,redemption,basis)。.


IPMT. .


Based on constant payments and a fixed interest rate, return investment or loans in a given time on a regular basis in the interest payment for a..


IPMT (rate, per, nper, pv, fv, type). .


IRR。.


Returns the value of cash flows on behalf of a group of internal rate of return. .


IRR(values,guess)。.


ISPMT. .


Calculation of interest for a specific period of an investment to pay ...


ISPMT (rate, per, nper, pv). .


MDURATION。.


Back to $ 100 face value of the Macauley modified duration of the securities. .


MDURATION(settlement,maturity, coupon,yld,frequency,basis)。.


MIRR. .


Returns a continuous cash flows during the period of the modified internal rate of return ...


MIRR (values, finance_rate, reinvest_rate). .


NOMINAL。.


Real interest rates based on a given number of installments and annual compound interest, nominal interest rate of return. .


NOMINAL(effect_rate,npery)。.


NPER. .


Based on constant payments and a fixed interest rate, return an investment (or loan) of the total number of ...


NPER (rate, pmt, pv, fv, type). .


NPV。.


By using the discount rate and a series of future payments (negative values) and income (positive), the net present value of an investment return. .


NPV(rate,value1,value2, …)。.


ODDFPRICE. .


Returns the first value of the coupon payment date is not fixed portfolio price $100..


ODDFPRICE (settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, basis). .


ODDFYIELD。.


First Interest Payment Date is not fixed return securities (long-term or short-term) rate of return. .


ODDFYIELD(settlement,maturity, issue,first_coupon,rate,pr, redemption, frequency,basis)。.


ODDLPRICE. .


Returns the last coupon is not a fixed value $100 portfolio (short or long) price ...


ODDLPRICE (settlement, maturity, last_interest, rate, yld, redemption, frequency, basis). .


ODDLYIELD。.


Final Interest Payment Date is not fixed return securities (long-term or short-term) rate of return. .


ODDLYIELD(settlement,maturity, last_interest,rate,pr,redemption, frequency,basis)。.


PMT. .


Based on constant payments and a fixed interest rate, and returns the amount of each payment of loan ...


PMT (rate, nper, pv, fv, type). .


PPMT。.


Based on fixed interest rate and equal installments, the return to invest in a given period of principal repayments. .


PPMT(rate,per,nper,pv,fv,type)。.


PRICE. .


Returns the value of regular servicing portfolio of $100 price ...


PRICE (settlement, maturity, rate, yld, redemption, frequency, basis). .


PRICEDISC。.


Back to the nominal value of the discounted issue price of securities of $ 100. .


PRICEDISC(settlement,maturity, discount,redemption,basis)。.


PRICEMAT. .


Returns the value of expired coupon $100 portfolio prices ...


PRICEMAT (settlement, maturity, issue, rate, yld, basis). .


PV。.


Back to the present value of the investment. A series of future payments the present value of the accumulation and the current value. For example, the borrower's borrowing items is the present value of the loan lending party. .


PV(rate,nper,pmt,fv,type)。.


RATE. .


Return periods of annuity interest rates. Function RATE calculated from the iterative method, and no possible solution or have more than one solution ...


RATE (nper, pmt, pv, fv, type, guess). .


RECEIVED。.


One-time interest in the securities back to recover the amount due. .


RECEIVED(settlement,maturity, investment,discount,basis)。.


SLN. .


During the return of an asset in a linear depreciation in value..


SLN (cost, salvage, life). .


SYD。.


Back to life of an asset at a specified sum of depreciation method to calculate depreciation period. .


SYD(cost,salvage,life,per)。.


TBILLEQ. .


Returns the equivalent rate of return of t-...


TBILLEQ (settlement, maturity, discount). .


TBILLPRICE。.


Treasury returned $ 100 face value price. .


TBILLPRICE(settlement,maturity, discount)。.


TBILLYIELD. .


Returns the yields on Treasury bills ...


TBILLYIELD (settlement, maturity, pr). .


VDB。.


Using the double declining balance method or other specified method and return any of the specified period (including the part of the period) depreciation of assets. VDB function on behalf of the variable declining balance method. .


VDB(cost,salvage,life,start_period, end_period,factor,no_switch)。.


XIRR. .


Returns the internal rate of return of a group of cash flow, cash flow does not necessarily occur at regular intervals. To calculate the internal rate of return of a group of regular cash flow, use the IRR function ...


XIRR (values, dates, guess). .


XNPV。.


Return a net present value of cash flows, these cash flows do not necessarily occur on a regular basis. To calculate a net present value of cash flow on a regular basis, please use the function NPV. .


XNPV(rate,values,dates)。.


YIELD. .


Returns the periodic servicing portfolio yields, YIELD function is used to calculate bond yields ...


YIELD (settlement, maturity, rate, pr, redemption, frequency, basis). .


YIELDDISC。.


Back to the securities issued at a discount rate of annual income. .


YIELDDISC(settlement,maturity, pr,redemption,basis)。.


YIELDMAT. .


Returns the annual yield of the portfolio of due interest rate ...


YIELDMAT (settlement, maturity, issue, rate, pr, basis). .


[Dvnews_page=Excel information function function] there is a class of functions in the Excel function, which is designed to return certain specified information, such as a cell or range, such as the content, format, number of cells, these functions we call information functions. In this article, we will use this kind of summary function to understand, while for some commonly used functions and parameters of the application examples ...


First, for the return information about cell formatting, location, or function of the information content of CELL. .


CELL function is used to return a reference region upper-left cell of the formatting, location, or contents, and so on. Its grammatical forms, CELL (info_type,reference) where Info_type is a text value that specifies the type of cell information you want. Reference which indicates that you want to get information about the cell. If omitted, the information specified in info_type is returned to the last cell ...


First look at, info_type possible values and the corresponding results. .


Type ...


Info_type. .


Returns a result ...


Position. .


"address"。.


The first cell reference in the reference, the text type. .


"col"。.


Column cell reference standard. .


"row"。.


Reference cell line number. .


"filename"。.


Contains the reference file name (including full path), text type. If the reference sheet that contains the target has not been saved, then return empty text .


The format..


"Color". .


If the cell is in the negative values are displayed in different colors, to 1, otherwise it returns 0.


"Format". .


Corresponding to the number format of cells in different text values. The following table lists the text values in different formats. If negative values are displayed in different colors in the cell, the text returned by the value added at the end of the "-"; if positive or all the cells in the cell are bracketed, are returned at the end of the text value "()" ...


"Parentheses". .


If positive or all the cells in the cell is parenthetical, is 1, otherwise it returns 0.


"Prefix". .


Unlike cells of "sign prefix" that corresponds to the text value. If unit grid text left on align, is returns single quotes ('); if unit grid text right on align, is returns double quotes ("); if unit grid text Center, is returns insert character (^); if unit grid text ends on align, is returns anti-slash (\); If is other situation, is returns empty this (" ")..


"Protect". .


If the cell is not locked, and is 0 if the cell is locked, otherwise 1.


"Type". .


With data type that corresponds to the text in the cell values. If the cell is empty, it returns "b". If the cell contains a text constant, returns "l"; If the cell contains other content, the return of "v.".


"Width". .


After rounding the width of the cell. Width of the column width to the default font size of a character as a unit..


Content. .


"contents"。.


Upper-left cell reference values: not the formula. .


Look again when the info_type is "format", and the reference is set with a built-in number format of cells, CELL returns the text value of the function ...


. .


Figure 1.


CELL function is mainly used for compatibility with other spreadsheet programs. In the following example, we learn how to use the CELL function to get the cell format, location and content. .


An example: want to know that the cell A1 through B4 areas such as information, such as line numbers, column widths, the contents of a cell ...


. .


Figure 2.


Second, the region used to calculate the number of blank cells COUNTBLANK. .


COUNTBLANK is used to calculate the specified number of blank cells in a range of cells. The syntax forms for COUNTBLANK (range) one number Range for which you want to count the blank cells. To be aware of is that ...


Even if the cell contains the return value is an empty text ("") formula, the cell will be counted, but cells with zero values are excluded. .


In the example as shown in the figure, cell B3 contains the formula =IF (A3<30, "", A3), but the formula to calculate the returned value is an empty text "", so the cell is evaluated to an empty cell. While the value of cell A3 is zero cell, excluding blank cells ...


Compare Figure 3-A and the results of Figure 3-B difference, the difference is that Figure 3-B formula in cell B3 is = IF (A3> 30, "", A3), the calculated results returned 0, therefore not counted as an empty cell. .


　。.


Figure 3A. .


　。.


Figure 3B. .


Third, the functions that correspond to the type of error number returned ERROR ... TYPE。.


ERROR. . TYPE returns the corresponding error in Microsoft excel in a number of values, or, if no error is returned # N / A. Syntax form ERROR. . TYPE (error_val) which Error_val as the label needs to be an error value. Although error_val value for the actual error, but it is usually a cell reference, and this cell contains a formula to be detected. The following is the error_val function returns the result. .


　。.


Figure 4. .


Would you know IF logic functions? can be used in a function IF ERROR ... TYPE detection error value, and returns a text string (for example, messaging) to replace error values. See examples ...


. .


Figure 5.


Fourth, return information about the current operating environment, a function of INFO. .


INFO function is used to return information about the current operating environment. The syntax forms for INFO (type_text) where Type_text is text, specify the type of information to be returned. Type_text results returned by a specific reference is made to the following table ...


Type_text. .


Return ...


"Directory". .


The path to the current directory or folder ...


"Memavail". .


Free memory space, in bytes..


"Memused". .


Data memory footprint ...


"Numfile". .


Number of active worksheets in the workbook that you open ...


"Origin". .


A1-style of an absolute reference, text, prefixed with "$A:", and Lotus 1-2-3 3. x compatible version. The basis of the current scroll position, and returns the window is visible in the far right corner of the cell ...


"Osversion". .


The current version number of the operating system, text values are ...


"Recalc". .


Current recalculation mode and return to "automatic" or "manual.".


"Release". .


Microsoft Excel version number, a text value..


"System". .


Operating name: Macintosh= "Mac" Windows= "pcdos"..


"Totmem". .


All memory space, including memory already occupied space, in bytes..


Example of how to use the INFO function informed the current operating environment information. .


　。.


Figure 6. .


Five specially designed for testing numerical functions – or a reference type IS class function ...


IS class function is used to test the value or reference type worksheet function in excel in a total of nine of these functions. To several functions, including:. .


(1) of ISBLANK when the value is empty, it returns TRUE ...


(2) ISERR except if the value of # N / A error value anything other than, returns TRUE. .


(3) the ISERROR if the value is any error value, it returns TRUE ...


(4) ISLOGICAL If the value is a logical value, it returns TRUE. .


(5) the ISNA error value if the value is #N/A, it returns TRUE ...


(6) ISNONTEXT If the value is not text, it returns TRUE. .


(7) the ISNUMBER if the value is numeric, it returns TRUE ...


(8) ISREF If the value is quoted, it returns TRUE. .


(9) the ISTEXT if the value is text, it returns TRUE ...


These functions, summarized as IS class functions, you can test the type of numerical values based on parameter returns TRUE or FALSE. For example, if the value is a reference to the blank cells, the function returns a logical value ISBLANK TRUE, otherwise returns FALSE. The syntax for the function name (value) where Value is the need for testing values. IS classes for different functions are: blank (empty cell), error values, logical values, text, numbers, reference values or parameters for the name of any of the above references. .


It should be noted is the parameter value IS class function is not converted. For example, in most other functions that require digital, the text value "19" is converted to the number 19. However in formula ISNUMBER ("19"), "19" is not by text values are converted into other types of values, ISNUMBER function returns FALSE. IS class function is mainly used for testing a formula to calculate the result. When it is when used in conjunction with the IF function, you can provide a method for locating errors in formulas ...


. .


Figure 7.


Six, and laboratory parameters of the function ISEVEN parity with ISODD. .


ISODD parity in order to test parameters and ISEVEN function. ISEVEN is when an even number of parameters, return TRUE, otherwise returns FALSE. ISODD is exactly the opposite, if an odd number of parameters, and return TRUE, otherwise return FALSE ...


On the use of these two functions refer to the specific examples. .


　。.


Figure 8. .


Seven, returns the converted value is worth after functions n..


Function N to return a numeric value after conversion. The syntax of the form N (value) where Value is the value to be converted. Function N can be transformed into the values listed in the following table:. .


　。.


Figure 9. .


Is important to note: normally you do not have to use a function in the formula n, because Excel will automatically convert the values as needed. This function is provided for compatibility with other spreadsheet programs. Microsoft Excel stores dates as serial numbers that can be used to calculate. By default, January 1, 1900 is serial number 1, January 1, 2008 is serial number 39,448, this is because it is 39,448 days away from January 1, 1900.. .


About specific uses may be obtained from the following example function n more detailed picture..


. .


Figure 10.


VIII, returns the error value # N / A a function of NA. .


#N/A NA function is used to return an error value. Error value #N/A means "unable to get valid values". Recommend NA sign blank cells. Enter #N/A in the cell that has no content, you can avoid accidentally as a result of the empty cells, including problems (if the formula refers to a cell that contains #N/A, #N/A returns an error value).


The syntax of the form NA (). .


Required note must include the parentheses following a function name, otherwise, Microsoft Excel does not recognize the function. You can also type directly in the cell #N/A. NA function is provided for compatibility with other spreadsheet programs ...


IX, the return value of the type of function TYPE. .


Function TYPE is used to return a numeric type. When a function is calculated when the result depends on the type of values in a particular cell, you can use the function TYPE. Its syntax TYPE (value) where Value can be any Microsoft Excel values such as numbers, text, logical values, and so on ...


To note is acceptable when using a function of different types of data (such as function and function ARGUMENT INPUT), the function TYPE useful. TYPE can use the function to find the function or formula that is what type of data returned. TYPE can be used to determine whether a cell containing the formula. TYPE only determines the results, display or value type. If a value is a cell reference, it refers to another cell contains a formula, this formula will return the results of TYPE value type. .


　。.


Figure 11. .


Above, our overview of the information functions of Excel functions. Information function is used to return certain specified information, such as a cell or range, such as the content, format, number of cells, such as a kind of function. In practice, often used with the logical functions such as IF to reach information on cell determines ...


[Dvnews_page = excel function application of the database function] in Microsoft excel worksheet contains a number of functions, they are stored in the data used to list or analyze data in the database, these functions are collectively referred to as database functions Dfunctions. .


A common feature, function, ...


This class of functions have some common characteristics:. .


(1), each function has three arguments: a database and the field and criteria. These parameters to a function used by the worksheet range ...


(2) In addition to GETPIVOTDATA function, the other twelve functions beginning with the letter D. .


(3) If you remove the letter d, you can find most of the database function is already seen in other types of Excel functions. For example, DAVERAGE d removed, is averaging function AVERAGE ...


Second, the list of database functions. .


Database contained in the Excel function and its application:..


. .


Figure 1.


Third, the database functions of the parameters of meaning. .


Because the database functions have the same three parameters, so I will first introduce the functions of several parameters. Then in a concrete example to illustrate the application of database functions ...


The syntax of the form of such function is the function name (database, field, criteria). .


Database for the range of cells that form the list or database. Databases are lists that contain a set of related data, which contains information about the records, and as a field that contains the data. List package mouth the first row of labels for each column ...


Field used for the specified function of the data columns. Inventory data in the column must have a sign in the first line item. Field can be text, that is a sign of quotation marks at both ends of items, such as "using the number of years" or "production"; In addition, Field data can also represent data columns list the number of positions: 1, said first column, 2 for second column and so on. .


Criteria for a group of cells that contain the given condition. You can specify any range for the criteria argument, as long as it contains at least one column label and to set conditions for cell below the column labels ...


Four examples. .


1, for example: some orchards of fruit trees of height, using a number of years, yields and profits of statistical data tables as shown in the figure, ...


. .


Figure 2.


Want to calculate. .


(1) there are a number of Apple trees between the tree 10~16 feet high ...


(2) the maximum profit of apple and pear value is. .


(3) the height is greater than 10 feet of Apple trees, what is the minimum profit ...


(4) the total profit of apple trees. .


(5) the height is greater than 10 feet above the average yield of Apple trees ...


(6) of all trees orchard average number of years. .


(7) order estimation of standard deviation in the yield of Apple and pear trees, and a true estimation of standard deviation, variance, actual variance..


2, the solution steps. .


(1) create a blank workbook or worksheet, the data entry in Excel ...


. .


Figure 3.


(2) The first question to be solved, how many kinds of apple trees 10 to 16 feet high between the query thus established a high degree of between 10 and 16. .


　。.


Figure 4. .


(3) using the DCOUNT function solve to calculate the number of cells that satisfy the condition type of Apple tree 10~16 feet in height. The formula is: DCOUNT (A4:E10, "age", B1:G2), find the condition is satisfied there is an Apple tree ...


(4) The second question for the sake of maximum profit of apple and pear value, then create a query - pear, as shown. .


　。.


Figure 5. .


(5) using the DMAX function finds the maximum profit of Apple and pear trees, and similarly can use the DMIN function calculating the height is greater than 10 feet minimum profits of Apple trees. Formula:..


= DMAX (A4: E10, "Profit", A1: A3) calculated the maximum profit of apple and pear 105. .


=DMIN (A4:E10, "profit", A1:B2) finds the height is greater than 10 feet minimum profits of Apple trees to 75.


(6) can be calculated using the function DSUM the total profit of all apple trees. .


　The formula is: =DSUM (A4:E10, "profit", A1:A2), total profit to 225.


(7) other similar functions may be asked to solve in detail the formula as shown. .


　。.


Figure 6. .


Five, to be aware of local ...


1, you can specify any criteria for the parameter region, as long as it contains at least one column below the flag and column flag to set the conditions for the cell. .


For example, if A1:A2 contains the column label in the A1 "trees", and A2 contains the name of Apple trees, can be named Apple tree species in this region, then the database name as a parameter to a function you can use the criteria ...


2, although the criteria range can be anywhere in the worksheet, but do not place the criteria range below the data list. If using the "Data" menu "Record" command to add information in the data list, the new information will be added in the data on the first line below the list. If the data is non-empty rows in the list below, Microsoft excel can not add new information. .


3, determine the criteria range does not overlap the list ...


4, to entire columns to the database, you need a column in the criteria range below enter a blank line flag. .


Six, on condition of ...


In the above example, we briefly introduce the conditions for the establishment of the region, where the details of the conditions in the excel data using advanced screening methods. .


1, the concept ...


Condition is the limit specified in a query or filter the result set the conditions for which records are included. For example, the example above, the selection of conditions, "height" field value is greater than 10 record: height> 10. .


List refers to a series of worksheet rows that contain the relevant data, for example, database or a set of customer name and phone number of the invoice. The first row has column labels in the list ...


2, the basic requirements to establish conditions for the region. .


(1) data that can be used as a criteria range on the list insert at least three blank rows ...


(2) The criteria range must have column labels. .


(3) Please ensure that the criteria values and keep at least one blank line between list ...


As in the above example A1: F3 is a condition of area acts out of which the first signs, such as species, height. .


3, filter criteria to establish ...


Sign the following line in the column, type the conditions to be matched. All entries to the beginning of the text will be filtered. For example, if you type the text "Dav" as a condition, Microsoft excel will find "Davolio", "David" and "Davis". If only match the specified text, type the formula = "= text", where "text" is the need to find the text. If you want to find some of the characters the same but the other characters in the text value is not necessarily the same, you can use wildcards. excel wildcard characters supported:. .


　。.


Figure 7. .


4, the establishment of several different criteria ...


(1) single row that has more than one condition. .


If a column has two or more filter criteria, you can direct rows from top to bottom, type in various conditions. For example, a sample criteria range shown above "tree" column contains "Apple tree" or "pear"..


(2) multiple columns with a single condition. .


To find a single condition is met in two or more columns of data, please enter all the conditions in the same row of the criteria range. For example, the following criteria area displays all of the examples in "high" greater than 10 and in the column "yield" larger than 10 rows of data..


. .


Figure 8.


(3) a column or another column that has a single condition. .


To find your meet one condition or another condition of the data, please enter the criteria in the criteria range does not peer. For example, the criteria area displays all of the above example in the "high" column in the data row is greater than 10 ...


(4) has two sets of two columns on one of the conditions. .


To find the two conditions are met (conditional includes criteria for multiple columns in each group), one of the rows of data, please type them in rows. For example, the following criteria area displays all the "species" column contains "Apple tree" and "height" is greater than 10 rows of data, but also shows "cherry tree", "age" of rows is greater than 10 ...


. .


Figure 9.


(5) one has two or more conditions. .


To find rows that meet more than two sets of criteria, please use the same standard includes more columns. For example, a sample criteria range shown above is between 10 and 16 height ...


(6) as a condition of the formula results. .


Excel formulas (formula: a cell in the range of values, cell references, names, or a combination of operators, you can generate a new value. Formulas are always with an equal sign (=). ) As a condition of its results. When you use a formula to create the conditions, do not mark the column label for a condition, condition tag should be blank, or use the list of non-column label tag. For example, in the following areas appear in column c, its value is greater than C7:C10 the average of the range of cells. =C7>AVERAGE($C$7:$C$10) 。.


Note that the formula must be used as a condition of relative references to refer to the column labels (for example, "high"), or the first recorded reference to the corresponding fields. All other references in the formula must be absolute reference and the formula to calculate the results must be TRUE or FALSE. In this example, the formula, C7 reference list of the first data record (line 7) of the field (column C). .


Course can be used in the formula column labels instead of a relative reference to a cell or a range name. When Microsoft Excel displays an error value in the cell that contains the condition name? Or #VALUE! , You can ignore these errors, because they do not affect filtering of the list ...


In addition Microsoft excel data in the calculation is not case sensitive. .


Refer to the same cells on the worksheet or range of cells ...


On multiple sheets of the same cell or range of cells (range: two or more cells on the grid. Area of cells can be adjacent or nonadjacent.) Reference is known as three-dimensional reference ( three references: a workbook on across two or more work in the area of the reference tables.). .


Click to enter the function cell ...


Type = (equal sign), then enter the function name, and then type the left parenthesis. .


Functions that can be used in a three dimensional reference ...


SUM - sum of the values;. .


AVERAGE – calculate the average of values (mathematical methods).


AVERAGEA - calculated values (including strings and logical values) of the mean (mathematical methods). .


COUNT — calculate the number of cells that contain numbers ...


COUNTA - calculate the number of non-blank cells. .


MAX – finds largest value in a set of values ...


MAXA - Find the maximum value in a set of values (including strings and logical values.) .


MIN — find the minimum value in a set of values ...


MINA - Find the minimum value in a set of values (including text and logical values.) .


PRODUCT – multiplied by the number ...


STDEV - estimates based on a sample standard deviation. .


STDEVA estimation based on a sample – (includes text and logical values) of standard deviation ...


STDEVP - calculated based on the total sample given the standard deviation. .


STDEVPA – calculating samples (including text and logical values) the overall standard deviation ...


VAR - variance of sample estimates. .


VARA – estimation of a sample (including text and logical values) of the variance in..


VARP - calculated based on the given sample of the overall variance. .


VARPA – calculating samples (including text and logical values) variance in..


Click the need to reference the first sheet tab. .


Hold down Shift and click need to reference the last worksheet tab..


Need to reference the selected cell or range of cells. .


Complete the formula, and then press Enter..


Many of my friends are using Excel editing, processing various data reports, often in the course of that Excel automatically calculates results with the results of our manual calculation error will appear. Excel worksheet for example, have B2 = 16. .18, C2 = 12. .69, B2 and C2 will be multiplied by the sum of 0. .11, The result "rounded" with two decimal places, and then multiplying the result by 3. .12, And then "rounded" with two decimal places, Excel's calculation is "9. .91", And our results by hand calculation "9. .92."。.


[Blocked ads] when we set the table, in the "Format Cells" window set the "scale" can only cell value "display" rounded, and can not be stored on the "value" rounding . In other words, the display and the actual storage of content (ie, the content in operation) is not exactly the same. Therefore, the resulting Excel calculation errors and the actual needs of the culprit is, it is the display cell data content and the content of the inconsistency involved in the calculation. So how do we avoid this error is it? . .


　　Method is simple, one is to use the Round function rounds the decimal precision, which is in the form: round (number,Num_digits), where "number" as need the rounded figures disjunction formula, Num_digits specifies the number of rounds. For the problem described in this article, we need only in cell D2, type "=Round ((B2+C2) *0 ... 11,2)", "E2" cell type "=Round (D2*3 ... 12,2)".. .


Select this option to avoid error ...