With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

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`

`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_s`th 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`

`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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

No credit card required