Programming Excel with VBA and .NET

The WorksheetFunction Object

Another useful top-level object to know about is the WorksheetFunction object. That object provides the functions from the Excel formula bar to your Visual Basic code. Some of these members are redundant with those provided by the VBA object library, however, others provide very useful (and advanced) analytical and statistical functions, as described in Table 4-8.

Table 4-8. Members of the WorksheetFunction object

Member	Description
Acos	Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is a number. The returned angle is given in radians in the range 0 to π.
Acosh	Returns the inverse hyperbolic cosine of a number.
And	The same as the Visual Basic And operator.
Asc	Converts double-byte characters to single-byte characters.
Asin	Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is the given number. The returned angle is given in radians in the range −π/2 to π/2.
Asinh	Returns the inverse hyperbolic sine of a number.
Atan2	Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates.
Atanh	Returns the inverse hyperbolic tangent of a number.
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.
Average	Returns the average (arithmetic mean) of the arguments.
BetaDist	Returns the beta cumulative distribution function.
BetaInv	Returns the inverse of the cumulative distribution function for a specified beta distribution.
BinomDist	Returns the individual term binomial distribution probability.
Ceiling	Returns a number rounded up, away from 0, to the nearest multiple of significance.
ChiDist	Returns the one-tailed probability of the chi-squared distribution.
ChiInv	Returns the inverse of the one-tailed probability of the chi-squared distribution.
ChiTest	Returns the test for independence. Determines whether hypothesized results are verified by an experiment.
Choose	Uses an index to return a value from the list of value arguments.
Clean	Removes all nonprintable characters from text.
Combin	Returns the number of combinations for a given number of items.
Confidence	Returns a value that you can use to construct a confidence interval for a population mean.
Correl	Returns the correlation coefficient of two cell ranges.
Cosh	Returns the hyperbolic cosine of a number.
Count	Counts the number of cells that contain numbers and also numbers within the list of arguments.
CountA	Counts the number of cells that are not empty and the values within the list of arguments.
CountBlank	Counts empty cells in a specified range of cells.
CountIf	Counts the number of cells within a range that meet the given criteria.
Covar	Returns covariance, the average of the products of deviations for each data point pair.
CritBinom	Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.
DAverage	Averages the values in a column of a list or database that match conditions you specify.
Days360	Returns the number of days between two dates based on a 360-day year (12 30-day months), which is used in some accounting calculations.
Db	Returns the depreciation of an asset for a specified period using the fixed-declining-balance method.
DCount	Counts the cells that contain numbers in a column of a list or database that match conditions you specify.
DCountA	Counts the nonblank cells in a column of a list or database that match specified conditions.
Ddb	Returns the depreciation of an asset for a specified period using the double-declining-balance method or some other method you specify.
Degrees	Converts radians into degrees.
DevSq	Returns the sum of squares of deviations of data points from their sample mean.
DGet	Extracts a single value from a column of a list or database that matches specified conditions.
DMax	Returns the largest number in a column of a list or database that matches specified conditions.
DMin	Returns the smallest number in a column of a list or database that matches specified conditions.
Dollar	Formats a number as currency using the local currency symbol.
DProduct	Multiplies the values in a column of a list or database that match conditions you specify.
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 specified conditions.
DStDevP	Calculates the standard deviation of a population based on the entire population, using the numbers in a column of a list or database that match specified conditions.
DSum	Adds the numbers in a column of a list or database that match specified conditions.
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 you specify.
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 specified conditions.
Even	Returns a number rounded up to the nearest even integer.
ExponDist	Returns the exponential distribution.
Fact	Returns the factorial of a number.
FDist	Returns the F probability distribution.
Find	Finds the location of one string within another (similar to Instr).
FindB	Finds the location of one double-byte string within another (similar to Instr).
FInv	Returns the inverse of the F probability distribution.
Fisher	Returns the Fisher transformation at x. This transformation produces a function that is normally distributed rather than skewed.
FisherInv	Returns the inverse of the Fisher transformation. Use this transformation when analyzing correlations between ranges or arrays of data.
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.
Floor	Rounds a number down, toward 0, to the nearest multiple of significance.
Forecast	Calculates, or predicts, a future value by using existing values.
Frequency	Calculates how often values occur within a range of values and then returns a vertical array of numbers.
FTest	Returns the result of an F-test. An F-test returns the one-tailed probability that the variances in array1 and array2 are not significantly different.
Fv	Returns the future value of an investment based on periodic, constant payments and a constant interest rate.
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.
GammaInv	Returns the inverse of the gamma cumulative distribution.
GammaLn	Returns the natural logarithm of the gamma function (x).
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.
Growth	Calculates predicted exponential growth by using existing data.
HarMean	Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the arithmetic mean of reciprocals.
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.
HypGeomDist	Returns the hypergeometric distribution. Hypergeometric distribution is the probability of a given number of sample successes, given the sample size, population successes, and population size.
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.
Ipmt	Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
Irr	Returns the internal rate of return for a series of cash flows represented by the numbers in values.
IsErr	Returns True if a cell contains an error other than #N/A.
IsError	Returns True if a cell contains an error.
IsLogical	Returns True if a cell contains a Boolean value.
IsNA	Returns True if a cell contains the #N/A error value.
IsNonText	Returns True if a cell does not contain text.
IsNumber	Returns True if a cell contains a numeric value.
Ispmt	Calculates the interest paid during a specific period of an investment.
IsText	Returns True if a cell contains a string.
Kurt	Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness or flatness of a distribution compared with the normal distribution.
Large	Returns the kth largest value in a data set. You can use this function to select a value based on its relative standing.
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.
Ln	Returns the natural logarithm of a number.
Log	Returns the logarithm of a number to the specified base.
Log10	Returns the base-10 logarithm of a number.
LogEst	Calculates an exponential curve that fits your data and returns an array of values that describes the curve. Returns an array of values.
LogInv	Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard_dev.
LogNormDist	Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard_dev.
Lookup	Finds a value in an array and returns that value. For two-dimensional arrays, it is better to use HLookup or VLookup.
Match	Returns the relative position of an item in an array that matches a specified value in a specified order.
Max	Returns the largest value in a set of values.
MDeterm	Returns the matrix determinant of an array.
Median	Returns the median of the given numbers.
Min	Returns the smallest number in a set of values.
MInverse	Returns the inverse matrix for the matrix stored in an array.
MIrr	Returns the modified internal rate of return for a series of periodic cash flows.
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.
Mode	Returns the most frequently occurring, or repetitive, value in an array or range of data.
NegBinomDist	Returns the negative binomial distribution. NegBinomDist returns the probability that there will be number_f failures before the number_sth success, when the constant probability of a success is probability_s.
NormDist	Returns the normal distribution for the specified mean and standard deviation. This function has a wide range of applications in statistics, including hypothesis testing.
NormInv	Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
NormSDist	Returns the standard normal cumulative distribution function. The distribution has a mean of 0 and a standard deviation of 1. Use this function in place of a table of standard normal curve areas.
NormSInv	Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of 0 and a standard deviation of 1.
NPer	Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
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).
Odd	Returns a number rounded up to the nearest odd integer.
Or	The same as the Visual Basic Or operator.
Parent	Returns the parent object for the specified object.
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.
Percentile	Returns the kth 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.
PercentRank	Returns the rank of a value in a data set as a percentage of the data set.
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 in which internal order is significant.
Phonetic	Extracts the phonetic (furigana) characters from a text string.
Pi	Returns the number 3.14159265358979, the mathematical constant π, accurate to 15 digits.
Pmt	Calculates the payment for a loan based on constant payments and a constant interest rate.
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 one minute.
Power	Returns the result of a number raised to a power.
Ppmt	Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
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.
Product	Multiplies all the numbers given as arguments and returns the product.
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.
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.
Quartile	Returns the quartile of a data set. Quartiles often are used in sales and survey data to divide populations into groups.
Radians	Converts degrees to radians.
Rank	Returns the rank of a number in a list of numbers. If you sort a list, the rank of the number is its position in the list.
Rate	Returns the interest rate per period of an annuity.
Replace	Replaces part of one string with another.
ReplaceB	Replaces part of one double-byte string with another.
Rept	Repeats text a given number of times.
Roman	Formats an Arabic numeral as a Roman numeral.
Round	Rounds a number to a specified number of digits.
RoundDown	Rounds a number down, toward 0.
RoundUp	Rounds a number up, away from 0.
RSq	Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's.
RTD	Retrieves real-time data from a program that supports automation.
Search	Finds the location of one string within another (similar to Instr).
SearchB	Finds the location of one double-byte string within another (similar to Instr).
Sinh	Returns the hyperbolic sine of a number.
Skew	Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean.
Sln	Returns the straight-line depreciation of an asset for one period.
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.
Small	Returns the kth smallest value in a data set. Use this function to return values with a particular relative standing in a data set.
Standardize	Returns a normalized value from a distribution characterized by mean and standard deviation.
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).
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).
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.
Substitute	Substitutes new_text for old_text in a text string.
Subtotal	Returns a subtotal in a list or database.
Sum	Adds all the numbers in a range of cells.
SumIf	Adds cells that meet specified criteria.
SumProduct	Multiplies corresponding components in the given arrays and returns the sum of those products.
SumSq	Returns the sum of the squares of the arguments.
SumX2MY2	Returns the sum of the difference of squares of corresponding values in two arrays.
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.
SumXMY2	Returns the sum of squares of differences of corresponding values in two arrays.
Syd	Returns the sum-of-years digits depreciation of an asset for a specified period.
Tanh	Returns the hyperbolic tangent of a number.
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.
Text	Converts a value to text in a specific number format.
TInv	Returns the t-value of the student t-distribution as a function of the probability and the degrees of freedom.
Transpose	Returns a vertical range of cells as a horizontal range or vice versa.
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.
Trim	Removes all spaces from text except for single spaces between words.
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.
TTest	Returns the probability associated with a student 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.
USDollar	Formats a number as U.S. currency.
Var	Estimates variance based on a sample.
VarP	Calculates variance based on the entire population.
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.
VLookup	Searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table.
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.
Weibull	Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device’s mean time to failure.
ZTest	Returns the one-tailed probability-value of a z-test. For a given hypothesized population mean, that is, the observed sample mean.

There is no Help for members in Table 4-8 from Visual Basic, but you can look up these functions in the Excel Help file as shown in Figure 4-21.

Figure 4-21. Use Excel Help to find information on the WorksheetFunction members