This reference lists 37 Excel compatibility functions, ordered by frequency of use, with high-frequency functions marked ⭐. Each function includes its syntax and an example.
| Function | Description | Example |
|---|---|---|
BETADIST |
Returns the beta cumulative distribution function | =BETADIST(0.2,3,5,0,1) — Returns the beta cumulative probability, used in probability and risk analysis |
BETAINV |
Returns the inverse of the cumulative distribution function for a specified beta distribution | =BETAINV(0.2,3,5) — Returns the beta distribution quantile for a given probability |
BINOMDIST |
Returns the individual term binomial distribution probability | =BINOMDIST(6,10,0.5,FALSE) — Returns the probability of exactly 6 successes in 10 trials with p = 0.5 |
CHIDIST |
Returns the right-tailed probability of the chi-squared distribution | =CHIDIST(20,10) — Returns the right-tailed chi-squared probability for chi-squared tests |
CHIINV |
Returns the inverse of the right-tailed probability of the chi-squared distribution | =CHIINV(0.95,10) — Returns the inverse chi-squared value, used to find test critical values |
CHITEST |
Returns the test for independence | =CHITEST(A1:A10,B1:B10) — Returns the chi-squared test result for observed vs expected values (goodness of fit) |
CONFIDENCE |
Returns the confidence interval for a population mean, using a normal distribution | =CONFIDENCE(0.05,10,100) — Returns the width of the confidence interval for the mean at 95% confidence |
COVAR |
Returns population covariance | =COVAR(A1:A10,B1:B10) — Returns the covariance of two data sets, measuring how they move together |
CRITBINOM |
Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value | =CRITBINOM(10,0.5,0.9) — Returns the smallest number of successes whose cumulative probability reaches a value |
EXPONDIST |
Returns the exponential distribution | =EXPONDIST(0.2,10,TRUE) — Returns the exponential cumulative probability or density, used in reliability analysis |
FDIST |
Returns the (right-tailed) F probability distribution | =FDIST(5,10,10) — Returns the right-tailed F probability, used in ANOVA |
FINV |
Returns the inverse of the (right-tailed) F probability distribution | =FINV(0.95,10,10) — Returns the inverse F value for computing F-test critical values |
FTEST |
Returns the result of an F-test | =FTEST(A1:A10,B1:B10) — Returns the F-test p-value for two data sets, checking equal variances |
GAMMADIST |
Returns the gamma distribution | =GAMMADIST(10,2,3,TRUE) — Returns the gamma cumulative probability or density |
GAMMAINV |
Returns the inverse of the gamma cumulative distribution | =GAMMAINV(0.05,2,3) — Returns the inverse gamma distribution value |
HYPGEOMDIST |
Returns the hypergeometric distribution | =HYPGEOMDIST(2,5,10,100,TRUE) — Returns the probability of successes when sampling from a finite population, used in QC |
LOGINV |
Returns the inverse of the lognormal cumulative distribution function | =LOGINV(0.95,3,4) — Returns the inverse of the lognormal distribution |
LOGNORMDIST |
Returns the cumulative lognormal distribution | =LOGNORMDIST(20,3,4) — Returns the lognormal cumulative probability |
MODE |
Returns the most common value in a data set | =MODE(A1:A10) — Returns the most frequently occurring value in a range |
NEGBINOMDIST |
Returns the negative binomial distribution | =NEGBINOMDIST(2,5,0.5) — Returns the negative binomial probability — failures before the r-th success |
NORMDIST |
Returns the normal cumulative distribution | =NORMDIST(85,70,10,TRUE) — Returns the normal cumulative probability with mean 70 and standard deviation 10 |
NORMINV |
Returns the inverse of the normal cumulative distribution | =NORMINV(0.95,0,1) — Returns the 95% quantile of the standard normal distribution (about 1.645) |
NORMSDIST |
Returns the standard normal cumulative distribution | =NORMSDIST(1.96) — Returns the cumulative probability of the standard normal distribution (mean 0, sd 1) |
NORMSINV |
Returns the inverse of the standard normal cumulative distribution | =NORMSINV(0.975) — Returns the 97.5% quantile of the standard normal distribution (about 1.96) |
PERCENTILE |
Returns the k-th percentile of values in a data set (Includes 0 and 1) | =PERCENTILE(A1:A10,0.5) — Returns the median of the data (the 50th percentile) |
PERCENTRANK |
Returns the percentage rank of a value in a data set (Includes 0 and 1) | =PERCENTRANK(A1:A10,A5) — Returns the percentile rank of A5 within the data range |
POISSON |
Returns the Poisson distribution | =POISSON(5,10,FALSE) — Returns the probability of exactly 5 events when lambda = 10 |
QUARTILE |
Returns the quartile of a data set (Includes 0 and 1) | =QUARTILE(A1:A10,1) — Returns the first quartile of the data (the 25th percentile) |
RANK |
Returns the rank of a number in a list of numbers | =RANK(A1,A:A,0) — Returns the rank of A1 in column A (0 = descending, 1 = ascending) |
STDEV |
Estimates standard deviation based on a sample | =STDEV(A1:A10) — Calculates the standard deviation from sample data (using n - 1) |
STDEVP |
Calculates standard deviation based on the entire population | =STDEVP(A1:A10) — Calculates the standard deviation from the full population (using n) |
TDIST |
Returns the probability for the Student t-distribution | =TDIST(2,10,2) — Returns the two-tailed t probability with 10 degrees of freedom |
TINV |
Returns the inverse of the probability for the Student t-distribution (two-tailed) | =TINV(0.05,10) — Returns the two-tailed t critical value, used in t-tests |
VAR |
Estimates variance based on a sample | =VAR(A1:A10) — Calculates the variance from sample data (using n - 1) |
VARP |
Calculates variance based on the entire population | =VARP(A1:A10) — Calculates the variance from the full population (using n) |
WEIBULL |
Returns the Weibull distribution | =WEIBULL(105,20,100,TRUE) — Returns the Weibull cumulative probability or density, used in reliability analysis |
ZTEST |
Returns the one-tailed probability-value of a z-test | =ZTEST(A1:A10,50) — Tests whether the mean exceeds the hypothesized 50, returning the one-tailed p-value |
From Online Toolbox (www.vba.net)