# Compatibility Function in Excel (34 Practical Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I am going to share with you 34 practical examples of compatibility function in Excel. You can easily apply these functions to perform a wide range of statistical calculations inside an Excel worksheet. To achieve this task, we will also see some useful features that might come in handy in many other Excel-related tasks.

## Compatibility Function in Excel: 34 Practical Examples

Function Objective

This compatibility function in Excel gives back the beta probability density as a cumulative result.

Syntax

`BETADIST(x,alpha,beta,[A],[B])`

Arguments Explanation

Argument Required/Optional Explanation
x Required The value between A and B at which to evaluate the function
Alpha Required A parameter of the distribution
Beta Required A parameter of the distribution
A Optional A lower bound to the interval of x
B Optional An upper bound to the interval of x

Return Parameter

The beta probability density. ### 2. The BETAINV Function

Function Objective

We can get the inverse cumulative value of the beta probability density from this compatibility function in Excel.

Syntax

`BETAINV (probability,alpha,beta,[A],[B])`

Arguments Explanation

Argument Required/Optional Explanation
Probability Required A probability associated with the beta distribution
Alpha Required A parameter of the distribution
Beta Required A parameter of the distribution
A Optional A lower bound to the interval of x
B Optional An upper bound to the interval of x

Return Parameter

Inverse cumulative value of the beta probability density.

Use of BETAINV Function ### 3. The BINOMDIST Function

Function Objective

As the name suggests, this function works with the binomial distribution probability and gives individual terms.

Syntax

`BINOMDIST (number_s,trials,probability_s,cumulative)`

Arguments Explanation

Argument Required/Optional Explanation
Number_s Required The number of successful incidents in trials
Trials Required Quantity of independent trials
Probability_s Required The probability of successful incidents on each trial
Cumulative Required A logical value that determines the form of the function.
• The cumulative distribution function—which represents the likelihood that there would be no more than number_s successes—is returned by BINOMDIST if cumulative is TRUE.
• The probability that there will be number_s successes is the probability mass function that BINOMDIST returns if cumulative is FALSE.

Return Parameter

Individual terms of the binomial distribution.

Use of BINOMDIST Function ### 4. The CEILING Function

Function Objective

This compatibility function in Excel can round a number to the nearest significant multiple which is also away from zero.

Syntax

`CEILING (number, significance)`

Arguments Explanation

Argument Required/Optional Explanation
Number Required The value you want to round
Significance Required The multiple to which the Number is to be rounded

Return Parameter

Rounded number.

Use of CEILING Function ### 5. The CHIDIST Function

Function Objective

This function works with the chi-squared data distribution and returns the right-tailed probability value.

Syntax

`CHIDIST(x,deg_freedom)`

Arguments Explanation

Argument Required/Optional Explanation
X Required The amount you wish to use to measure the distribution’s performance
Deg_freedom Required The number of degrees of freedom

Return Parameter

The right-tailed probability value

Use of CHIDIST Function ### 6. The CHIINV Function

Function Objective

This function also works with the chi-squared distribution but instead gives the inverse of the right-tailed probability.

Syntax

`CHIINV (probability,deg_freedom)`

Arguments Explanation

Argument Required/Optional Explanation
Probability Required A probability associated with the chi-squared distribution
Deg_freedom Required The number of degrees of freedom

Return Parameter

The inverse of the right-tailed probability

Use of CHIINV Function ### 7. The CHITEST Function

Function Objective

This function is mainly helpful to check for independence.

Syntax

`CHITEST (actual_range,expected_range)`

Arguments Explanation

Argument Required/Optional Explanation
Actual_range Required The set of data containing observations that can be compared against predictions
Expected_range Required The set of data that shows how much row totals and column totals add up to the overall sum

Return Parameter

Independence test result

Use of CHITEST Function Read More: How to Use Format Function in Excel

### 8. The CONFIDENCE Function

Function Objective

This function uses a normal distribution and returns the confidence interval for a mean population.

Syntax

`CONFIDENCE (alpha,standard_dev,size)`

Arguments Explanation

Argument Required/Optional Explanation
Alpha Required The significance level used to compute the confidence level. The confidence level equals 00*(1 – alpha)%, or in other words, an alpha of 0.05 indicates a 95 percent confidence level
Standard_dev Required The population standard deviation for the data range and is assumed to be known
Size Required The sample size

Return Parameter

The confidence interval for a mean population

Use of CONFIDENCE Function ### 9. The COVAR Function

Function Objective

This function works with covariance which can determine the relationship between two sets of data.

Syntax

`COVAR (array1, array2)`

Arguments Explanation

Argument Required/Optional Explanation
Array1 Required The first cell range of integers
Array2 Required The second cell range of integers

Return Parameter

The relationship between two sets of data

Use of COVAR Function ### 10. The CRITBINOM Function

Function Objective

The function returns the least value for which a given criterion value is larger than or equal to the cumulative binomial distribution.

Syntax

`CRITBINOM (trials,probability_s,alpha)`

Arguments Explanation

Argument Required/Optional Explanation
Trials Required The number of Bernoulli trials
Probability_s Required The probability of success on each trial
Alpha Required The criterion value

Return Parameter

The least value for which a given criterion value is larger than or equal to the cumulative binomial distribution

Use of CRITBINOM Function ### 11. The EXPONDIST Function

Function Objective

The formula of this compatibility function in Excel returns an exponential distribution.

Syntax

`EXPONDIST(x,lambda,cumulative)`

Arguments Explanation

Argument Required/Optional Explanation
X Required The value of the function
Cumulative Required a logical number that specifies the exponential function’s form to use

EXPONDIST returns the cumulative distribution function if cumulative is TRUE.

EXPONDIST returns the probability density function if cumulative is FALSE.

Return Parameter

An exponential distribution

Use of EXPONDIST Function ### 12. The FDIST Function

Function Objective

If two Excel data sets have differing levels of diversity, we can use this compatibility function to compare them.

Syntax

`FDIST(x,deg_freedom1,deg_freedom2)`

Arguments Explanation

Argument Required/Optional Explanation
X Required The value at which to evaluate the function.
Deg_freedom1 Required The numerator degrees of freedom
Deg_freedom2 Required The denominator degrees of freedom

Return Parameter

F probability value

Use of FDIST Function ### 13. The FINV Function

Function Objective

The function outputs the (right-tailed) F probability distribution’s inverse.

Syntax

`FINV (probability,deg_freedom1,deg_freedom2)`

Arguments Explanation

Argument Required/Optional Explanation
Probability Required a likelihood corresponding to the F cumulative distribution
Deg_freedom1 Required The numerator degrees of freedom
Deg_freedom2 Required The denominator degrees of freedom

Return Parameter

F probability distribution’s inverse

Use of FINV Function ### 14. The FLOOR Function

Function Objective

The FLOOR function reduces the integer to the nearest multiple of significance before rounding it down to zero.

Syntax

`FLOOR (number, significance)`

Arguments Explanation

Argument Required/Optional Explanation
Number Required The numeric value you want to round
Significance Required The multiple to which you want to round

Return Parameter

Rounded number

Use of FLOOR Function ### 15. The FTEST Function

Function Objective

The function returns the F-test’s outcome.

Syntax

`FTEST (array1, array2)`

Arguments Explanation

Argument Required/Optional Explanation
Array1 Required The first array or range of data
Array2 Required The second array or range of data

Return Parameter

The F-test’s outcome

Use of FTEST Function Function Objective

The function outputs the gamma distribution of a dataset.

Syntax

`GAMMADIST(x,alpha,beta,cumulative)`

Arguments Explanation

Argument Required/Optional Explanation
X Required Your desired value for evaluating the distribution
Alpha Required A parameter to the distribution.
Beta Required A parameter to the distribution.

The typical gamma distribution is returned by GAMMADIST if beta = 1

Cumulative Required A logical value that determines the form of the function.

The cumulative distribution function is what GAMMADIST returns if cumulative is TRUE.

GAMMADIST returns the probability density function if cumulative is FALSE.

Return Parameter

The gamma distribution of a dataset ### 17. The GAMMAINV Function

Function Objective

The formula returns the inverse gamma cumulative distribution using the GAMMAINV compatibility function in Excel.

Syntax

`GAMMAINV (probability,alpha,beta)`

Arguments Explanation

Argument Required/Optional Explanation
Probability Required The probability associated with the gamma distribution
Alpha Required A parameter of the distribution
Beta Required A parameter to the distribution.

GAMMAINV yields the common gamma distribution if beta = 1.

Return Parameter

The inverse gamma cumulative distribution

Use of GAMMAINV Function ### 18. The HYPGEOMDIST Function

Function Objective

This function gives back the probability of success for a sample population.

Syntax

`HYPGEOMDIST (sample_s,number_sample,population_s,number_pop)`

Arguments Explanation

Argument Required/Optional Explanation
Sample_s Required The number of successful events in the sample
Number_sample Required The sample size
Population_s Required The number of successes in the population
Number_pop Required The population size

Return Parameter

The probability of success for a sample population

Use of HYPGEOMDIST Function Function Objective

The formula returns the lognormal cumulative distribution function’s inverse.

Syntax

`LOGINV (probability, mean, standard_dev)`

Arguments Explanation

Argument Required/Optional Explanation
Probability Required A probability associated with the lognormal distribution.
Mean Required The mean of ln(x).
Standard_dev Required The standard deviation of ln(x).

Return Parameter

The lognormal cumulative distribution function’s inverse ### 20. The LOGNORMDIST Function

Function Objective

The method returns the cumulative lognormal distribution of x, where ln(x) is a function having a normal distribution with mean and standard deviation as its parameters.

Syntax

`LOGNORMDIST(x,mean,standard_dev)`

Arguments Explanation

Argument Required/Optional Explanation
X Required The value at which to evaluate the function.
Mean Required The mean of ln(x).
Standard_dev Required The standard deviation of ln(x).

Return Parameter

The cumulative lognormal distribution of x

Use of LOGNORMDIST Function ### 21. The MODE Function

Function Objective

This function gives back the most frequent value in a data array.

Syntax

`MODE (number1,[number2],...)`

Arguments Explanation

Argument Required/Optional Explanation
Number1 Required The first number argument that you wish to use to determine the mode
Number2 Optional The range of parameters (2–255) for which you want to determine the mode

Instead of using arguments that are separated by commas, you can either use a single array or a reference to an array.

Return Parameter

The most frequent value in a data array

Use of MODE Function Read More: What Are Excel Function Arguments

### 22. The NEGBINOMDIST Function

Function Objective

This function gives the negative binomial distribution of a dataset.

Syntax

`NEGBINOMDIST (number_f,number_s,probability_s)`

Arguments Explanation

Argument Required/Optional Explanation
Number_f Required The number of failures.
Number_s Required The threshold number of successes.
Probability_s Required The probability of a success.

Return Parameter

The negative binomial distribution of a dataset

Use of NEGBINOMDIST Function ### 23. The NORMSDIST Function

Function Objective

If we have a specified mean and a standard deviation, this function will give us the normal distribution.

Syntax

`NORMDIST(x,mean,standard_dev,cumulative)`

Arguments Explanation

Argument Required/Optional Explanation
X Required The value for which you want the distribution.
Mean Required The arithmetic mean of the distribution.
Standard_dev Required The standard deviation of the distribution.
Cumulative Required a logical value that establishes the function’s shape.

NORMDIST returns the cumulative distribution function if cumulative is TRUE.

The probability mass function is returned by NORMDIST if cumulative is FALSE.

Return Parameter

The normal distribution

Use of NORMSDIST Function ### 24. The NORMINV Function

Function Objective

This function is useful to find out the inverse of the normal cumulative distribution after specifying the mean and standard deviation.

Syntax

`NORMINV (probability,mean,standard_dev)`

Arguments Explanation

Argument Required/Optional Explanation
Probability Required A probability associated with the beta distribution
Mean Required A parameter of the distribution
Standard_dev Required A parameter of the distribution

Return Parameter

The inverse of the normal cumulative distribution

Use of NORMINV Function ### 25. The PERCENTILE Function

Function Objective

This function gives the percentile of values at the k-th position.

Syntax

`PERCENTILE (array,k)`

Arguments Explanation

Argument Required/Optional Explanation
Array Required the collection of data that characterizes relative standing.
K Required The percentile value in the range 0…1, inclusive.

Return Parameter

The percentile of values at the k-th position

Use of PERCENTILE Function ### 26. The PERCENTRANK Function

Function Objective

We can use this compatibility function to easily determine the relative standing of a value within a data array in Excel.

Syntax

`PERCENTRANK (array,x,[significance])`

Arguments Explanation

Argument Required/Optional Explanation
Array Required the collection of data in an array or range with numerical values that indicate relative standing.
X Required the value for which you seek rank information.
Significance Optional a number that indicates how many significant digits there are in the percentage result that was returned.

PERCENTRANK employs three digits if it is removed (0.xxx).

Return Parameter

The relative standing of a value within a data array

Use of PERCENTRANK Function ### 27. The POISSON Function

Function Objective

This function gives back the Poisson distribution to predict the number of events within a certain time period.

Syntax

`POISSON(x,mean,cumulative)`

Arguments Explanation

Argument Required/Optional Explanation
X Required The number of events.
Mean Required The expected numeric value.
Cumulative Required a logical value that establishes how the probability distribution is represented when it is returned.

If cumulative is TRUE, POISSON will return the cumulative Poisson probability that x random events will occur, with zero being the most likely outcome.

If cumulative returns FALSE, POISSON delivers the Poisson probability mass function predicting that exactly x events will occur.

Return Parameter

The Poisson distribution

Use of POISSON Function ### 28. The RANK Function

Function Objective

We can use this function to get the rank of a number from a collection of numbers.

Syntax

`RANK (number,ref,[order])`

Arguments Explanation

Argument Required/Optional Explanation
Number Required The number whose rank you want to find
Ref Required a collection of numbers or a reference to a list of numbers. In the ref, non-numeric values are disregarded.
Order Optional A number specifying how to rank number.

Microsoft Excel ranks numbers as though ref were a list arranged in decreasing order if the order is 0 (zero) or omitted.

Microsoft Excel ranks numbers as though ref were a list arranged in ascending order if the order is any nonzero value.

Return Parameter

The rank of a number from a collection of numbers

Use of RANK Function ### 29. The STDEV Function

Function Objective

This compatibility function in Excel is very useful for determining the standard deviation value from a sample that we provide.

Syntax

`STDEV (number1,[number2],...)`

Arguments Explanation

Argument Required/Optional Explanation
Number1 Required the first number argument representing a population sample.
Number2 Required Arguments in the range of 2 to 255 represent a sample of a population.

Instead of using arguments that are separated by commas, you can either use a single array or a reference to an array.

Return Parameter

The standard deviation value from a sample

Use of STDEV Function ### 30. The TINV Function

Function Objective

This function finds the two-tailed inverse value of the student’s t-distribution data.

Syntax

`TINV (probability,deg_freedom)`

Arguments Explanation

Argument Required/Optional Explanation
Probability Required The probability associated with the two-tailed Student’s t-distribution.
Deg_freedom Required The probability associated with the two-tailed Student’s t-distribution.

Return Parameter

The two-tailed inverse value of the student’s t-distribution data

Use of TINV Function ### 31. The TTEST Function

Function Objective

The TTEST function gives back the probability related to a student’s t-test.

Syntax

`TTEST (array1,array2,tails,type)`

Arguments Explanation

Argument Required/Optional Explanation
Array1 Required The first data set.
Array2 Required The second data set.
Tails Required Determines the number of distribution tails.

TTEST employs the one-tailed distribution if tails = 1.

TTEST employs the two-tailed distribution if tails = 2.

Type Required The kind of t-Test to perform.

Look at the t-Test Table given below.

Return Parameter

The probability related to a student’s t-test

Use of TTEST Function ### 32. The VAR Function

Function Objective

We can use this function to get the variance from a sample we provide.

Syntax

`VAR (number1,[number2],...)`

Arguments Explanation

Argument Required/Optional Explanation
Number1 Required the first number argument representing a population sample.
Number2 Optional Number arguments 2 to 255 corresponding to a sample of a population.

Return Parameter

The variance from a sample

Use of VAR Function ### 33. The WEIBULL Function

Function Objective

The function gives back the Weibull distribution which is useful for analyzing reliability.

Syntax

`WEIBULL(x,alpha,beta,cumulative)`

Arguments Explanation

Argument Required/Optional Explanation
X Required The value at which to evaluate the function.
Alpha Required A parameter to the distribution.
Beta Required A parameter to the distribution.
Cumulative Required determines the function’s shape.

Return Parameter

The Weibull distribution

Use of WEIBULL Function ### 34. The ZTEST Function

Function Objective

The function is very useful to get back the one-tailed probability value from a z-test.

Syntax

`ZTEST (array,x,[sigma])`

Arguments Explanation

Argument Required/Optional Explanation
Array Required The array or range of data against which to test x.
X Required The value to test.
Sigma Optional The population (known) standard deviation. The sample standard deviation is applied if it is omitted.

Return Parameter

The one-tailed probability value from a z-test

Use of ZTEST Function ## Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on 34 practical examples of compatibility functions in Excel. As you can see, there are quite a few ways to use these functions. So wisely choose the function that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please let me know in the comments.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  