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 basic Excel functions that can do different types. However, to calculate optimal, there are many other functions can excel optimal handling as well as the simple formula excel where you may not know.
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.
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-
n fact, some functions are not much use excel, you only need to master the basic Excel functions that can do different types. However, to calculate optimal, there are many other functions can excel optimal handling as well as the simple formula excel where you may not know.
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
![Aggregate functions in Excel 2003 2007 2010 2013 Basic Advanced]()
Reviewed by Tín Nguyễn
on
11:42:00 PM
Rating:
No comments:
Post a Comment