61 New Functions in Excel 2010

By J. Carlton Collins
 

In Excel 2010, Microsoft added 61 new functions and improved 19 others. This page contains a summary of those new functions and improvements. CPAs will notice that many of these functions are more scientific in nature, or are geared more towards engineers and statisticians. However, there are a handful of new functions that apply to the CPA function - particularly when it comes to data analysis. I've place a J next to those new functions which I consider more useful in standard CPA applications.  
 

1.      AGGREGATE - allows you to handle errors and other issues in ranges referenced by aggregate function such as MAX, SMALL, SUM, AVERAGE,…J

2.      BETA.DIST - improved version of BETADIST - Returns the beta distribution

3.      BETA.INV - improved version of BETAINV - Returns the inverse of the beta cumulative probability density function (BETA.DIST)

4.      BINOM.DIST - improved version of BINOMDIST - Returns the individual term binomial distribution probability. Use BINOM.DIST in problems with a fixed number of tests or trials, when the outcomes of any trial are only success or failure, when trials are independent, and when the probability of success is constant throughout the experiment. 

5.      BINOM.INV - improved version of CRITBNOM - Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value

6.      CEILING.PRECISE - Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up. However, if the number or the significance is zero, zero is returned. J

7.      CHISQ.DIST - Returns the left-tailed probability of the chi-squared distribution.

8.      CHISQ.DIST.RT - improved version of CHISQDIST - Returns the right-tailed probability of the chi-squared distribution.

9.      CHISQ.INV - Returns the inverse of the left-tailed probability of the chi-squared distribution.

10.  CHISQ.INV.RT - improved version of CHIINV - Returns the inverse of the right-tailed probability of the chi-squared distribution.

11.  CHISQ.TEST - improved version of CHITEST - Returns the test for independence. CHISQ.TEST returns the value from the chi-squared (χ2) distribution for the statistic and the appropriate degrees of freedom. You can use χ2 tests to determine whether hypothesized results are verified by an experiment.

12.  CONFIDENCE.NORM - improved version of CONFIDENCE - Returns the confidence interval for a population mean, using a normal distribution. J

13.  CONFIDENCE.T - Returns the confidence interval for a population mean, using a Students T's distribution.

14.  COVARIANCE.P - improved version of COVAR - Returns the population covariance, the average of the products deviations for each data point pair in two data sets

15.  COVARIANCE.S - Returns the sample covariance, the average of the products deviations for each data point pair in two data sets

16.  ERF.PRECISE - Returns the error fucntion integrated between the lower limit and infinity. The ERF function works for defined lower and upper set of limits.

17.  ERFC.PRECISE - Returns the complementary ERF function integrated between x and infinity, ERFC returns the results integrated between a lower and upper limit.

18.  EXPON.DIST - imporoved version of EXPONDIST - Returns the exponential distribution. Use EXPON.DIST to model the time between events, such as how long an automated bank teller takes to deliver cash.

19.  F.DIST - Returns the (left-tailed) F probability distribution. You can use this function to determine whether two data sets have different degrees of diversity.

20.  F.DIST.RT - improved version of FDIST - Returns the (right-tailed) F probability distribution. You can use this function to determine whether two data sets have different degrees of diversity.

21.  F.INV - Returns the inverse of the (left-tailed) F probability distribution.

22.  F.INV.RT - inproved version of FINV - Returns the inverse of the (right-tailed) F probability distribution. If p = F.DIST.RT(x,...), then F.INV.RT(p,...) = x.

23.  F.TEST - improved version of FTEST - Returns the result of an F-test, the two-tailed probability that the variances in array1 and array2 are not significantly different.

24.  FLOOR.PRECISE - Returns a number that is rounded down to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded down. However, if the number or the significance is zero, zero is returned. J

25.  GAMMA.DIST - improved version of 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.

26.  GAMMA.INV - improved version of GAMMAINV - Returns the inverse of the gamma cumulative distribution. If p = GAMMA.DIST(x,...), then GAMMA.INV(p,...) = x.

27.  GAMMALN.PRECISE - improved version of GAMMALN - Returns the natural logarithm of the gamma function, Γ(x) as calculated with the GAMMA.DIST function.

28.  HYPGEOM.DIST - improved version of HYPGEOMDIST - returns the probability of a given number of sample successes, given the sample size, population successes, and population size. Use HYPGEOM.DIST 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.

29.  ISO.CEILING - Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up. However, if the number or the significance is zero, zero is returned.

30.  LOGNORM.DIST - improved version of LOGNORMDIST - Returns the 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.

31.  LOGNORM.INV - improved version of LOGINV - Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters Mean and Standard_dev. If p = LOGNORM.DIST(x,...) then LOGNORM.INV(p,...) = x. Use the lognormal distribution to analyze logarithmically transformed data.

32.  MODE.MULT - Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data. For horizontal arrays, use TRANSPOSE(MODE.MULT(number1,number2,...)).

33.  MODE.SNGL - Improved version of MODE - returns the most frequently occurring value in a range.

34.  NEGBINOM.DIST - improved versin of NEGBINOMDIST - Returns the negative binomial distribution, the probability that there will be Number_f failures before the Number_s-th success, with Probability_s probability of a success.  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.

35.  NETWORKDAYS.INTL- Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays

36.  NORM.DIST - improved version of 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.

37.  NORM.INV - improved version of NORMINV - Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.

38.  NORM.S.DIST - improved version of NORMSDIST - Returns the standard normal distribution (has a mean of zero and a standard deviation of one). Use this function in place of a table of standard normal curve areas.

39.  NORM.S.INV- improved version of NORMSINV - Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one.

40.  PERCENTILE.EXC - Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive. J

41.  PERCENTILE.INC - improved version of PERCENTILE - Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive. 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. J

42.  PERCENTRANK.EXC - Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set. J

43.  PERCENTRANK.INC - improved version of PERCENTRANK - Returns the rank of a value in a data set as a percentage (0..1, inclusive) 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.INC to evaluate the standing of an aptitude test score among all scores for the test. J

44.  POISSON.DIST- improved version of 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.

45.  QUARTILE.EXC - Returns the quartile of the data set, based on percentile values from 0..1, exclusive. J

46.  QUARTILE.INC - improved version of QUARTILE - Returns the quartile of a data set, based on percentile values from 0..1, inclusive. Quartiles often are used in sales and survey data to divide populations into groups. For example, you can use QUARTILE.INC to find the top 25 percent of incomes in a population. J

47.  RANK.AVG - Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the average rank is returned. J

48.  RANK.EQ- improved version of RANK - Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. If you were to sort the list, the rank of the number would be its position. J

49.  STDEV.P - improved version of STDEVP - Calculates standard deviation based on the entire population given as arguments (ignores logical values and text).

50.  STDEV.S - improved version of STDEV - Estimates standard deviation based on a sample (ignores logical values and text in the sample).

51.  T.DIST- Returns the Student's t-distribution. 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.

52.  T.DIST.2T - Returns the two-tailed Student's t-distribution. The Student's 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.

53.  T.DIST.RT - Improved version of TDIST - Returns the right-tailed Student's t-distribution. 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.

54.  T.INV - Returns the left-tailed inverse of the Student's t-distribution.

55.  T.INV.2T - improved verion of TINV - Returns the two-tailed inverse of the Student's t-distribution.

56.  T.TEST - imporved version of TTEST - Returns the probability associated with a Student's t-Test. Use T.TEST to determine whether two samples are likely to have come from the same two underlying populations that have the same mean.

57.  VAR.P - improved version of VARP - Calculates variance based on the entire population (ignores logical values and text in the population).

58.  VAR.S - improved version of VAR - Estimates variance based on a sample (ignores logical values and text in the sample).

59.  WEIBULL.DIST - improved version of WEIBULL - Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device's mean time to failure.

60.  WORKDAY.INTL - Returns the serial number of the date before or after a specified number of workdays with custom weekend parameters. Weekend parameters indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays. J

61.  Z.TEST - improved version of ZTEST - Returns the one-tailed P-value of a z-test. For a given hypothesized population mean, x, Z.TEST 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.