# Aggregate functions in Excel 2003 2007 2010 2013 Basic Advanced

Tag: Aggregate functions in Excel 2003 2007 2010 2013 Basic Advanced full keygen, full key, full crack, portable, full serial key, lastest, 32 & 64 bit, for windows 7 8 10, free download

n fact, some functions are not much use excel, you only need to master the

Here is a list of functions from basic to excel Advanced is divided into many different types that can be easily applied to the appropriate areas.

ABS (number): Calculated absolute value of a number (the absolute value of a number is always positive).

CEILING (number,-significance): Rounding up some (distant out of 0) to the nearest multiple of-significance.

COMBIN (number, number_chosen): Returns the number of combinations is selected from a number of elements. Often used to determine the total number of groups from a number of elements.

EVEN (number) Rounds a number to the nearest integer

EXP (number): Calculate the base e exponential of a number

FACT (number) : Calculate the factorial of a number

FACTDOUBLE (number): Calculate the factorial of a number of secondary

FLOOR (number,-significance): Rounding down some to the nearest multiple of

GCD (number1, number2, ...): Find Convention the largest share of the

INT (number) Rounds a number down to the nearest integer

LCM (number1, number2, ...): Find least common multiple of the number of

LN (number): Calculate the natural logarithm of a number

LOG (number): Calculate the logarithm of a number

LOG10 (number): Calculate the logarithm base 10 of a number

MDETERM (array): Calculate the determinant of a matrix

MINVERSE (array): Find the inverse of a matrix matrix

MMULT (array1, array2): Calculates two matrices

MOD (number, divisor): Take the remainder of a division.

MROUND (number, multiple): Rounds a number to a multiple of some

MULTINOMIAL (number1 , number2, ...): Calculate the ratio of the factorial factorial sum and the number of

oDD (number) Rounds a number to the nearest odd integer

PI (): Returns the number Pi

POWER (number , power): Calculate the power of some of

the PRODUCT (number1, number2, ...): Calculates the number

Quotient (numberator, denominator): Get the integer part of a division

RAND (): Returns a random number between 0 and 1

RANDBETWEEN (bottom, top): Returns a random number between one option

ROMAN (number, form): Move some (Arabic) about the Roman numerical format options

ROUND (number , NUM_DIGITS): Rounds a number to the specifications

ROUNDDOWN (number, NUM_DIGITS): Rounding down some under the designation

ROUNDUP (number, NUM_DIGITS): Rounding up some under the designation

SERIESSUM (x, n, m, coefficients): Calculate the total power of a sequence of

sIGN (number): Returns the sign (algebra) of a number

SQRT (number): Calculate the square root of a number

SQRTPI (number): Calculate the square root of some are multiplied by Pi

SUBTOTAL (function_num, REF1, ref2, ...): calculated for a group of children in a list according to the specified calculation

sUM (number1, number2, ...): calculate the sum of No.

SUMIF (range, criteria, sum_range): Calculates the sum of cells that a condition specified

SUMIFS (sum_range, criteria_range1, Criteria1, criteria_range2, criteria2, ...): Calculates the sum of cells that specify conditions [/ URL]

SUMPRODUCT (array1, array2, ...): Calculate the sum of the product of the elements in the data array

SUMSQ (number1, number2, ...): Calculate the sum of squares of the number

SUMX2MY2 (array_x, array_y): Count the effect of squares of the elements in the two arrays value

SUMX2PY2 (array_x, array_y): Calculates the sum of the sum of squares of the elements in the array values

SUMXMY2 (array_x, array_y): Calculates the sum of the square of the effect of elements in the array value

TRUNC (number, NUM_DIGITS): Get the integer part of a number (without rounding)

ASC (text): Converts the characters double- byte to single-byte characters.

BATHTEXT (number): Translation of the word (in Thailand), and then add the suffix "Bath" in the back.

CHAR (number): Convert a code in ANSI code (with domain values from 1-255) to the corresponding character.

CLEAN (text): remove all non-printable characters in the string

cODE (text): Returns the code of the first character string text

CONCATENATE (text1, Text2, ...): Connects multiple strings into a string

dOLLAR (number, decimals): Converts a number into a form of currency (US dollars), which together with the thousands separator, and can do round at will.

EXACT (text1, Text2): Comparing two strings. If the same, then returns TRUE, if different, it returns FALSE. There are case sensitive and lowercase.

FIND (find_text, WITHIN_TEXT, start_num): Find the starting position of a substring (find_text) within a sequence (WITHIN_TEXT), calculated according to the first character

FIXED (number, decimals, no_commas ): Converts a number to text (text), whether or not accompanied by the thousands separator, and can be rounded according to

the LEFT (text, NUM_CHARS): Returns the first character or more parties left of a string, according to the specified number

LEN (text): Count the number of characters in a string

LOWER (text): Change all the characters in a text string to lowercase

MID (text, start_num, NUM_CHARS) : Returns one or more consecutive characters within a string, starting at a given position

PROPER (text): change the first character in the string to uppercase, and change the remaining characters into print often

REPLACE (OLD_TEXT, start_num, NUM_CHARS, NEW_TEXT): Replace part of a string with another string, with the number of characters specified

REPT (text, times): Repeat a string with a given number of times

RIGHT (text, NUM_CHARS): Returns one or more characters from the right of a string, according to the specified number

SEARCH (find_text, WITHIN_TEXT, start_num): Find the starting position of a substring (find_text) within a string (WITHIN_TEXT), calculated according to the first character

SUBSTITUTE (text, OLD_TEXT, NEW_TEXT, instance_num): Replace this string with another string

T (value): Returns a string if the reference value is the string, in turn, will return empty string

tEXT (value, format_text): Converts a number into text format (text) format specified

TRIM (text): Remove all the useless white space in a text string, leaving only the spaces used as a space between two letters

UPPER (text): Change all the characters in the string to capitals

VALUE (text): Converts a string into a number

ADDRESS (ROW_NUM, COLUMN_NUM, abs_num, a1, sheet_text): Create a text box at the address, according to the number of lines and columns are provided indicators

aREAS (reference): Returns the number of references in a reference. Each reference area can be a discrete cell or a continuous range in the spreadsheet

CHOOSE (num, value1, value2, ...): Choose a value in a list

COLUMN (reference): Returns the number the column of the first cell in the upper left corner of the reference range

cOLUMNS (reference): Returns the column number of the reference range

GETPIVOTDATA (data_field, pivot_table, field1, item1, field2, ITEM2, ...): Returns data stored in a PivotTable. Can use GETPIVOTDATA to retrieve data from a report summarizing the PivotTable, provided that the data to see the summary of the reports.

HLOOKUP (lookup_value, table_array, row_index_num, range_lookup): Detecting a column containing the price looking for value in the first row (top) of a data table, if found, will look to in this column, and will get the value of the specified goods before

HYPERLINK (link_location, FRIENDLY_NAME): Used to create a connection, a hyperlink

INDEX (reference, ROW_NUM, COLUMN_NUM, area_num): Find a value in a table (or array) if known its position in the table (or array), which is based on row number and column number

INDIRECT (ref_text, a1): Returns a reference from string. Reference is returned immediately to display their content - INDIRECT function can also be used when you want to change the reference to a cell within a formula without changing the formula that

LOOKUP (lookup_value, lookup_vector, result_vector): vectors - Search in a row or a column, if found will return the value of the cell in the same position on the line (or column) is indicated

LOOKUP (lookup_value, array): Type ARRAY - Search row (or column), the first of an array value, if found will return the value of the cell in the same position on the line (or column) last in the array

MATCH (lookup_value, lookup_array, match_type): returns the position of a value in a range of values

OFFSET (reference, rows, cols, height, width) Returns a reference to a particular area, starting from a single cell, or range, to a specified distance

rOW (reference): Returns the line number of the first cell in the upper left corner of the reference range

ROWS (reference): Returns the row number of the reference range

TRANSPOSE (array): Transfer a data area along the horizontal pivot and vice versa (always be entered as an array formula)

VLOOKUP (lookup_value, table_array, col_index_num, range_lookup): Detect a row (row) contains the lookup value in the first column (left) of a data table, if found, will look to in this restaurant, and will take the value specified in the previous column.

aND (logical1, logical2, ...): Returns TRUE if all stakeholders number is TRUE, returns FALSE if one or more argument is FALSE

IF (logical_test, value_if_true, value_if_false): Used to check the conditions and formula values

IFERROR (value, value_if_error): If an error occurs, .. . doing something

NOT (logical): reverse the value of the arguments

oR (logical1, logical2, ...): returns TRUE if one or more argument is TRUE, returns FALSE if all the arguments are FALSE

FALSE () and TRUE (): You can enter either TRUE FALSE directly into the formula, Excel will understand that it is a valid expression TRUE FALSE or

without resorting to the syntax of these functions to

cELL (info_type, reference): for information on the format, location or contents of the box in the upper left corner in a reference

ERROR.TYPE (error_val): Returns a number corresponding to one of the error values in Excel or returns the #NA! if no errors

INFO (info_text): Returns the information of the operating environment while working with MS Excel

IS ... (value): Includes functions: ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER , ISREF, ISTEXT. Used to check data in Excel, All returns TRUE if testing shows the true and FALSE if testing shows the wrong

ISEVEN (number): Returns TRUE if the number is an even number, FALSE if the number is odd

ISODD (number): Returns TRUE if the number is odd, FALSE if the number is an even number

N (value): Convert a value of some

NA (value): Used to generate a # N / a! to mark the blank cells to avoid the problem before using some functions of Excel. When referring to the cell function is highlighted, will return the # N / A!

TYPE (value): Returns the type of value to lookup

DATE (year . month, day): Returns the number represents a specific date. If the cell format was General before the function was entered, the result will be expressed in the form of date.

DATEDIF (START_DAY, END_DAY, unit): Used to calculate the number of days, months, years difference between two time values.

DATEVALUE (DATE_TEXT): Returns the number of days sequentially shown by DATE_TEXT (convert a rich text string date into a date value can be calculated).

dAY (SERIAL_NUMBER): Returns the day part of a date value, is represented by the sequence. The result returned is an integer from 1 to 31.

DAYS360 (start_date, end_date, method): Returns the number of days between two dates based on a year of 360 days (12 months, 30 days per month) to use for financial calculations.

EDATE (start_date, months): Returns the number sequence represents a milestone day from the time before and how this timeline a specified number of months.

EOMONTH (start_date, months ): Returns the sequential number represents the last day of a given month from the timeline given and how this timeline a specified number of months.

hOUR (SERIAL_NUMBER): Returns the hour part of a value time. The result returned is an integer from 0 to 23.

MINUTE (SERIAL_NUMBER): Returns the minutes portion of a time value. The result returned is an integer from 0 to 59.

MONTH (SERIAL_NUMBER): Returns the month part of a date value, is represented by the sequence. The result returned is an integer from 1 to 12.

NETWORKDAYS (start_date, end_date, holidays): Returns all working days in a period between start_date and end_date, excluding weekends and holidays (holidays).

nOW (): Returns the sequential number of the current date and time shown. If the cell format was General before the function was entered, the result will be expressed in the form of date and time moments.

SECOND (SERIAL_NUMBER): Returns the seconds portion of a time value. The result returned is an integer from 0 to 59.

TIME (hour, minute, second): Returns the decimal part of a time value (from 0 to less than 1). If the cell format was General before the function was entered, the result will be expressed in the form of hours of seconds.

TIMEVALUE (TIME_TEXT): Returns the decimal part of the value of time (from 0 to less than 1) represented by TIME_TEXT (convert a rich text string into a value of time period can be calculated.)

TODAY (): Returns the number of sequential current date shown. If the cell format was General before the function was entered, the result will be expressed in the form of date.

WEEKDAY (SERIAL_NUMBER, return_type): Returns the day of the week corresponding to the date provided. The result returned is an integer from 1 to 7.

WEEKNUM (SERIAL_NUMBER, return_type): Returns a few weeks of that year.

WORKDAY (START_DAY, days, holidays): Returns a sequential number representing the number working days, can be started before or after work, and subtract the weekends and holidays (if any) in that period.

yEAR (SERIAL_NUMBER): Returns a fifth of the value date , is represented by the sequence. The result returned is an integer from 1900 to 9999

to be returned (start_date, end_date, basis): Returns the ratio of a period in a year.

statistical functions can be divided 3 small groups follows: group function on Statistics, the group of probability distribution function, and the function group of correlation and linear regression

1. TEAM TITLES IN STATISTICS

AVEDEV (number1, number2, ...): On average absolute deviation of data points from their mean. Often used as a measure of the variability of the data set

AVERAGE (number1, number2, ...): Calculate the average of

AVERAGEA (number1, number2, ...): Calculate the average of the values, including both logical values

AVERAGEIF (range, Criteria1): Calculate the average of the values in an array in a condition

AVERAGEIFS (range, Criteria1, criteria2, ...): Calculate the average of the values in a under many conditions array

COUNT (value1, value2, ...): Count the number of cells in the list

COUNTA (value1, value2, ...): Count the number of cells that contain the value (not empty) list

COUNTBLANK (range ): Counts the blank cells in a region of

COUNTIF (range, criteria): Count the number of cells that a given condition within a range

COUNTIFS (range1, Criteria1, RANGE2, criteria2, ...): Count the number of cells that many conditions previous

DEVSQ (number1, number2, ...): Calculate the squared deviations of data points from their sample mean, and the squared plus that again.

FREQUENCY (data_array, bins_array): Determine how much value often appear within a range of values, and then returns a vertical array of numbers. Always use this function in an array formula

GEOMEAN (number1, number2, ...): Returns the average of a sequence of positive numbers. Often used to calculate the average growth rate, which compounding has been given variable rates ...

HARMEAN (number1, number2, ...): Returns the harmonic mean (arithmetic average of the inverse) of the No.

KURT (number1, number2, ...): Calculates the tip of the collection of data, indicates how sharp or flat relative level of a distribution compared with normal distribution

lARGE (array, k): Returns the largest value most k in a set of data

MAX (number1, number2, ...): Returns the largest value of a set of values

MAXA (number1, number2, ...): Returns the largest value of a collection values, including text and logical values

mEDIAN (number1, number2, ...): Calculate the median of numbers.

MIN (number1, number2, ...): Returns the minimum value of a value collection

MINA (number1, number2, ...): Returns the smallest value of a set of values, including text and logical values

MODE (number1, number2, ...): Returns the value most appeared in an array value

pERCENTILE (array, k): Find the kth percentile of values in a data array

PERCENTRANK (array, x-significance): Returns the rank (relative position) of a value in a data array, the array is the percentage of data that

PERMUT (number, number_chosen): Returns the permutations of the object.

qUARTILE (array, quart): Calculated quartile of the data set. Often used in survey data to divide into groups gathered ...

RANK (number, ref, order): Calculate the rank of a number in the list of the

SKEW (number1, number2, ...): Returns deviation of the distribution, describes the asymmetry of distribution around its average value

sMALL (array, k): Returns the k-th smallest value in a set of

STDEV (number1, number2, ...) : Estimate the standard deviation based on the sample

STDEVA (value1, value2, ...): Estimation of the standard deviation based on the sample, including logical values

STDEVP (number1, number2, ...): Calculated Overall standard deviation according gathers

STDEVPA (value1, value2, ...): Calculate the standard deviation as a whole set, including text and logical values

VAR (number1, number2, ...): Returns variance based on a sample

VARA (value1, value2, ...): Returns the variance based on a sample, including text and logical values

VARP (number1, number2, ...): Returns the variance based on the entire episode where

VARPA (value1, value2, ...): Returns the variance based on the entire collection, including the logic and text value.

TRIMMEAN (array, percent): on average, the interior of a data set, by type the percentage of the data points at the beginning and at the end of the data set.

BETADIST (x, alpha, beta, A,: Returns the value of the distribution function for determining the exact density accrued interest beta.

BETAINV (probability, alpha, beta, A,: Returns the inverse of the function that calculates the probability density distribution of accumulated beta.

BINOMDIST (number_s, Trials, probability_s, cumulative): Returns the probability of the successful trials of the binary distribution.

CHIDIST (x, Degrees_freedom): Returns the probability of a chi-squared distribution.

CHIINV (probability, Degrees_freedom): Returns the inverse of the probability distribution of the expenditure side -squared.

CHITEST (actual_range, expected_range): Returns the probability value from the chi-squared distribution and the corresponding number of degrees of freedom.

cONFIDENCE (alpha, standard_dev, size): Calculate the confidence interval for a theoretical expectations

CRITBINOM (Trials, probability_s, alpha): Returns the smallest value for the cumulative binomial distribution is greater than or equal to the standard value. Often used to ensure quality applications ...

EXPONDIST (x, lambda, cumulative): Calculate the exponential distribution. Often used to simulate the time between the events ...

FDIST (x, degrees_freedom1, degrees_freedom2): Calculate the probability distribution F. Often used to find out if the two sets of data there are different degrees or not ...

FINV (probability, degrees_freedom1, degrees_freedom2): Calculates the inverse of the F probability distribution is often used to compare the variability in two data sets

FTEST (array1, array2): Returns the results of a test used to determine F. Often whether two samples have different variances or not ...

FISHER (x): Returns the Fisher transformation at x. Often used to test the hypothesis based on the correlation ...

FISHERINV (y): Calculates the inverse Fisher transformation. Often used to analyze the correlation between the array data ...

GAMMADIST (x, alpha, beta, cumulative): Returns the gamma cumulative distribution. Can be used to study skewed distribution

GAMMAINV (probability, alpha, beta): Returns the inverse of the gamma cumulative distribution.

GAMMLN (x): Calculate the natural logarithm of the gamma function

HYPGEOMDIST (number1, number2, .. .): Returns the hypergeometric distribution (probability of a certain number of successes ...)

LOGINV (probability, mean, standard_dev): Calculates the inverse of the lognormal cumulative distribution function of x (LOGNORMDIST)

LOGNORMDIST (x, mean, standard_dev): Returns the cumulative lognormal distribution of x, in which the natural logarithm of x is normally distributed with parameters mean and standard_dev.

NEGBINOMDIST (number_f, number_s, probability_s): Returns the negative binomial distribution (returns the probability that there will be failures number_f number_s times before success, when the constant probability of a success is probability_s times)

NORMDIST (x, mean, standard_dev, cumulative): returns the normal distribution ( normal distribution). Commonly used in statistics, including hypothesis testing

NORMINV (probability, mean, standard_dev): Calculates the inverse normal cumulative distribution

NORMSDIST (z): Returns the cumulative distribution function normal (standard normal cumulative distribution function), is distributed with the mean value is zero (0) and standard deviation is 1

NORMSINV (probability): Calculates the inverse cumulative distribution function of the normal

POISSON (x, mean, cumulative): Returns the Poisson distribution. Often used to estimate the number of events that will occur in a certain time period

PROB (x_range, prob_range, LOWER_LIMIT, UPPER_LIMIT): Calculate the probability of the value in the range between two limits

STANDARDIZE (x, mean, standard_dev ): Returns a normalized value from the distribution indicated by the mean and standard_dev

TDIST (x, Degrees_freedom, tails): Returns the probability of Student distribution (distribution t), where x is the value calculated from the t and be used to calculate the probability.

TINV (probability, Degrees_freedom): Returns the Student's t distribution.

TTEST (array1, array2, tails, type): calculated probability associated with the Student test.

WEIBULL (x, alpha, beta, cumulative): Returns the Weibull distribution. Often used in reliability analysis, such as average life calculation of a device.

ZTEST (array, x, sigma): Returns the probability of a part of the test z.

CORREL (array1, array2): calculate the correlation coefficient between two arrays to determine the relationship of two features

COVAR (array1, array2): Calculates the deviation of each pair of data points, then calculate the average of the volume of which

FORECAST (x, known_y's, known_x's): calculated or predict a future value by using existing values, by means of linear regression

GROWTH (known_y's, known_x's, new_x's, const ): Calculate the expected growth exponential, using the data available.

INTERCEPT (known_y's, known_x's): Find the intersection of a straight line with the y axis using the x and y values given

LINEST (known_y's, known_x's, const, stats): As the statistics for a line by using the method of least squares (least squares) to calculate a straight line that best fits the data and then returns an array describing path scale. Always use this function in an array formula.

LOGEST (known_y's, known_x's, const, stats): Used in the regression analysis. The function will calculate exponential curve fit to the data provided, and then returns an array value that describes the curve. Always use this function in an array formula

PEARSON (array1, array2): Calculate the correlation coefficient Pearson torque area (r), a dimensionless index, in the range of -1 to 1, reflecting the expansion of relations linearly between the two sets of data

RSQ (known_y's, known_x's): calculate the squared correlation coefficient torque achievement Pearson (r), through data points in known_y's and known_x's

sLOPE (known_y's, known_x's): calculate the slope of road linear regression through the data points.

STEYX (known_y's, known_x's): Returns the standard error of the predicted value y for each value x in the regression.

TREND (known_y's, known_x's, new_x's, const): Returns about the trend of linear

general of Management functions Database and List

DAVERAGE (database, field, criteria): calculated the average of the values in a column of the list or the value of a database, according to a specified condition.

DCOUNT (database, field, criteria): Counting cells containing data in a column of the list or the value of a database, according to a specified condition.

DCOUNTA (database, field, criteria): Counting of cells "is not empty" in a column of the list or the value of a database, according to a specified condition.

DGET (database, field, criteria): Extract a value from a column of a list or database, matches the specified conditions.

DMAX (database, field, criteria) : Returns the largest value in a column of a list or a database, according to a specified condition.

DMIN (database, field, criteria): Returns the smallest value in a column of a list hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DRODUCT (database, field, criteria) : Nhân các giá trị trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DSTDEV (database, field, criteria) : Ước lượng độ lệch chuẩn của một tập hợp theo mẫu, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DSTDEVP (database, field, criteria) : Tính độ lệch chuẩn của một tập hợp theo toàn thể các tập hợp, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DSUM (database, field, criteria) : Cộng các số trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DVAR (database, field, criteria) : Ước lượng sự biến thiên của một tập hợp dựa trên một mẫu, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DVARP (database, field , criteria) : Tính toán sự biến thiên của một tập hợp dựa trên toàn thể tập hợp, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

Một số ví dụ về cách dùng Criteria để nhập điều kiện

GETPIVOTDATA (data_field, pivot_table, field1, item1, field2, item2,...) : Trả về dữ liệu được lưu giữ trong báo cáo PivotTable.

IMCOS (inumber): Returns the cosine of a complex number

IMDIV (inumber1, inumber2): Calculated quotient (result of the division) of two complex numbers

IMEXP (inumber): Returns the exponential of a complex number

IMLN (inumber) : Returns logarite complex nature of some

IMLOG10 (inumber): Returns the decimal logarite of a complex number

IMLOG2 (inumber): Returns logarite base 2 of a complex number

IMPOWER (inumber, number): Calculated power of a complex number

IMPRODUCT (inumber1, inumber2, ...): Calculates the number of 2 to 255 complex numbers together

IMREAL (inumber): Returns the real coefficient of a complex number

IMSIN (inumber): Returns the sine of a number complex

IMSQRT (inumber): Returns the square root of a complex number 2

IMSUB (inumber1, inumber2): Calculate the difference of two complex numbers

IMSUM (inumber1, inumber2, ...): Calculate the sum of 2 to 255 complex numbers

OCT2BIN ( number, places) Converts an octal number to binary number

OCT2DEC (number): Converts an octal number to decimal

OCT2HEX (number, places) Converts an octal number to a hexadecimal number

ACCRINT (issue, first_interest, settlement, rate, par, frequency, basis, calc_method): Calculates the accrued interest for a securities pay interest periodically

ACCRINTM (issue, settlement, rate, par, basis): Calculates the accrued interest for securities with interest payment on maturity

AMORDEGRC (cost, date_purchased, first_period, Salvage, period, rate, basis): As per fiscal depreciation in accounting depending on the shelf life of the asset (used in the system according to French accounting)

AMORLINC (cost, date_purchased, first_period, Salvage, period, rate, basis): As per fiscal depreciation accounting (used in the accounting system under French)

COUPDAYBS (settlement, maturity , frequency, basis): Count the number of days since the beginning of the next interest period settlement date

COUPDAYS (settlement, maturity, frequency, basis): Count the number of days in the interest period including the settlement date

COUPDAYSCN (settlement, maturity, frequency, basis ): Calculates the number of days from the settlement date until the next interest

COUPNCD (settlement, maturity, frequency, basis): Returns a number that represents the next interest date after the settlement date since

COUPNUM (settlement, maturity, frequency, basis): Count the number of times the interest payable between settlement date and the maturity date

COUPPCD (settlement, maturity, frequency, basis): returns a number that represents the last interest payment date, first date accounting

CUMIPMT (rate, nper, pv, start_period, end_period, type): Calculate the cumulative income to pay for loans in the period between start_period and end_period

CUMPRINC (rate, nper, pv, start_period, end_period, type): returns the accumulated capital to pay for the loan for the period between start_period and end_period

DB (cost, salvage regimens, life, period, month): Calculate depreciation for an asset using a declining balance method in a fixed rates (fixed-declining balance method) in a defined time period.

DDB (cost, salvage regimens, life, period, factor): Calculated asset depreciation for a method using the double-declining balance (double- declining balance method), or decrease over a certain percentage, for a defined period of time.

DISC (settlement, maturity, pr, redemption, basis): Calculate the discount rate of a security

DOLLARDE (fractional_dollar, fraction ): Convert fractional dollar price to the dollar price in decimal format

DOLLARFR (decimal_dollar, fraction): Convert the decimal value in the form of dollars to the price of the dollar in the form of fractional

DURATION (settlement, maturity, coupon, YLD, frequency, basis): Calculated validity period Macauley based on copper face value $ 100 (validity period is the weighted average current value of the flow of cash and is used as a measure of feedback that changed benefits the net value of the bonds)

EFFECT (nominal_rate, Npery): Calculate the annual real interest rate, anticipated annual nominal interest rate and the total number of payment periods per year compounding

FV (rate, nper, pmt, pv, type): Calculate the value of the investment period based on periodic fixed payments and a fixed interest rate

FVSCHEDULE (principal, schedule): Calculate the value of a term of the initial capital after the application of a series of compound interest (calculated value for an investment period with interest rate changes)

INTRATE (settlement, maturity, investment, redemption, basis): calculated interest rate of investment securities for a whole

IPMT (rate, per, nper , pv, fv, type): returns the interest payment for an investment based on periodic fixed payments and a constant interest rate based on

IRR (values, guess): Calculates the internal rate for a sequence of functions cash flow is represented by the value of

ISPMT (rate, per, nper, pv): Calculate the amount of interest paid in a certain period for a loan with a constant interest rate, after deduction of the amount Original paid for that period.

MDURATION (settlement, maturity, coupon, YLD, frequency, basis): As of deadline Macauley modified for stock-based co-denomination $ 100

MIRR (values, finance_rate, reinvest_rate): Calculated margin business intrinsic interest in a series of cash flow in cycles

nOMINAL (effect_rate, Npery): calculate the annual nominal interest rate, anticipated real interest and compounding periods per year

NPER (rate, pmt, pv, fv, type): Calculate the number of terms to repay investment loans based on each cycle, the amount paid and the fixed rate of return

NPV (rate, value1, value2, ...): Calculate the net present value of an investment using discount rate compares with more maturity payments (negative values) and income (positive values)

ODDFPRICE (settlement, maturity, issue, first_coupon, rate, YLD, redemption, frequency, basis) : Calculate the value per contract face value $ 100 of securities with first semester odd (short or long)

ODDFYIELD (settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, basis): Returns profit of a stock and a time to first interest is odd (short or long)

ODDLPRICE (settlement, maturity, last_interest, rate, YLD, redemption, frequency, basis): calculated value per contract face value $ 100 of stock with coupon interest period end is odd (short or long)

ODDLYIELD (settlement, maturity, last_interest, rate, pr, redemption, frequency, basis): Calculates the yield of securities is odd last period (short-term or long-term)

PMT (rate, nper, pv, fv, type): Count money to pay for loans with interest rate constant and regular pay

PPMT (rate, per, nper, pv, fv, type): calculate payment funds in a given period for an investment, in which the payment is made regularly periodically with a constant interest rate

PRICE (settlement, maturity, rate, YLD, redemption, frequency , basis): Calculate the value of securities on the contract face value $ 100, interest payments cyclical

PRICEDISC (settlement, maturity, discount, redemption, basis): Calculated values on copper denominations of $ 100 of a stock has discounted

PRICEMAT (settlement, maturity, issue, rate, YLD, basis): Calculated values on copper denominations of $ 100 a securities pay interest at maturity

PV (rate, nper, pmt, fv, type): As prices present value of an investment

rATE (nper, pmt, pv, fv, type, guess): Calculated interest rate per period of an annuity

REVEICED (settlement, maturity, investment, discount, basis): Calculate the amount received to maturity investment securities for a whole

SLN (cost, salvage regimens, life): Calculate the cost of depreciation (straight line basis) of an asset for one period

SYD (cost, salvage regimens, life, per ): Calculated amortized, the carrying value of assets in the period for determination of

TBILLEQ (settlement, maturity, discount): Calculates the yield corresponds to bonds for treasury bonds

TBILLPRICE (settlement, maturity, discount): Calculated the value of par value $ 100 for treasuries

TBILLYIELD (settlement, maturity, pr): Calculates the yield for treasury bonds

VDB (cost, salvage regimens, life, start_period, end_period, factor, no_switch): Calculated asset depreciation used in many period

XIRR (values, dates, guess): Calculated inner yields a roving series non-recurring cash

XNPV (rate, values, dates): Calculated rates for a range of mobile net cash periodic

YIELD (settlement, maturity, rate, pr, redemption, frequency, basis): Calculates the yield on securities pay interest periodically

YIELDDISC (settlement, maturity, pr, redemption, basis): Calculate the annual profit the stock has discounted

YIELDMAT (settlement, maturity, issue, rate, pr, basis): Calculated annual profits of securities pay interest at maturity

ACOS (number): returns a radian values range from 0 to pi, the arccosine, or inverse cosine of a number in the range of -1 to 1

ACOSH (number): Returns a radian value, is the inverse hyperbolic cosine-of some greater than or equal to 1

ASIN (number): Returns a value in the period from -Pi radians / 2 to Pi / 2, is the arcsine, or inverse sine of a number in the range of -1 to 1

ASINH (number ): Returns a radian value, is the inverse hyperbolic sine-of some

ATAN (number): Returns a value in the range of -Pi radians / 2 to Pi / 2, is the arctangent, or inverse tangent of some

ATAN2 (x_num, y_num): Returns a value radian range (but not including) from -Pi to Pi, is tangent, or inverse tangent of a point with coordinates x and y

ATANH (number) : Returns a radian value, is the inverse of a tang-hyperbolic range from -1 to 1

COS (number): Returns a radian value, the cosine of a number

COSH (number): Returns a radian value, the hyperbolic cosine of a number-

dEGREES (angle): Convert measurements from radians to degrees angle

rADIANS (angle): Conversion of an angle measurement from degrees to radians

SIN (number): Returns radian is a sine value of some

BIRTH (number): Returns a radian value, is of some hyperbolic sine-

TAN (number): Returns a radian value, is evidence of some

TANH (number): Returning a radian value, is evidence of some hyperbolic-

n fact, some functions are not much use excel, you only need to master the

**that can do different types. However, to calculate optimal, there are many other functions can excel optimal handling as well as the simple**__basic Excel functions__**where you may not know.**__formula excel__Here is a list of functions from basic to excel Advanced is divided into many different types that can be easily applied to the appropriate areas.

Aggregate functions in Excel 2003 2007 2010 2013 Basic Advanced |

**Excel math functions - MATH FUNCTIONS**ABS (number): Calculated absolute value of a number (the absolute value of a number is always positive).

CEILING (number,-significance): Rounding up some (distant out of 0) to the nearest multiple of-significance.

COMBIN (number, number_chosen): Returns the number of combinations is selected from a number of elements. Often used to determine the total number of groups from a number of elements.

EVEN (number) Rounds a number to the nearest integer

EXP (number): Calculate the base e exponential of a number

FACT (number) : Calculate the factorial of a number

FACTDOUBLE (number): Calculate the factorial of a number of secondary

FLOOR (number,-significance): Rounding down some to the nearest multiple of

GCD (number1, number2, ...): Find Convention the largest share of the

INT (number) Rounds a number down to the nearest integer

LCM (number1, number2, ...): Find least common multiple of the number of

LN (number): Calculate the natural logarithm of a number

LOG (number): Calculate the logarithm of a number

LOG10 (number): Calculate the logarithm base 10 of a number

MDETERM (array): Calculate the determinant of a matrix

MINVERSE (array): Find the inverse of a matrix matrix

MMULT (array1, array2): Calculates two matrices

MOD (number, divisor): Take the remainder of a division.

MROUND (number, multiple): Rounds a number to a multiple of some

MULTINOMIAL (number1 , number2, ...): Calculate the ratio of the factorial factorial sum and the number of

oDD (number) Rounds a number to the nearest odd integer

PI (): Returns the number Pi

POWER (number , power): Calculate the power of some of

the PRODUCT (number1, number2, ...): Calculates the number

Quotient (numberator, denominator): Get the integer part of a division

RAND (): Returns a random number between 0 and 1

RANDBETWEEN (bottom, top): Returns a random number between one option

ROMAN (number, form): Move some (Arabic) about the Roman numerical format options

ROUND (number , NUM_DIGITS): Rounds a number to the specifications

ROUNDDOWN (number, NUM_DIGITS): Rounding down some under the designation

ROUNDUP (number, NUM_DIGITS): Rounding up some under the designation

SERIESSUM (x, n, m, coefficients): Calculate the total power of a sequence of

sIGN (number): Returns the sign (algebra) of a number

SQRT (number): Calculate the square root of a number

SQRTPI (number): Calculate the square root of some are multiplied by Pi

SUBTOTAL (function_num, REF1, ref2, ...): calculated for a group of children in a list according to the specified calculation

sUM (number1, number2, ...): calculate the sum of No.

SUMIF (range, criteria, sum_range): Calculates the sum of cells that a condition specified

SUMIFS (sum_range, criteria_range1, Criteria1, criteria_range2, criteria2, ...): Calculates the sum of cells that specify conditions [/ URL]

SUMPRODUCT (array1, array2, ...): Calculate the sum of the product of the elements in the data array

SUMSQ (number1, number2, ...): Calculate the sum of squares of the number

SUMX2MY2 (array_x, array_y): Count the effect of squares of the elements in the two arrays value

SUMX2PY2 (array_x, array_y): Calculates the sum of the sum of squares of the elements in the array values

SUMXMY2 (array_x, array_y): Calculates the sum of the square of the effect of elements in the array value

TRUNC (number, NUM_DIGITS): Get the integer part of a number (without rounding)

**Excel functions handle text and strings - tEXT fUNCTIONS**ASC (text): Converts the characters double- byte to single-byte characters.

BATHTEXT (number): Translation of the word (in Thailand), and then add the suffix "Bath" in the back.

CHAR (number): Convert a code in ANSI code (with domain values from 1-255) to the corresponding character.

CLEAN (text): remove all non-printable characters in the string

cODE (text): Returns the code of the first character string text

CONCATENATE (text1, Text2, ...): Connects multiple strings into a string

dOLLAR (number, decimals): Converts a number into a form of currency (US dollars), which together with the thousands separator, and can do round at will.

EXACT (text1, Text2): Comparing two strings. If the same, then returns TRUE, if different, it returns FALSE. There are case sensitive and lowercase.

FIND (find_text, WITHIN_TEXT, start_num): Find the starting position of a substring (find_text) within a sequence (WITHIN_TEXT), calculated according to the first character

FIXED (number, decimals, no_commas ): Converts a number to text (text), whether or not accompanied by the thousands separator, and can be rounded according to

the LEFT (text, NUM_CHARS): Returns the first character or more parties left of a string, according to the specified number

LEN (text): Count the number of characters in a string

LOWER (text): Change all the characters in a text string to lowercase

MID (text, start_num, NUM_CHARS) : Returns one or more consecutive characters within a string, starting at a given position

PROPER (text): change the first character in the string to uppercase, and change the remaining characters into print often

REPLACE (OLD_TEXT, start_num, NUM_CHARS, NEW_TEXT): Replace part of a string with another string, with the number of characters specified

REPT (text, times): Repeat a string with a given number of times

RIGHT (text, NUM_CHARS): Returns one or more characters from the right of a string, according to the specified number

SEARCH (find_text, WITHIN_TEXT, start_num): Find the starting position of a substring (find_text) within a string (WITHIN_TEXT), calculated according to the first character

SUBSTITUTE (text, OLD_TEXT, NEW_TEXT, instance_num): Replace this string with another string

T (value): Returns a string if the reference value is the string, in turn, will return empty string

tEXT (value, format_text): Converts a number into text format (text) format specified

TRIM (text): Remove all the useless white space in a text string, leaving only the spaces used as a space between two letters

UPPER (text): Change all the characters in the string to capitals

VALUE (text): Converts a string into a number

**SEARCH tHE CONTENT EXCEL aND REFERENCE - LOOKUP fUNCTIONS**ADDRESS (ROW_NUM, COLUMN_NUM, abs_num, a1, sheet_text): Create a text box at the address, according to the number of lines and columns are provided indicators

aREAS (reference): Returns the number of references in a reference. Each reference area can be a discrete cell or a continuous range in the spreadsheet

CHOOSE (num, value1, value2, ...): Choose a value in a list

COLUMN (reference): Returns the number the column of the first cell in the upper left corner of the reference range

cOLUMNS (reference): Returns the column number of the reference range

GETPIVOTDATA (data_field, pivot_table, field1, item1, field2, ITEM2, ...): Returns data stored in a PivotTable. Can use GETPIVOTDATA to retrieve data from a report summarizing the PivotTable, provided that the data to see the summary of the reports.

HLOOKUP (lookup_value, table_array, row_index_num, range_lookup): Detecting a column containing the price looking for value in the first row (top) of a data table, if found, will look to in this column, and will get the value of the specified goods before

HYPERLINK (link_location, FRIENDLY_NAME): Used to create a connection, a hyperlink

INDEX (reference, ROW_NUM, COLUMN_NUM, area_num): Find a value in a table (or array) if known its position in the table (or array), which is based on row number and column number

INDIRECT (ref_text, a1): Returns a reference from string. Reference is returned immediately to display their content - INDIRECT function can also be used when you want to change the reference to a cell within a formula without changing the formula that

LOOKUP (lookup_value, lookup_vector, result_vector): vectors - Search in a row or a column, if found will return the value of the cell in the same position on the line (or column) is indicated

LOOKUP (lookup_value, array): Type ARRAY - Search row (or column), the first of an array value, if found will return the value of the cell in the same position on the line (or column) last in the array

MATCH (lookup_value, lookup_array, match_type): returns the position of a value in a range of values

OFFSET (reference, rows, cols, height, width) Returns a reference to a particular area, starting from a single cell, or range, to a specified distance

rOW (reference): Returns the line number of the first cell in the upper left corner of the reference range

ROWS (reference): Returns the row number of the reference range

TRANSPOSE (array): Transfer a data area along the horizontal pivot and vice versa (always be entered as an array formula)

VLOOKUP (lookup_value, table_array, col_index_num, range_lookup): Detect a row (row) contains the lookup value in the first column (left) of a data table, if found, will look to in this restaurant, and will take the value specified in the previous column.

**tHE CONTENT MANAGEMENT DISCUSSION EXCEL - Logical FUNCTION**aND (logical1, logical2, ...): Returns TRUE if all stakeholders number is TRUE, returns FALSE if one or more argument is FALSE

IF (logical_test, value_if_true, value_if_false): Used to check the conditions and formula values

IFERROR (value, value_if_error): If an error occurs, .. . doing something

NOT (logical): reverse the value of the arguments

oR (logical1, logical2, ...): returns TRUE if one or more argument is TRUE, returns FALSE if all the arguments are FALSE

FALSE () and TRUE (): You can enter either TRUE FALSE directly into the formula, Excel will understand that it is a valid expression TRUE FALSE or

without resorting to the syntax of these functions to

**the functions EXCEL iNFORMATION - iNFORMATION FUNCTIONS**cELL (info_type, reference): for information on the format, location or contents of the box in the upper left corner in a reference

ERROR.TYPE (error_val): Returns a number corresponding to one of the error values in Excel or returns the #NA! if no errors

INFO (info_text): Returns the information of the operating environment while working with MS Excel

IS ... (value): Includes functions: ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER , ISREF, ISTEXT. Used to check data in Excel, All returns TRUE if testing shows the true and FALSE if testing shows the wrong

ISEVEN (number): Returns TRUE if the number is an even number, FALSE if the number is odd

ISODD (number): Returns TRUE if the number is odd, FALSE if the number is an even number

N (value): Convert a value of some

NA (value): Used to generate a # N / a! to mark the blank cells to avoid the problem before using some functions of Excel. When referring to the cell function is highlighted, will return the # N / A!

TYPE (value): Returns the type of value to lookup

**EXCEL DATE OF TITLES AND TIME - DATE AND TIME FUNCTIONS**DATE (year . month, day): Returns the number represents a specific date. If the cell format was General before the function was entered, the result will be expressed in the form of date.

DATEDIF (START_DAY, END_DAY, unit): Used to calculate the number of days, months, years difference between two time values.

DATEVALUE (DATE_TEXT): Returns the number of days sequentially shown by DATE_TEXT (convert a rich text string date into a date value can be calculated).

dAY (SERIAL_NUMBER): Returns the day part of a date value, is represented by the sequence. The result returned is an integer from 1 to 31.

DAYS360 (start_date, end_date, method): Returns the number of days between two dates based on a year of 360 days (12 months, 30 days per month) to use for financial calculations.

EDATE (start_date, months): Returns the number sequence represents a milestone day from the time before and how this timeline a specified number of months.

EOMONTH (start_date, months ): Returns the sequential number represents the last day of a given month from the timeline given and how this timeline a specified number of months.

hOUR (SERIAL_NUMBER): Returns the hour part of a value time. The result returned is an integer from 0 to 23.

MINUTE (SERIAL_NUMBER): Returns the minutes portion of a time value. The result returned is an integer from 0 to 59.

MONTH (SERIAL_NUMBER): Returns the month part of a date value, is represented by the sequence. The result returned is an integer from 1 to 12.

NETWORKDAYS (start_date, end_date, holidays): Returns all working days in a period between start_date and end_date, excluding weekends and holidays (holidays).

nOW (): Returns the sequential number of the current date and time shown. If the cell format was General before the function was entered, the result will be expressed in the form of date and time moments.

SECOND (SERIAL_NUMBER): Returns the seconds portion of a time value. The result returned is an integer from 0 to 59.

TIME (hour, minute, second): Returns the decimal part of a time value (from 0 to less than 1). If the cell format was General before the function was entered, the result will be expressed in the form of hours of seconds.

TIMEVALUE (TIME_TEXT): Returns the decimal part of the value of time (from 0 to less than 1) represented by TIME_TEXT (convert a rich text string into a value of time period can be calculated.)

TODAY (): Returns the number of sequential current date shown. If the cell format was General before the function was entered, the result will be expressed in the form of date.

WEEKDAY (SERIAL_NUMBER, return_type): Returns the day of the week corresponding to the date provided. The result returned is an integer from 1 to 7.

WEEKNUM (SERIAL_NUMBER, return_type): Returns a few weeks of that year.

WORKDAY (START_DAY, days, holidays): Returns a sequential number representing the number working days, can be started before or after work, and subtract the weekends and holidays (if any) in that period.

yEAR (SERIAL_NUMBER): Returns a fifth of the value date , is represented by the sequence. The result returned is an integer from 1900 to 9999

to be returned (start_date, end_date, basis): Returns the ratio of a period in a year.

**THE CONTENT EXCEL STATISTICS - Statistical FUNCTIONS**statistical functions can be divided 3 small groups follows: group function on Statistics, the group of probability distribution function, and the function group of correlation and linear regression

1. TEAM TITLES IN STATISTICS

AVEDEV (number1, number2, ...): On average absolute deviation of data points from their mean. Often used as a measure of the variability of the data set

AVERAGE (number1, number2, ...): Calculate the average of

AVERAGEA (number1, number2, ...): Calculate the average of the values, including both logical values

AVERAGEIF (range, Criteria1): Calculate the average of the values in an array in a condition

AVERAGEIFS (range, Criteria1, criteria2, ...): Calculate the average of the values in a under many conditions array

COUNT (value1, value2, ...): Count the number of cells in the list

COUNTA (value1, value2, ...): Count the number of cells that contain the value (not empty) list

COUNTBLANK (range ): Counts the blank cells in a region of

COUNTIF (range, criteria): Count the number of cells that a given condition within a range

COUNTIFS (range1, Criteria1, RANGE2, criteria2, ...): Count the number of cells that many conditions previous

DEVSQ (number1, number2, ...): Calculate the squared deviations of data points from their sample mean, and the squared plus that again.

FREQUENCY (data_array, bins_array): Determine how much value often appear within a range of values, and then returns a vertical array of numbers. Always use this function in an array formula

GEOMEAN (number1, number2, ...): Returns the average of a sequence of positive numbers. Often used to calculate the average growth rate, which compounding has been given variable rates ...

HARMEAN (number1, number2, ...): Returns the harmonic mean (arithmetic average of the inverse) of the No.

KURT (number1, number2, ...): Calculates the tip of the collection of data, indicates how sharp or flat relative level of a distribution compared with normal distribution

lARGE (array, k): Returns the largest value most k in a set of data

MAX (number1, number2, ...): Returns the largest value of a set of values

MAXA (number1, number2, ...): Returns the largest value of a collection values, including text and logical values

mEDIAN (number1, number2, ...): Calculate the median of numbers.

MIN (number1, number2, ...): Returns the minimum value of a value collection

MINA (number1, number2, ...): Returns the smallest value of a set of values, including text and logical values

MODE (number1, number2, ...): Returns the value most appeared in an array value

pERCENTILE (array, k): Find the kth percentile of values in a data array

PERCENTRANK (array, x-significance): Returns the rank (relative position) of a value in a data array, the array is the percentage of data that

PERMUT (number, number_chosen): Returns the permutations of the object.

qUARTILE (array, quart): Calculated quartile of the data set. Often used in survey data to divide into groups gathered ...

RANK (number, ref, order): Calculate the rank of a number in the list of the

SKEW (number1, number2, ...): Returns deviation of the distribution, describes the asymmetry of distribution around its average value

sMALL (array, k): Returns the k-th smallest value in a set of

STDEV (number1, number2, ...) : Estimate the standard deviation based on the sample

STDEVA (value1, value2, ...): Estimation of the standard deviation based on the sample, including logical values

STDEVP (number1, number2, ...): Calculated Overall standard deviation according gathers

STDEVPA (value1, value2, ...): Calculate the standard deviation as a whole set, including text and logical values

VAR (number1, number2, ...): Returns variance based on a sample

VARA (value1, value2, ...): Returns the variance based on a sample, including text and logical values

VARP (number1, number2, ...): Returns the variance based on the entire episode where

VARPA (value1, value2, ...): Returns the variance based on the entire collection, including the logic and text value.

TRIMMEAN (array, percent): on average, the interior of a data set, by type the percentage of the data points at the beginning and at the end of the data set.

**TEAM TITLES fOR dISTRIBUTION PROBABILITY EXCEL**BETADIST (x, alpha, beta, A,: Returns the value of the distribution function for determining the exact density accrued interest beta.

BETAINV (probability, alpha, beta, A,: Returns the inverse of the function that calculates the probability density distribution of accumulated beta.

BINOMDIST (number_s, Trials, probability_s, cumulative): Returns the probability of the successful trials of the binary distribution.

CHIDIST (x, Degrees_freedom): Returns the probability of a chi-squared distribution.

CHIINV (probability, Degrees_freedom): Returns the inverse of the probability distribution of the expenditure side -squared.

CHITEST (actual_range, expected_range): Returns the probability value from the chi-squared distribution and the corresponding number of degrees of freedom.

cONFIDENCE (alpha, standard_dev, size): Calculate the confidence interval for a theoretical expectations

CRITBINOM (Trials, probability_s, alpha): Returns the smallest value for the cumulative binomial distribution is greater than or equal to the standard value. Often used to ensure quality applications ...

EXPONDIST (x, lambda, cumulative): Calculate the exponential distribution. Often used to simulate the time between the events ...

FDIST (x, degrees_freedom1, degrees_freedom2): Calculate the probability distribution F. Often used to find out if the two sets of data there are different degrees or not ...

FINV (probability, degrees_freedom1, degrees_freedom2): Calculates the inverse of the F probability distribution is often used to compare the variability in two data sets

FTEST (array1, array2): Returns the results of a test used to determine F. Often whether two samples have different variances or not ...

FISHER (x): Returns the Fisher transformation at x. Often used to test the hypothesis based on the correlation ...

FISHERINV (y): Calculates the inverse Fisher transformation. Often used to analyze the correlation between the array data ...

GAMMADIST (x, alpha, beta, cumulative): Returns the gamma cumulative distribution. Can be used to study skewed distribution

GAMMAINV (probability, alpha, beta): Returns the inverse of the gamma cumulative distribution.

GAMMLN (x): Calculate the natural logarithm of the gamma function

HYPGEOMDIST (number1, number2, .. .): Returns the hypergeometric distribution (probability of a certain number of successes ...)

LOGINV (probability, mean, standard_dev): Calculates the inverse of the lognormal cumulative distribution function of x (LOGNORMDIST)

LOGNORMDIST (x, mean, standard_dev): Returns the cumulative lognormal distribution of x, in which the natural logarithm of x is normally distributed with parameters mean and standard_dev.

NEGBINOMDIST (number_f, number_s, probability_s): Returns the negative binomial distribution (returns the probability that there will be failures number_f number_s times before success, when the constant probability of a success is probability_s times)

NORMDIST (x, mean, standard_dev, cumulative): returns the normal distribution ( normal distribution). Commonly used in statistics, including hypothesis testing

NORMINV (probability, mean, standard_dev): Calculates the inverse normal cumulative distribution

NORMSDIST (z): Returns the cumulative distribution function normal (standard normal cumulative distribution function), is distributed with the mean value is zero (0) and standard deviation is 1

NORMSINV (probability): Calculates the inverse cumulative distribution function of the normal

POISSON (x, mean, cumulative): Returns the Poisson distribution. Often used to estimate the number of events that will occur in a certain time period

PROB (x_range, prob_range, LOWER_LIMIT, UPPER_LIMIT): Calculate the probability of the value in the range between two limits

STANDARDIZE (x, mean, standard_dev ): Returns a normalized value from the distribution indicated by the mean and standard_dev

TDIST (x, Degrees_freedom, tails): Returns the probability of Student distribution (distribution t), where x is the value calculated from the t and be used to calculate the probability.

TINV (probability, Degrees_freedom): Returns the Student's t distribution.

TTEST (array1, array2, tails, type): calculated probability associated with the Student test.

WEIBULL (x, alpha, beta, cumulative): Returns the Weibull distribution. Often used in reliability analysis, such as average life calculation of a device.

ZTEST (array, x, sigma): Returns the probability of a part of the test z.

**TEAM TITLES ON RELATIONSHIPS AND EXCEL REGRESSION LINEAR**CORREL (array1, array2): calculate the correlation coefficient between two arrays to determine the relationship of two features

COVAR (array1, array2): Calculates the deviation of each pair of data points, then calculate the average of the volume of which

FORECAST (x, known_y's, known_x's): calculated or predict a future value by using existing values, by means of linear regression

GROWTH (known_y's, known_x's, new_x's, const ): Calculate the expected growth exponential, using the data available.

INTERCEPT (known_y's, known_x's): Find the intersection of a straight line with the y axis using the x and y values given

LINEST (known_y's, known_x's, const, stats): As the statistics for a line by using the method of least squares (least squares) to calculate a straight line that best fits the data and then returns an array describing path scale. Always use this function in an array formula.

LOGEST (known_y's, known_x's, const, stats): Used in the regression analysis. The function will calculate exponential curve fit to the data provided, and then returns an array value that describes the curve. Always use this function in an array formula

PEARSON (array1, array2): Calculate the correlation coefficient Pearson torque area (r), a dimensionless index, in the range of -1 to 1, reflecting the expansion of relations linearly between the two sets of data

RSQ (known_y's, known_x's): calculate the squared correlation coefficient torque achievement Pearson (r), through data points in known_y's and known_x's

sLOPE (known_y's, known_x's): calculate the slope of road linear regression through the data points.

STEYX (known_y's, known_x's): Returns the standard error of the predicted value y for each value x in the regression.

TREND (known_y's, known_x's, new_x's, const): Returns about the trend of linear

**functions EXCEL DATABASE MANAGEMENT aND LIST - LIST DATABASE aND fUNCTIONS managament**general of Management functions Database and List

DAVERAGE (database, field, criteria): calculated the average of the values in a column of the list or the value of a database, according to a specified condition.

DCOUNT (database, field, criteria): Counting cells containing data in a column of the list or the value of a database, according to a specified condition.

DCOUNTA (database, field, criteria): Counting of cells "is not empty" in a column of the list or the value of a database, according to a specified condition.

DGET (database, field, criteria): Extract a value from a column of a list or database, matches the specified conditions.

DMAX (database, field, criteria) : Returns the largest value in a column of a list or a database, according to a specified condition.

DMIN (database, field, criteria): Returns the smallest value in a column of a list hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DRODUCT (database, field, criteria) : Nhân các giá trị trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DSTDEV (database, field, criteria) : Ước lượng độ lệch chuẩn của một tập hợp theo mẫu, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DSTDEVP (database, field, criteria) : Tính độ lệch chuẩn của một tập hợp theo toàn thể các tập hợp, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DSUM (database, field, criteria) : Cộng các số trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DVAR (database, field, criteria) : Ước lượng sự biến thiên của một tập hợp dựa trên một mẫu, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DVARP (database, field , criteria) : Tính toán sự biến thiên của một tập hợp dựa trên toàn thể tập hợp, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

Một số ví dụ về cách dùng Criteria để nhập điều kiện

GETPIVOTDATA (data_field, pivot_table, field1, item1, field2, item2,...) : Trả về dữ liệu được lưu giữ trong báo cáo PivotTable.

IMCOS (inumber): Returns the cosine of a complex number

IMDIV (inumber1, inumber2): Calculated quotient (result of the division) of two complex numbers

IMEXP (inumber): Returns the exponential of a complex number

IMLN (inumber) : Returns logarite complex nature of some

IMLOG10 (inumber): Returns the decimal logarite of a complex number

IMLOG2 (inumber): Returns logarite base 2 of a complex number

IMPOWER (inumber, number): Calculated power of a complex number

IMPRODUCT (inumber1, inumber2, ...): Calculates the number of 2 to 255 complex numbers together

IMREAL (inumber): Returns the real coefficient of a complex number

IMSIN (inumber): Returns the sine of a number complex

IMSQRT (inumber): Returns the square root of a complex number 2

IMSUB (inumber1, inumber2): Calculate the difference of two complex numbers

IMSUM (inumber1, inumber2, ...): Calculate the sum of 2 to 255 complex numbers

OCT2BIN ( number, places) Converts an octal number to binary number

OCT2DEC (number): Converts an octal number to decimal

OCT2HEX (number, places) Converts an octal number to a hexadecimal number

**EXCEL fUNCTIONS oF FINANCIAL - FINANCIAN fUNCTIONS**ACCRINT (issue, first_interest, settlement, rate, par, frequency, basis, calc_method): Calculates the accrued interest for a securities pay interest periodically

ACCRINTM (issue, settlement, rate, par, basis): Calculates the accrued interest for securities with interest payment on maturity

AMORDEGRC (cost, date_purchased, first_period, Salvage, period, rate, basis): As per fiscal depreciation in accounting depending on the shelf life of the asset (used in the system according to French accounting)

AMORLINC (cost, date_purchased, first_period, Salvage, period, rate, basis): As per fiscal depreciation accounting (used in the accounting system under French)

COUPDAYBS (settlement, maturity , frequency, basis): Count the number of days since the beginning of the next interest period settlement date

COUPDAYS (settlement, maturity, frequency, basis): Count the number of days in the interest period including the settlement date

COUPDAYSCN (settlement, maturity, frequency, basis ): Calculates the number of days from the settlement date until the next interest

COUPNCD (settlement, maturity, frequency, basis): Returns a number that represents the next interest date after the settlement date since

COUPNUM (settlement, maturity, frequency, basis): Count the number of times the interest payable between settlement date and the maturity date

COUPPCD (settlement, maturity, frequency, basis): returns a number that represents the last interest payment date, first date accounting

CUMIPMT (rate, nper, pv, start_period, end_period, type): Calculate the cumulative income to pay for loans in the period between start_period and end_period

CUMPRINC (rate, nper, pv, start_period, end_period, type): returns the accumulated capital to pay for the loan for the period between start_period and end_period

DB (cost, salvage regimens, life, period, month): Calculate depreciation for an asset using a declining balance method in a fixed rates (fixed-declining balance method) in a defined time period.

DDB (cost, salvage regimens, life, period, factor): Calculated asset depreciation for a method using the double-declining balance (double- declining balance method), or decrease over a certain percentage, for a defined period of time.

DISC (settlement, maturity, pr, redemption, basis): Calculate the discount rate of a security

DOLLARDE (fractional_dollar, fraction ): Convert fractional dollar price to the dollar price in decimal format

DOLLARFR (decimal_dollar, fraction): Convert the decimal value in the form of dollars to the price of the dollar in the form of fractional

DURATION (settlement, maturity, coupon, YLD, frequency, basis): Calculated validity period Macauley based on copper face value $ 100 (validity period is the weighted average current value of the flow of cash and is used as a measure of feedback that changed benefits the net value of the bonds)

EFFECT (nominal_rate, Npery): Calculate the annual real interest rate, anticipated annual nominal interest rate and the total number of payment periods per year compounding

FV (rate, nper, pmt, pv, type): Calculate the value of the investment period based on periodic fixed payments and a fixed interest rate

FVSCHEDULE (principal, schedule): Calculate the value of a term of the initial capital after the application of a series of compound interest (calculated value for an investment period with interest rate changes)

INTRATE (settlement, maturity, investment, redemption, basis): calculated interest rate of investment securities for a whole

IPMT (rate, per, nper , pv, fv, type): returns the interest payment for an investment based on periodic fixed payments and a constant interest rate based on

IRR (values, guess): Calculates the internal rate for a sequence of functions cash flow is represented by the value of

ISPMT (rate, per, nper, pv): Calculate the amount of interest paid in a certain period for a loan with a constant interest rate, after deduction of the amount Original paid for that period.

MDURATION (settlement, maturity, coupon, YLD, frequency, basis): As of deadline Macauley modified for stock-based co-denomination $ 100

MIRR (values, finance_rate, reinvest_rate): Calculated margin business intrinsic interest in a series of cash flow in cycles

nOMINAL (effect_rate, Npery): calculate the annual nominal interest rate, anticipated real interest and compounding periods per year

NPER (rate, pmt, pv, fv, type): Calculate the number of terms to repay investment loans based on each cycle, the amount paid and the fixed rate of return

NPV (rate, value1, value2, ...): Calculate the net present value of an investment using discount rate compares with more maturity payments (negative values) and income (positive values)

ODDFPRICE (settlement, maturity, issue, first_coupon, rate, YLD, redemption, frequency, basis) : Calculate the value per contract face value $ 100 of securities with first semester odd (short or long)

ODDFYIELD (settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, basis): Returns profit of a stock and a time to first interest is odd (short or long)

ODDLPRICE (settlement, maturity, last_interest, rate, YLD, redemption, frequency, basis): calculated value per contract face value $ 100 of stock with coupon interest period end is odd (short or long)

ODDLYIELD (settlement, maturity, last_interest, rate, pr, redemption, frequency, basis): Calculates the yield of securities is odd last period (short-term or long-term)

PMT (rate, nper, pv, fv, type): Count money to pay for loans with interest rate constant and regular pay

PPMT (rate, per, nper, pv, fv, type): calculate payment funds in a given period for an investment, in which the payment is made regularly periodically with a constant interest rate

PRICE (settlement, maturity, rate, YLD, redemption, frequency , basis): Calculate the value of securities on the contract face value $ 100, interest payments cyclical

PRICEDISC (settlement, maturity, discount, redemption, basis): Calculated values on copper denominations of $ 100 of a stock has discounted

PRICEMAT (settlement, maturity, issue, rate, YLD, basis): Calculated values on copper denominations of $ 100 a securities pay interest at maturity

PV (rate, nper, pmt, fv, type): As prices present value of an investment

rATE (nper, pmt, pv, fv, type, guess): Calculated interest rate per period of an annuity

REVEICED (settlement, maturity, investment, discount, basis): Calculate the amount received to maturity investment securities for a whole

SLN (cost, salvage regimens, life): Calculate the cost of depreciation (straight line basis) of an asset for one period

SYD (cost, salvage regimens, life, per ): Calculated amortized, the carrying value of assets in the period for determination of

TBILLEQ (settlement, maturity, discount): Calculates the yield corresponds to bonds for treasury bonds

TBILLPRICE (settlement, maturity, discount): Calculated the value of par value $ 100 for treasuries

TBILLYIELD (settlement, maturity, pr): Calculates the yield for treasury bonds

VDB (cost, salvage regimens, life, start_period, end_period, factor, no_switch): Calculated asset depreciation used in many period

XIRR (values, dates, guess): Calculated inner yields a roving series non-recurring cash

XNPV (rate, values, dates): Calculated rates for a range of mobile net cash periodic

YIELD (settlement, maturity, rate, pr, redemption, frequency, basis): Calculates the yield on securities pay interest periodically

YIELDDISC (settlement, maturity, pr, redemption, basis): Calculate the annual profit the stock has discounted

YIELDMAT (settlement, maturity, issue, rate, pr, basis): Calculated annual profits of securities pay interest at maturity

**Excel trigonometric functions - trigonometric fUNCTIONS**ACOS (number): returns a radian values range from 0 to pi, the arccosine, or inverse cosine of a number in the range of -1 to 1

ACOSH (number): Returns a radian value, is the inverse hyperbolic cosine-of some greater than or equal to 1

ASIN (number): Returns a value in the period from -Pi radians / 2 to Pi / 2, is the arcsine, or inverse sine of a number in the range of -1 to 1

ASINH (number ): Returns a radian value, is the inverse hyperbolic sine-of some

ATAN (number): Returns a value in the range of -Pi radians / 2 to Pi / 2, is the arctangent, or inverse tangent of some

ATAN2 (x_num, y_num): Returns a value radian range (but not including) from -Pi to Pi, is tangent, or inverse tangent of a point with coordinates x and y

ATANH (number) : Returns a radian value, is the inverse of a tang-hyperbolic range from -1 to 1

COS (number): Returns a radian value, the cosine of a number

COSH (number): Returns a radian value, the hyperbolic cosine of a number-

dEGREES (angle): Convert measurements from radians to degrees angle

rADIANS (angle): Conversion of an angle measurement from degrees to radians

SIN (number): Returns radian is a sine value of some

BIRTH (number): Returns a radian value, is of some hyperbolic sine-

TAN (number): Returns a radian value, is evidence of some

TANH (number): Returning a radian value, is evidence of some hyperbolic-

Aggregate functions in Excel 2003 2007 2010 2013 Basic Advanced
Reviewed by Duy Tín
on
11:42:00 PM
Rating:

## No comments:

Post a Comment