Share via


WorksheetFunction Object Members

Excel Developer Reference

Used as a container for Microsoft Excel worksheet functions that can be called from Visual Basic.

Methods

  Name Description
Bb225774.methods(en-us,office.12).gif AccrInt Returns the accrued interest for a security that pays periodic interest.
Bb225774.methods(en-us,office.12).gif AccrIntM Returns the accrued interest for a security that pays interest at maturity.
Bb225774.methods(en-us,office.12).gif Acos Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is Arg1. The returned angle is given in radians in the range 0 (zero) to pi.
Bb225774.methods(en-us,office.12).gif Acosh Returns the inverse hyperbolic cosine of a number. Number must be greater than or equal to 1. The inverse hyperbolic cosine is the value whose hyperbolic cosine is Arg1, so Acosh(Cosh(number)) equals Arg1.
Bb225774.methods(en-us,office.12).gif AmorDegrc Returns the depreciation for each accounting period. This function is provided for the French accounting system.
Bb225774.methods(en-us,office.12).gif AmorLinc Returns the depreciation for each accounting period. This function is provided for the French accounting system.
Bb225774.methods(en-us,office.12).gif And Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.
Bb225774.methods(en-us,office.12).gif Asc For Double-byte character set (DBCS) languages, changes full-width (double-byte) characters to half-width (single-byte) characters.
Bb225774.methods(en-us,office.12).gif Asin Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is Arg1. The returned angle is given in radians in the range -pi/2 to pi/2.
Bb225774.methods(en-us,office.12).gif Asinh Returns the inverse hyperbolic sine of a number. The inverse hyperbolic sine is the value whose hyperbolic sine is Arg1, so Asinh(Sinh(number)) equals Arg1.
Bb225774.methods(en-us,office.12).gif Atan2 Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates. The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a point with coordinates (x_num, y_num). The angle is given in radians between -pi and pi, excluding -pi.
Bb225774.methods(en-us,office.12).gif Atanh Returns the inverse hyperbolic tangent of a number. Number must be between -1 and 1 (excluding -1 and 1).
Bb225774.methods(en-us,office.12).gif AveDev Returns the average of the absolute deviations of data points from their mean. AveDev is a measure of the variability in a data set.
Bb225774.methods(en-us,office.12).gif Average Returns the average (arithmetic mean) of the arguments.
Bb225774.methods(en-us,office.12).gif AverageIf Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.
Bb225774.methods(en-us,office.12).gif AverageIfs Returns the average (arithmetic mean) of all cells that meet multiple criteria.
Bb225774.methods(en-us,office.12).gif BahtText Converts a number to Thai text and adds a suffix of "Baht."
Bb225774.methods(en-us,office.12).gif BesselI Returns the modified Bessel function, which is equivalent to the Bessel function evaluated for purely imaginary arguments.
Bb225774.methods(en-us,office.12).gif BesselJ Returns the Bessel function.
Bb225774.methods(en-us,office.12).gif BesselK Returns the modified Bessel function, which is equivalent to the Bessel functions evaluated for purely imaginary arguments.
Bb225774.methods(en-us,office.12).gif BesselY Returns the Bessel function, which is also called the Weber function or the Neumann function.
Bb225774.methods(en-us,office.12).gif BetaDist Returns the beta cumulative distribution function.
Bb225774.methods(en-us,office.12).gif BetaInv Returns the inverse of the cumulative distribution function for a specified beta distribution. That is, if probability = BetaDist(x,...), then BetaInv(probability,...) = x.
Bb225774.methods(en-us,office.12).gif Bin2Dec Converts a binary number to decimal.
Bb225774.methods(en-us,office.12).gif Bin2Hex Converts a binary number to hexadecimal.
Bb225774.methods(en-us,office.12).gif Bin2Oct Converts a binary number to octal.
Bb225774.methods(en-us,office.12).gif BinomDist Returns the individual term binomial distribution probability.
Bb225774.methods(en-us,office.12).gif Ceiling Returns number rounded up, away from zero, to the nearest multiple of significance.
Bb225774.methods(en-us,office.12).gif ChiDist Returns the one-tailed probability of the chi-squared distribution.
Bb225774.methods(en-us,office.12).gif ChiInv Returns the inverse of the one-tailed probability of the chi-squared distribution.
Bb225774.methods(en-us,office.12).gif ChiTest Returns the test for independence.
Bb225774.methods(en-us,office.12).gif Choose Uses Arg1 as the index to return a value from the list of value arguments.
Bb225774.methods(en-us,office.12).gif Clean Removes all nonprintable characters from text.
Bb225774.methods(en-us,office.12).gif Combin Returns the number of combinations for a given number of items. Use Combin to determine the total possible number of groups for a given number of items.
Bb225774.methods(en-us,office.12).gif Complex Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj.
Bb225774.methods(en-us,office.12).gif Confidence Returns a value that you can use to construct a confidence interval for a population mean.
Bb225774.methods(en-us,office.12).gif Convert Converts a number from one measurement system to another. For example, Convert can translate a table of distances in miles to a table of distances in kilometers.
Bb225774.methods(en-us,office.12).gif Correl Returns the correlation coefficient of the Arg1 and Arg2 cell ranges.
Bb225774.methods(en-us,office.12).gif Cosh Returns the hyperbolic cosine of a number.
Bb225774.methods(en-us,office.12).gif Count Counts the number of cells that contain numbers and counts numbers within the list of arguments.
Bb225774.methods(en-us,office.12).gif CountA Counts the number of cells that are not empty and the values within the list of arguments.
Bb225774.methods(en-us,office.12).gif CountBlank Counts empty cells in a specified range of cells.
Bb225774.methods(en-us,office.12).gif CountIf Counts the number of cells within a range that meet the given criteria.
Bb225774.methods(en-us,office.12).gif CountIfs Counts the number of cells within a range that meet multiple criteria.
Bb225774.methods(en-us,office.12).gif CoupDayBs Returns the number of days from the beginning of the coupon period to the settlement date.
Bb225774.methods(en-us,office.12).gif CoupDays Returns the number of days in the coupon period that contains the settlement date.
Bb225774.methods(en-us,office.12).gif CoupDaysNc Returns the number of days from the settlement date to the next coupon date.
Bb225774.methods(en-us,office.12).gif CoupNcd Returns a number that represents the next coupon date after the settlement date.
Bb225774.methods(en-us,office.12).gif CoupNum Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon.
Bb225774.methods(en-us,office.12).gif CoupPcd Returns the previous coupon before the settlement date.
Bb225774.methods(en-us,office.12).gif Covar Returns covariance, the average of the products of deviations for each data point pair.
Bb225774.methods(en-us,office.12).gif CritBinom Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.
Bb225774.methods(en-us,office.12).gif CumIPmt Returns the cumulative interest paid on a loan between start_period and end_period.
Bb225774.methods(en-us,office.12).gif CumPrinc Returns the cumulative principal paid on a loan between start_period and end_period.
Bb225774.methods(en-us,office.12).gif DAverage Averages the values in a column of a list or database that match conditions you specify.
Bb225774.methods(en-us,office.12).gif Days360 Returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations.
Bb225774.methods(en-us,office.12).gif Db Returns the depreciation of an asset for a specified period using the fixed-declining balance method.
Bb225774.methods(en-us,office.12).gif Dbcs Converts half-width (single-byte) letters within a character string to full-width (double-byte) characters. The name of the function (and the characters that it converts) depends upon the language settings. Read/write String.
Bb225774.methods(en-us,office.12).gif DCount Counts the cells that contain numbers in a column of a list or database that match conditions that you specify.
Bb225774.methods(en-us,office.12).gif DCountA Counts the nonblank cells in a column of a list or database that match conditions that you specify.
Bb225774.methods(en-us,office.12).gif Ddb Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify.
Bb225774.methods(en-us,office.12).gif Dec2Bin Converts a decimal number to binary.
Bb225774.methods(en-us,office.12).gif Dec2Hex Converts a decimal number to hexadecimal.
Bb225774.methods(en-us,office.12).gif Dec2Oct Converts a decimal number to octal.
Bb225774.methods(en-us,office.12).gif Degrees Converts radians into degrees.
Bb225774.methods(en-us,office.12).gif Delta Tests whether two values are equal. Returns 1 if number1 = number2; returns 0 otherwise.
Bb225774.methods(en-us,office.12).gif DevSq Returns the sum of squares of deviations of data points from their sample mean.
Bb225774.methods(en-us,office.12).gif DGet Extracts a single value from a column of a list or database that matches conditions that you specify.
Bb225774.methods(en-us,office.12).gif Disc Returns the discount rate for a security.
Bb225774.methods(en-us,office.12).gif DMax Returns the largest number in a column of a list or database that matches conditions you that specify.
Bb225774.methods(en-us,office.12).gif DMin Returns the smallest number in a column of a list or database that matches conditions that you specify.
Bb225774.methods(en-us,office.12).gif Dollar The function described in this Help topic converts a number to text format and applies a currency symbol. The name of the function (and the symbol that it applies) depends upon your language settings.
Bb225774.methods(en-us,office.12).gif DollarDe Converts a dollar price expressed as a fraction into a dollar price expressed as a decimal number. Use DOLLARDE to convert fractional dollar numbers, such as securities prices, to decimal numbers.
Bb225774.methods(en-us,office.12).gif DollarFr Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction. Use DOLLARFR to convert decimal numbers to fractional dollar numbers, such as securities prices.
Bb225774.methods(en-us,office.12).gif DProduct Multiplies the values in a column of a list or database that match conditions that you specify.
Bb225774.methods(en-us,office.12).gif DStDev Estimates the standard deviation of a population based on a sample by using the numbers in a column of a list or database that match conditions that you specify.
Bb225774.methods(en-us,office.12).gif DStDevP Calculates the standard deviation of a population based on the entire population by using the numbers in a column of a list or database that match conditions that you specify.
Bb225774.methods(en-us,office.12).gif DSum Adds the numbers in a column of a list or database that match conditions that you specify.
Bb225774.methods(en-us,office.12).gif Duration Returns the Macauley duration for an assumed par value of $100. Duration is defined as the weighted average of the present value of the cash flows and is used as a measure of a bond price's response to changes in yield.
Bb225774.methods(en-us,office.12).gif DVar Estimates the variance of a population based on a sample by using the numbers in a column of a list or database that match conditions that you specify.
Bb225774.methods(en-us,office.12).gif DVarP Calculates the variance of a population based on the entire population by using the numbers in a column of a list or database that match conditions that you specify.
Bb225774.methods(en-us,office.12).gif EDate Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.
Bb225774.methods(en-us,office.12).gif Effect Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.
Bb225774.methods(en-us,office.12).gif EoMonth Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
Bb225774.methods(en-us,office.12).gif Erf Returns the error function integrated between lower_limit and upper_limit.
Bb225774.methods(en-us,office.12).gif ErfC Returns the complementary ERF function integrated between the specified parameter and infinity.
Bb225774.methods(en-us,office.12).gif Even Returns number rounded up to the nearest even integer. You can use this function for processing items that come in twos. For example, a packing crate accepts rows of one or two items. The crate is full when the number of items, rounded up to the nearest two, matches the crate's capacity.
Bb225774.methods(en-us,office.12).gif ExponDist Returns the exponential distribution. Use EXPONDIST to model the time between events, such as how long an automated bank teller takes to deliver cash. For example, you can use EXPONDIST to determine the probability that the process takes at most 1 minute.
Bb225774.methods(en-us,office.12).gif Fact Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number.
Bb225774.methods(en-us,office.12).gif FactDouble Returns the double factorial of a number.
Bb225774.methods(en-us,office.12).gif FDist Returns the F probability distribution. You can use this function to determine whether two data sets have different degrees of diversity. For example, you can examine the test scores of men and women entering high school and determine if the variability in the females is different from that found in the males.
Bb225774.methods(en-us,office.12).gif Find Finds specific information in a worksheet.
Bb225774.methods(en-us,office.12).gif FindB FIND and FINDB locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.
Bb225774.methods(en-us,office.12).gif FInv Returns the inverse of the F probability distribution. If p = FDIST(x,...), then FINV(p,...) = x.
Bb225774.methods(en-us,office.12).gif Fisher Returns the Fisher transformation at x. This transformation produces a function that is normally distributed rather than skewed. Use this function to perform hypothesis testing on the correlation coefficient.
Bb225774.methods(en-us,office.12).gif FisherInv Returns the inverse of the Fisher transformation. Use this transformation when analyzing correlations between ranges or arrays of data. If y = FISHER(x), then FISHERINV(y) = x.
Bb225774.methods(en-us,office.12).gif Fixed Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
Bb225774.methods(en-us,office.12).gif Floor Rounds number down, toward zero, to the nearest multiple of significance.
Bb225774.methods(en-us,office.12).gif Forecast Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.
Bb225774.methods(en-us,office.12).gif Frequency Calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores. Because FREQUENCY returns an array, it must be entered as an array formula.
Bb225774.methods(en-us,office.12).gif FTest Returns the result of an F-test. An F-test returns the two-tailed probability that the variances in array1 and array2 are not significantly different. Use this function to determine whether two samples have different variances. For example, given test scores from public and private schools, you can test whether these schools have different levels of test score diversity.
Bb225774.methods(en-us,office.12).gif Fv Returns the future value of an investment based on periodic, constant payments and a constant interest rate.
Bb225774.methods(en-us,office.12).gif FVSchedule Returns the future value of an initial principal after applying a series of compound interest rates. Use FVSCHEDULE to calculate the future value of an investment with a variable or adjustable rate.
Bb225774.methods(en-us,office.12).gif GammaDist Returns the gamma distribution. You can use this function to study variables that may have a skewed distribution. The gamma distribution is commonly used in queuing analysis.
Bb225774.methods(en-us,office.12).gif GammaInv Returns the inverse of the gamma cumulative distribution. If p = GAMMADIST(x,...), then GAMMAINV(p,...) = x.
Bb225774.methods(en-us,office.12).gif GammaLn Returns the natural logarithm of the gamma function, Γ(x).
Bb225774.methods(en-us,office.12).gif Gcd Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder.
Bb225774.methods(en-us,office.12).gif GeoMean Returns the geometric mean of an array or range of positive data. For example, you can use GEOMEAN to calculate average growth rate given compound interest with variable rates.
Bb225774.methods(en-us,office.12).gif GeStep Returns 1 if number ≥ step; returns 0 (zero) otherwise. Use this function to filter a set of values. For example, by summing several GESTEP functions you calculate the count of values that exceed a threshold.
Bb225774.methods(en-us,office.12).gif Growth Calculates predicted exponential growth by using existing data. GROWTH returns the y-values for a series of new x-values that you specify by using existing x-values and y-values. You can also use the GROWTH worksheet function to fit an exponential curve to existing x-values and y-values.
Bb225774.methods(en-us,office.12).gif HarMean Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the arithmetic mean of reciprocals.
Bb225774.methods(en-us,office.12).gif Hex2Bin Converts a hexadecimal number to binary.
Bb225774.methods(en-us,office.12).gif Hex2Dec Converts a hexadecimal number to decimal.
Bb225774.methods(en-us,office.12).gif Hex2Oct Converts a hexadecimal number to octal.
Bb225774.methods(en-us,office.12).gif HLookup Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.
Bb225774.methods(en-us,office.12).gif HypGeomDist Returns the hypergeometric distribution. HYPGEOMDIST returns the probability of a given number of sample successes, given the sample size, population successes, and population size. Use HYPGEOMDIST for problems with a finite population, where each observation is either a success or a failure, and where each subset of a given size is chosen with equal likelihood.
Bb225774.methods(en-us,office.12).gif IfError Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR function to trap and handle errors in a formula.
Bb225774.methods(en-us,office.12).gif ImAbs Returns the absolute value (modulus) of a complex number in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif Imaginary Returns the imaginary coefficient of a complex number in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif ImArgument Returns the argument (theta), an angle expressed in radians, such that:
Bb225774.methods(en-us,office.12).gif ImConjugate Returns the complex conjugate of a complex number in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif ImCos Returns the cosine of a complex number in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif ImDiv Returns the quotient of two complex numbers in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif ImExp Returns the exponential of a complex number in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif ImLn Returns the natural logarithm of a complex number in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif ImLog10 Returns the common logarithm (base 10) of a complex number in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif ImLog2 Returns the base-2 logarithm of a complex number in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif ImPower Returns a complex number in x + yi or x + yj text format raised to a power.
Bb225774.methods(en-us,office.12).gif ImProduct Returns the product of 2 to 29 complex numbers in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif ImReal Returns the real coefficient of a complex number in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif ImSin Returns the sine of a complex number in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif ImSqrt Returns the square root of a complex number in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif ImSub Returns the difference of two complex numbers in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif ImSum Returns the sum of two or more complex numbers in x + yi or x + yj text format.
Bb225774.methods(en-us,office.12).gif Index Returns a value or the reference to a value from within a table or range. There are two forms of the INDEX function: the array form and the reference form.
Bb225774.methods(en-us,office.12).gif Intercept Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values. The intercept point is based on a best-fit regression line plotted through the known x-values and known y-values. Use the INTERCEPT function when you want to determine the value of the dependent variable when the independent variable is 0 (zero). For example, you can use the INTERCEPT function to predict a metal's electrical resistance at 0°C when your data points were taken at room temperature and higher.
Bb225774.methods(en-us,office.12).gif IntRate Returns the interest rate for a fully invested security.
Bb225774.methods(en-us,office.12).gif Ipmt Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
Bb225774.methods(en-us,office.12).gif Irr Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.
Bb225774.methods(en-us,office.12).gif IsErr Checks the type of value and returns TRUE or FALSE depending if the value refers to any error value except #N/A.
Bb225774.methods(en-us,office.12).gif IsError Checks the type of value and returns TRUE or FALSE depending if the value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
Bb225774.methods(en-us,office.12).gif IsEven Checks the type of value and returns TRUE or FALSE depending if the value is even.
Bb225774.methods(en-us,office.12).gif IsLogical Checks the type of value and returns TRUE or FALSE depending if the value refers to a logical value.
Bb225774.methods(en-us,office.12).gif IsNA Checks the type of value and returns TRUE or FALSE depending if the value refers to the #N/A (value not available) error value.
Bb225774.methods(en-us,office.12).gif IsNonText Checks the type of value and returns TRUE or FALSE depending if the value refers to any item that is not text. (Note that this function returns TRUE if value refers to a blank cell.)
Bb225774.methods(en-us,office.12).gif IsNumber Checks the type of value and returns TRUE or FALSE depending if the value refers to a number.
Bb225774.methods(en-us,office.12).gif IsOdd Checks the type of value and returns TRUE or FALSE depending if the value is odd.
Bb225774.methods(en-us,office.12).gif Ispmt Calculates the interest paid during a specific period of an investment. This function is provided for compatibility with Lotus 1-2-3.
Bb225774.methods(en-us,office.12).gif IsText Checks the type of value and returns TRUE or FALSE depending if the value refers to text.
Bb225774.methods(en-us,office.12).gif Kurt Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness or flatness of a distribution compared with the normal distribution. Positive kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a relatively flat distribution.
Bb225774.methods(en-us,office.12).gif Large Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score.
Bb225774.methods(en-us,office.12).gif Lcm Returns the least common multiple of integers. The least common multiple is the smallest positive integer that is a multiple of all integer arguments number1, number2, and so on. Use LCM to add fractions with different denominators.
Bb225774.methods(en-us,office.12).gif LinEst Calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, and returns an array that describes the line. Because this function returns an array of values, it must be entered as an array formula.
Bb225774.methods(en-us,office.12).gif Ln Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904).
Bb225774.methods(en-us,office.12).gif Log Returns the logarithm of a number to the base you specify.
Bb225774.methods(en-us,office.12).gif Log10 Returns the base-10 logarithm of a number.
Bb225774.methods(en-us,office.12).gif LogEst In regression analysis, calculates an exponential curve that fits your data and returns an array of values that describes the curve. Because this function returns an array of values, it must be entered as an array formula.
Bb225774.methods(en-us,office.12).gif LogInv Use the lognormal distribution to analyze logarithmically transformed data.
Bb225774.methods(en-us,office.12).gif LogNormDist Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard_dev. Use this function to analyze data that has been logarithmically transformed.
Bb225774.methods(en-us,office.12).gif Lookup Returns a value either from a one-row or one-column range or from an array. The LOOKUP function has two syntax forms: the vector form and the array form.
Bb225774.methods(en-us,office.12).gif Match Returns the relative position of an item in an array that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.
Bb225774.methods(en-us,office.12).gif Max Returns the largest value in a set of values.
Bb225774.methods(en-us,office.12).gif MDeterm Returns the matrix determinant of an array.
Bb225774.methods(en-us,office.12).gif MDuration Returns the modified Macauley duration for a security with an assumed par value of $100.
Bb225774.methods(en-us,office.12).gif Median Returns the median of the given numbers. The median is the number in the middle of a set of numbers.
Bb225774.methods(en-us,office.12).gif Min Returns the smallest number in a set of values.
Bb225774.methods(en-us,office.12).gif MInverse Returns the inverse matrix for the matrix stored in an array.
Bb225774.methods(en-us,office.12).gif MIrr Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the interest received on reinvestment of cash.
Bb225774.methods(en-us,office.12).gif MMult Returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2.
Bb225774.methods(en-us,office.12).gif Mode Returns the most frequently occurring, or repetitive, value in an array or range of data.
Bb225774.methods(en-us,office.12).gif MRound Returns a number rounded to the desired multiple.
Bb225774.methods(en-us,office.12).gif MultiNomial Returns the ratio of the factorial of a sum of values to the product of factorials.
Bb225774.methods(en-us,office.12).gif NegBinomDist Returns the negative binomial distribution. NEGBINOMDIST returns the probability that there will be number_f failures before the number_s-th success, when the constant probability of a success is probability_s. This function is similar to the binomial distribution, except that the number of successes is fixed, and the number of trials is variable. Like the binomial, trials are assumed to be independent.
Bb225774.methods(en-us,office.12).gif NetworkDays Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
Bb225774.methods(en-us,office.12).gif Nominal Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year.
Bb225774.methods(en-us,office.12).gif NormDist Returns the normal distribution for the specified mean and standard deviation. This function has a very wide range of applications in statistics, including hypothesis testing.
Bb225774.methods(en-us,office.12).gif NormInv Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
Bb225774.methods(en-us,office.12).gif NormSDist Returns the standard normal cumulative distribution function. The distribution has a mean of 0 (zero) and a standard deviation of one. Use this function in place of a table of standard normal curve areas.
Bb225774.methods(en-us,office.12).gif NormSInv Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one.
Bb225774.methods(en-us,office.12).gif NPer Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
Bb225774.methods(en-us,office.12).gif Npv Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).
Bb225774.methods(en-us,office.12).gif Oct2Bin Converts an octal number to binary.
Bb225774.methods(en-us,office.12).gif Oct2Dec Converts an octal number to decimal.
Bb225774.methods(en-us,office.12).gif Oct2Hex Converts an octal number to hexadecimal.
Bb225774.methods(en-us,office.12).gif Odd Returns number rounded up to the nearest odd integer.
Bb225774.methods(en-us,office.12).gif OddFPrice Returns the price per $100 face value of a security having an odd (short or long) first period.
Bb225774.methods(en-us,office.12).gif OddFYield Returns the yield of a security that has an odd (short or long) first period.
Bb225774.methods(en-us,office.12).gif OddLPrice Returns the price per $100 face value of a security having an odd (short or long) last coupon period.
Bb225774.methods(en-us,office.12).gif OddLYield Returns the yield of a security that has an odd (short or long) last period.
Bb225774.methods(en-us,office.12).gif Or Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
Bb225774.methods(en-us,office.12).gif Pearson Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets.
Bb225774.methods(en-us,office.12).gif Percentile Returns the k-th percentile of values in a range. You can use this function to establish a threshold of acceptance. For example, you can decide to examine candidates who score above the 90th percentile.
Bb225774.methods(en-us,office.12).gif PercentRank Returns the rank of a value in a data set as a percentage of the data set. This function can be used to evaluate the relative standing of a value within a data set. For example, you can use PERCENTRANK to evaluate the standing of an aptitude test score among all scores for the test.
Bb225774.methods(en-us,office.12).gif Permut Returns the number of permutations for a given number of objects that can be selected from number objects. A permutation is any set or subset of objects or events where internal order is significant. Permutations are different from combinations, for which the internal order is not significant. Use this function for lottery-style probability calculations.
Bb225774.methods(en-us,office.12).gif Phonetic Extracts the phonetic (furigana) characters from a text string.
Bb225774.methods(en-us,office.12).gif Pi Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.
Bb225774.methods(en-us,office.12).gif Pmt Calculates the payment for a loan based on constant payments and a constant interest rate.
Bb225774.methods(en-us,office.12).gif Poisson Returns the Poisson distribution. A common application of the Poisson distribution is predicting the number of events over a specific time, such as the number of cars arriving at a toll plaza in 1 minute.
Bb225774.methods(en-us,office.12).gif Power Returns the result of a number raised to a power.
Bb225774.methods(en-us,office.12).gif Ppmt Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
Bb225774.methods(en-us,office.12).gif Price Returns the price per $100 face value of a security that pays periodic interest.
Bb225774.methods(en-us,office.12).gif PriceDisc Returns the price per $100 face value of a discounted security.
Bb225774.methods(en-us,office.12).gif PriceMat Returns the price per $100 face value of a security that pays interest at maturity.
Bb225774.methods(en-us,office.12).gif Prob Returns the probability that values in a range are between two limits. If upper_limit is not supplied, returns the probability that values in x_range are equal to lower_limit.
Bb225774.methods(en-us,office.12).gif Product Multiplies all the numbers given as arguments and returns the product.
Bb225774.methods(en-us,office.12).gif Proper Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.
Bb225774.methods(en-us,office.12).gif Pv Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.
Bb225774.methods(en-us,office.12).gif Quartile Returns the quartile of a data set. Quartiles often are used in sales and survey data to divide populations into groups. For example, you can use QUARTILE to find the top 25 percent of incomes in a population.
Bb225774.methods(en-us,office.12).gif Quotient Returns the integer portion of a division. Use this function when you want to discard the remainder of a division.
Bb225774.methods(en-us,office.12).gif Radians Converts degrees to radians.
Bb225774.methods(en-us,office.12).gif RandBetween Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated.
Bb225774.methods(en-us,office.12).gif Rank Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)
Bb225774.methods(en-us,office.12).gif Rate Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.
Bb225774.methods(en-us,office.12).gif Received Returns the amount received at maturity for a fully invested security.
Bb225774.methods(en-us,office.12).gif Replace Replaces part of a text string, based on the number of characters you specify, with a different text string.
Bb225774.methods(en-us,office.12).gif ReplaceB REPLACEB replaces part of a text string, based on the number of bytes you specify, with a different text string.
Bb225774.methods(en-us,office.12).gif Rept Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.
Bb225774.methods(en-us,office.12).gif Roman Converts an arabic numeral to roman, as text.
Bb225774.methods(en-us,office.12).gif Round Rounds a number to a specified number of digits.
Bb225774.methods(en-us,office.12).gif RoundDown Rounds a number down, toward zero.
Bb225774.methods(en-us,office.12).gif RoundUp Rounds a number up, away from 0 (zero).
Bb225774.methods(en-us,office.12).gif RSq Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's. For more information, see PEARSON. The r-squared value can be interpreted as the proportion of the variance in y attributable to the variance in x.
Bb225774.methods(en-us,office.12).gif RTD This method connects to a source to receive real-time data.
Bb225774.methods(en-us,office.12).gif Search SEARCH and SEARCHB locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.
Bb225774.methods(en-us,office.12).gif SearchB SEARCH and SEARCHB locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.
Bb225774.methods(en-us,office.12).gif SeriesSum Returns the sum of a power series based on the formula:
Bb225774.methods(en-us,office.12).gif Sinh Returns the hyperbolic sine of a number.
Bb225774.methods(en-us,office.12).gif Skew Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean. Positive skewness indicates a distribution with an asymmetric tail extending toward more positive values. Negative skewness indicates a distribution with an asymmetric tail extending toward more negative values.
Bb225774.methods(en-us,office.12).gif Sln Returns the straight-line depreciation of an asset for one period.
Bb225774.methods(en-us,office.12).gif Slope Returns the slope of the linear regression line through data points in known_y's and known_x's. The slope is the vertical distance divided by the horizontal distance between any two points on the line, which is the rate of change along the regression line.
Bb225774.methods(en-us,office.12).gif Small Returns the k-th smallest value in a data set. Use this function to return values with a particular relative standing in a data set.
Bb225774.methods(en-us,office.12).gif SqrtPi Returns the square root of (number * pi).
Bb225774.methods(en-us,office.12).gif Standardize Returns a normalized value from a distribution characterized by mean and standard_dev.
Bb225774.methods(en-us,office.12).gif StDev Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).
Bb225774.methods(en-us,office.12).gif StDevP Calculates standard deviation based on the entire population given as arguments. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).
Bb225774.methods(en-us,office.12).gif StEyx Returns the standard error of the predicted y-value for each x in the regression. The standard error is a measure of the amount of error in the prediction of y for an individual x.
Bb225774.methods(en-us,office.12).gif Substitute Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.
Bb225774.methods(en-us,office.12).gif Subtotal Creates subtotals.
Bb225774.methods(en-us,office.12).gif Sum Adds all the numbers in a range of cells.
Bb225774.methods(en-us,office.12).gif SumIf Adds the cells specified by a given criteria.
Bb225774.methods(en-us,office.12).gif SumIfs Adds the cells in a range that meet multiple criteria.
Bb225774.methods(en-us,office.12).gif SumProduct Multiplies corresponding components in the given arrays, and returns the sum of those products.
Bb225774.methods(en-us,office.12).gif SumSq Returns the sum of the squares of the arguments.
Bb225774.methods(en-us,office.12).gif SumX2MY2 Returns the sum of the difference of squares of corresponding values in two arrays.
Bb225774.methods(en-us,office.12).gif SumX2PY2 Returns the sum of the sum of squares of corresponding values in two arrays. The sum of the sum of squares is a common term in many statistical calculations.
Bb225774.methods(en-us,office.12).gif SumXMY2 Returns the sum of squares of differences of corresponding values in two arrays.
Bb225774.methods(en-us,office.12).gif Syd Returns the sum-of-years' digits depreciation of an asset for a specified period.
Bb225774.methods(en-us,office.12).gif Tanh Returns the hyperbolic tangent of a number.
Bb225774.methods(en-us,office.12).gif TBillEq Returns the bond-equivalent yield for a Treasury bill.
Bb225774.methods(en-us,office.12).gif TBillPrice Returns the price per $100 face value for a Treasury bill.
Bb225774.methods(en-us,office.12).gif TBillYield Returns the yield for a Treasury bill.
Bb225774.methods(en-us,office.12).gif TDist Returns the Percentage Points (probability) for the Student t-distribution where a numeric value (x) is a calculated value of t for which the Percentage Points are to be computed. The t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution.
Bb225774.methods(en-us,office.12).gif Text Converts a value to text in a specific number format.
Bb225774.methods(en-us,office.12).gif TInv Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom.
Bb225774.methods(en-us,office.12).gif Transpose Returns a vertical range of cells as a horizontal range, or vice versa. TRANSPOSE must be entered as an array formula in a range that has the same number of rows and columns, respectively, as an array has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array on a worksheet.
Bb225774.methods(en-us,office.12).gif Trend Returns values along a linear trend. Fits a straight line (using the method of least squares) to the arrays known_y's and known_x's. Returns the y-values along that line for the array of new_x's that you specify.
Bb225774.methods(en-us,office.12).gif Trim Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.
Bb225774.methods(en-us,office.12).gif TrimMean Returns the mean of the interior of a data set. TRIMMEAN calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set. You can use this function when you wish to exclude outlying data from your analysis.
Bb225774.methods(en-us,office.12).gif TTest Returns the probability associated with a Student's t-Test. Use TTEST to determine whether two samples are likely to have come from the same two underlying populations that have the same mean.
Bb225774.methods(en-us,office.12).gif USDollar Converts a number to text format and applies a currency symbol. The name of the method (and the symbol that it applies) depends upon the language settings.
Bb225774.methods(en-us,office.12).gif Var Estimates variance based on a sample.
Bb225774.methods(en-us,office.12).gif VarP Calculates variance based on the entire population.
Bb225774.methods(en-us,office.12).gif Vdb Returns the depreciation of an asset for any period you specify, including partial periods, using the double-declining balance method or some other method you specify. VDB stands for variable declining balance.
Bb225774.methods(en-us,office.12).gif VLookup Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.
Bb225774.methods(en-us,office.12).gif Weekday Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.
Bb225774.methods(en-us,office.12).gif WeekNum Returns a number that indicates where the week falls numerically within a year.
Bb225774.methods(en-us,office.12).gif Weibull Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device's mean time to failure.
Bb225774.methods(en-us,office.12).gif WorkDay Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.
Bb225774.methods(en-us,office.12).gif Xirr Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function.
Bb225774.methods(en-us,office.12).gif Xnpv Returns the net present value for a schedule of cash flows that is not necessarily periodic. Read/write Double.
Bb225774.methods(en-us,office.12).gif YearFrac Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term.
Bb225774.methods(en-us,office.12).gif YieldDisc Returns the annual yield for a discounted security.
Bb225774.methods(en-us,office.12).gif YieldMat Returns the annual yield of a security that pays interest at maturity.
Bb225774.methods(en-us,office.12).gif ZTest Returns the one-tailed probability-value of a z-test. For a given hypothesized population mean, ZTEST returns the probability that the sample mean would be greater than the average of observations in the data set (array) — that is, the observed sample mean.

Properties

  Name Description
Bb225774.properties(en-us,office.12).gif Application When used without an object qualifier, this property returns an Application object that represents the Microsoft Excel application. When used with an object qualifier, this property returns an Application object that represents the creator of the specified object. You can use this property with an OLE Automation object to return the application of that object. Read-only.
Bb225774.properties(en-us,office.12).gif Creator Returns a 32-bit integer that indicates the application in which this object was created. Read-only Long.
Bb225774.properties(en-us,office.12).gif Parent Returns the parent object for the specified object. Read-only.