Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Collapse]Spreadsheet Formulas
    [Collapse]Functions
      Mathematical Functions
      Statistical Functions
      Date and Time Functions
      Text Functions
      Financial Functions
      Logical Functions
      Lookup and Reference Functions
      Engineering Functions
      Information Functions
      Compatibility Functions
      Database Functions
      Web Functions
      User-Defined Functions (UDF)
      Real Time Data (RTD) function
     Operators
     Array Formulas
     Calculation
     Formula Engine
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Statistical Functions

This document briefly describes statistical functions implemented in the XtraSpreadsheet.

*Standard Options means the following:
The arguments 'number1', '[number2]', etc., are numerical values or references to cells containing numbers. You can enter up to 255 number arguments. The arguments can be logical values or text representations of numbers. You can also use a reference to a cell array instead of separate numbers. Text, logical values, or empty cells within an array are ignored.

Name

Description

Syntax

AVEDEVCalculates the average deviation of a set of values.AVEDEV( number1, [number2], ... )
*Standard Options
AVERAGEReturns the average (arithmetic mean) of a list of numbers.AVERAGE( number1, [number2], ... )
*Standard Options
AVERAGEAReturns the average (arithmetic mean) of a list of numbers.AVERAGEA( number1, [number2], ... )
The difference compared to the AVERAGE function is that within arrays or reference arguments logical values are counted as 1 or 0 and text is counted as zero.
AVERAGEIFReturns the average (arithmetic mean) of the cells in a range that meet a given criteria.AVERAGEIF(range, criteria, [average_range])
The range is an array of values (or range of cells containing values) that is tested against the given criteria. If the average_range argument is omitted, the values in the initial range argument are used to calculate an average. Otherwise, the corresponding cells in the average_range array is used for calculation.
AVERAGEIFSFinds entries in one or more arrays, that satisfy the respective supplied criteria, and returns the average (arithmetic mean) of the corresponding values in an array supplied as the first argument.AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )
The average_range is the range for calculation. Criteria_range1, [criteria_range2], … - are the arrays to be tested against supplied criteria. Criteria1, [criteria2], … - are the respective conditions to be tested.
BETA.INVReturns the inverse of the beta cumulative probability density function (BETA.DIST).BETA.INV(probability,alpha,beta,[A],[B])
BETA.DISTReturns the beta distribution.BETA.DIST(x,alpha,beta,cumulative,[A],[B])
BINOM.DISTReturns the individual term binomial distribution probability.BINOM.DIST(number_s,trials,probability_s,cumulative)
BINOM.INVReturns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.BINOM.INV(trials,probability_s,alpha)
BINOM.DIST.RANGEReturns the probability of a trial result using a binomial distribution.BINOM.DIST.RANGE(trials,probability_s,number_s,[number_s2])
CHISQ.DISTReturns the chi-squared distribution.CHISQ.DIST(x,deg_freedom,cumulative)
X - Required. The value at which you want to evaluate the distribution. Deg_freedom - Required. The number of degrees of freedom. Cumulative - Required. A logical value that determines the form of the function.
CHISQ.DIST.RTReturns the right-tailed probability of the chi-squared distribution.CHISQ.DIST.RT(x,deg_freedom)
CHISQ.INVReturns the inverse of the left-tailed probability of the chi-squared distribution.CHISQ.INV(probability,deg_freedom)
CHISQ.INV.RTReturns the inverse of the right-tailed probability of the chi-squared distribution.CHISQ.INV.RT(probability,deg_freedom)
CHISQ.TESTReturns the test for independence as the value from the chi-squared distribution for the statistic and the appropriate degrees of freedom.CHISQ.TEST(actual_range,expected_range)
CONFIDENCE.NORMReturns the confidence interval for a population mean, using a normal distribution.CONFIDENCE.NORM(alpha,standard_dev,size)
Alpha is the significance level used to compute the confidence level. The confidence level equals 100*(1 - alpha)%, or in other words, an alpha of 0.05 indicates a 95 percent confidence level. Standard_dev is the population standard deviation for the data range and is assumed to be known. Size is the sample size.
CONFIDENCE.TReturns the confidence interval for a population mean, using a Student's t distribution.CONFIDENCE.T (alpha,standard_dev,size)
CORRELCalculates the correlation coefficient for two sets of values.CORREL( array1, array2 )
The arrays should be of equal length.
COUNTReturns the number of numeric values in a set of cells or values.COUNT( value1, [value2], ... )
Numbers and dates are always counted as numeric values. Text representations of numbers and logical values are counted only if supplied directly as function arguments.
COUNTAReturns a number of non-blank cells or values within a specified set.COUNTA( value1, [value2], ... )
COUNTBLANKReturns the number of blank cells in a range of cells.COUNTBLANK( range )
COUNTIFReturns the number of cells that satisfy a given criteria in a specified range.COUNTIF( range, criteria )
COUNTIFSReturns the number of entries that satisfy all specified criteria in specified ranges.COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2], ... )
Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other. Criteria is applied to the associated range and the logical matrix (true/false) is calculated. Resulting matrices are added using AND operator and the number of True entries is counted.
COVARIANCE.PReturns population covariance, the average of the products of deviations for each data point pair in two data sets.COVARIANCE.P(array1,array2)
COVARIANCE.SReturns the sample covariance, the average of the products of deviations for each data point pair in two data sets.COVARIANCE.S(array1,array2)
DEVSQReturns the sum of squares of deviations of data points from their sample mean.DEVSQ(number1, [number2], ...)
EXPON.DISTReturns the exponential distribution.EXPON.DIST(x,lambda,cumulative)
F.DISTReturns the F probability distribution.F.DIST(x,deg_freedom1,deg_freedom2,cumulative)
F.DIST.RTReturns the (right-tailed) F probability distribution (degree of diversity) for two data sets.F.DIST.RT(x,deg_freedom1,deg_freedom2)
F.INVReturns the inverse of the F probability distribution.F.INV(probability,deg_freedom1,deg_freedom2)
F.INV.RTReturns the inverse of the (right-tailed) F probability distribution.F.INV.RT(probability,deg_freedom1,deg_freedom2)
F.TESTReturns the result of an F-test, the two-tailed probability that the variances in array1 and array2 are not significantly different.F.TEST(array1,array2)
FISHERReturns the Fisher transformation at x. FISHER(x)
FISHERINVReturns the inverse of the Fisher transformation.FISHERINV(y)
FORECASTPerforms a linear regression to fit a straight line using least squares criterion to the specified arrays of values. Returns a new value for the specified X data point.FORECAST(x,known_y's,known_x's)
X is the data point for which you want to predict a value. Known_y's is the set of y-values for the relationship y = mx + b. Known_x's is a set of x-values for the relationship.
FREQUENCYCalculates how often values occur within a range of values, and then returns a vertical array of numbers.FREQUENCY(data_array, bins_array)
Data_array is an array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros. Bins_array is an array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.
GAMMAReturn the gamma function value.GAMMA(number)
GAMMA.DISTReturns the gamma distribution.GAMMA.DIST(x,alpha,beta,cumulative)
GAMMA.INVReturns the inverse of the gamma cumulative distribution.GAMMA.INV(probability,alpha,beta)
GAMMALNReturns the natural logarithm of the gamma function.GAMMALN(x)
GAMMALN.PRECISEReturns the natural logarithm of the gamma function (new version).GAMMALN.PRECISE(x)
GAUSSCalculates the probability that a member of a standard normal population will fall between the mean and z standard deviations from the mean.GAUSS(z)
GEOMEANReturns the geometric mean of a list of numbers.GEOMEAN( number1, [number2], ... )
*Standard Options
GROWTHCalculates an exponential curve that best fits your data based on a number of known X and Y values. Returns the y-values for a series of new x-values.GROWTH(known_y's, [known_x's], [new_x's], [const])
Known_y's is the set of y-values for the relationship y = b*m^x. Known_x's is an optional set of x-values for the relationship; if omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_y's. New_x's is an optional set of new x-values for which you want GROWTH to return corresponding y-values. If omitted, it is assumed to be the same as known_x's. Const is an optional logical value. It is TRUE or omitted, to calculate the b constant; otherwise b is set to 1. GROWTH is the exponential counterpart to the linear regression function TREND .
HARMEANReturns the harmonic mean of a data set.HARMEAN(number1,number2,...)
HYPGEOM.DISTReturns the hypergeometric distribution.HYPGEOM.DIST(sample_s,number_sample,population_s,number_pop,cumulative)
INTERCEPTCalculates the best fit regression line using a series of x- and y- values and returns the value at which this line intercepts the y-axis.INTERCEPT(known_y's, known_x's)
Known_y's is the dependent set of observations or data. Known_x's is the independent set of observations or data.
KURTReturns the kurtosis of a data set. KURT(number1,number2,...)
LARGEReturns the k'th largest value from an array or a range of cells containing numerical values.LARGE( array, k )
The array argument is the array or range of data for which the k-th largest value will be determined. The k argument is the top position of value in a sorted array.
LINESTReturns statistical information on the line of best fit, through a supplied set of x- and y- values using 'least-square' method.LINEST(known_y's,known_x's,const,stats)
Known_y's is the set of y-values you already know in the relationship y = mx + b. Known_x's is an optional set of x-values that you may already know in the relationship y = mx + b. Const is a logical value specifying whether to force the constant b to equal 0. Stats is a logical value specifying whether to return additional regression statistics.
LOGESTCalculates regression to fit an exponential curve using a least squares method. LOGEST( known_y's, [known_x's], [const], [stats] 
Known_y's is the set of dependent values. Known_x's is an optional set of independent values. The argument ""constant"" is TRUE to calculate the constant b in the regression equation y = b*m^x; otherwise, b equals 1. The argument ""stats"" set to TRUE if you want additional statistics, including various sums of squares, r-squared, f-statistic, and standard errors of the regression coefficients. LOGEST is the exponential counterpart to the linear regression function LINEST.
LOGNORM.DISTReturns the log-normal probability density function or the cumulative log- normal distribution.LOGNORM.DIST(x,mean,standard_dev,cumulative)
X is the value at which to evaluate the function. Mean is the mean of ln(x). Standard_dev is the standard deviation of ln(x). Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, LOGNORM.DIST returns the cumulative distribution function; otherwise, it returns the probability density function.
LOGNORM.INVReturns the inverse of the log-normal distributionLOGNORM.INV(probability,mean,standard_dev)
X is a probability associated with the lognormal distribution. Mean is the mean of ln(x). Standard_dev is the standard deviation of ln(x).
MAXReturns the largest value in a set of values.MAX( number1, [number2], ... )
*Standard Options
MAXAReturns the largest value in a set of values.MAXA( number1, [number2], ... )
The difference compared to the MAX function is that within arrays or reference arguments logical values are counted as 1 or 0 and text is counted as zero.
MEDIANReturns the statistical median (the middle value) of a list of numbers.MEDIAN(number1, [number2], ...)
*Standard Options
MINReturns the smallest value in a set of values.MIN(number1, [number2], ...)
*Standard Options
MINAReturns the smallest value in a set of values.MIN(number1, [number2], ...)
The difference compared to the MIN function is that within arrays or reference arguments logical values are counted as 1 or 0 and text is counted as zero.
MODE.MULTReturns a vertical array of the statistical modes (the most frequently occurring values) within a set of values.MODE.MULT((number1,[number2],...])
The arguments number1, [number2], etc, are numerical values or references to cells containing numbers. You can enter up to 255 number arguments. The arguments can be logical values or text representations of numbers. You can also use a reference to cell array instead of separate numbers. Text, logical values, or empty cells witin array are ignored. If the data set contains no duplicate data points, the #N/A error value is returned.
MODE.SNGLReturns the statistical mode (the most frequently occurring value) in a set of values.MODE.SNGL(number1,[number2],...])
The arguments number1, [number2], etc, are numerical values or references to cells containing numbers. You can enter up to 255 number arguments. The arguments can be logical values or text representations of numbers. You can also use a reference to cell array instead of separate numbers. Text, logical values, or empty cells witin array are ignored. If the data set contains no duplicate data points, the #N/A error value is returned.
NEGBINOM.DISTReturns the negative binomial distribution.NEGBINOM.DIST(number_f,number_s,probability_s,cumulative)
NORM.DISTReturns the normal distribution for the specified mean and standard deviation.NORM.DIST(x,mean,standard_dev,cumulative)
X is the value for which you want the distribution. Mean is the arithmetic mean of the distribution. Standard_dev is the standard deviation of the distribution. Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, NORM.DIST returns the cumulative distribution function; if FALSE, it returns the probability mass function.
NORM.INVReturns the inverse of the normal cumulative distribution for the specified mean and standard deviation.NORM.INV(probability,mean,standard_dev)
Probability is a probability corresponding to the normal distribution. Mean is the arithmetic mean of the distribution. Standard_dev is the standard deviation of the distribution.
NORM.S.DISTReturns the standard normal distribution (has a mean of zero and a standard deviation of one).NORM.S.DIST(z,cumulative)
Z is the value for which you want the distribution. Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, NORMS.DIST returns the cumulative distribution function; otherwise it returns the probability mass function.
NORM.S.INVReturns the inverse of the standard normal cumulative distribution.NORM.S.INV(probability)
Probability is a probability that corresponds to the normal distribution.
PEARSONReturns the Pearson product moment correlation coefficient, a statistical measurement of the correlation (linear association) between two sets of values. PEARSON(array1,array2)
The array1 is a set of independent values, the array2 is a set of dependent values.
PERCENTILE.EXCReturns the k'th percentile of a supplied range of values for a given value of k, within the range 0 to 1 (exclusive).PERCENTILE.EXC( array, k )
PERCENTILE.INCReturns the k'th percentile of a supplied range of values for a given value of k, within the range 0 to 1 (inclusive).PERCENTILE.INC( array, k )
PERCENTRANK.EXCCalculates the relative position, between 0 and 1 (exclusive), of a specified value within a supplied array.PERCENTRANK.EXC( array, x, [significance] )
PERCENTRANK.INCCalculates the relative position, between 0 and 1 (inclusive), of a specified value within a supplied array.PERCENTRANK.INC( array, x, [significance] )
PERMUTReturns the number of permutations for a given number of objects.PERMUT(number, number_chosen)
PERMUTATIONAReturns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects.PERMUTATIONA(number, number-chosen)
PHIReturns the value of the density function for a standard normal distribution.PHI(x)
POISSON.DISTReturns the Poisson distribution.POISSON.DIST(x,mean,cumulative)
PROBReturns the probability that values in a range are between two limits.PROB(x_range, prob_range, [lower_limit], [upper_limit])
QUARTILE.EXCReturns the quartile of the data set, based on percentile values from 0..1, exclusive.QUARTILE.EXC(array, quart)
QUARTILE.INCReturns the quartile of a data set, based on percentile values from 0..1, inclusive.QUARTILE.INC(array,quart)
RANK.AVGReturns 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.RANK.AVG(number,ref,[order])
RANK.EQReturns 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.RANK.EQ(number,ref,[order])
RSQReturns the square of the Pearson product moment correlation coefficient.RSQ(known_y's,known_x's)
SKEWReturns the skewness (the asymmetry around the mean) of a distribution.SKEW(number1, [number2], ...)
Number1 is required, subsequent numbers are optional. You can supply up to 255 arguments for which you want to calculate skewness or use a single array or a reference to an array instead of arguments separated by commas.
SKEW.PReturns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean.SKEW.P(number 1, [number 2],…)
SLOPEReturns the slope of the linear regression line through data points in known_y's and known_x's.SLOPE(known_y's, known_x's)
Known_y's is an array or cell range of numeric dependent data values. Known_x's is the set of independent data values.
SMALLReturns the k'th smallest value from an array or a range of cells containing numerical values.SMALL array, k )
The array argument is the array or range of data for which the k-th smallest value will be determined. The k argument is the top position of value in an array sorted from smallest to largest.
STANDARDIZEReturns a normalized value from a distribution characterized by mean and standard_dev.STANDARDIZE(x,mean,standard_dev)
STEYXReturns the standard error of the predicted y-value for each x in the regression.STEYX(known_y's, known_x's)
STDEVACalculates the standard deviation based on a sample.STDEVA(value1, [value2], ...)
The arguments value1, [value2], etc, are numerical values or references to cells. You can enter up to 255 arguments. The arguments can be logical values or text representations of numbers. You can also use a reference to cell array instead of separate numbers. Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero).
STDEVPACalculates standard deviation based on the entire population, including numbers, text, and logical valuesSTDEVPA(value1,value2,...)
STDEV.PCalculates the standard deviation based on the entire population.STDEV.P(number1,[number2],...])
*Standard Options
STDEV.SCalculates the standard deviation based on a sample.STDEV.S(number1,[number2],...])
*Standard Options
T.DISTReturns the Student's t-distribution.T.DIST(x,deg_freedom, cumulative)
T.DIST.2TReturns the two-tailed Student's t-distribution.T.DIST.2T(x,deg_freedom)
T.DIST.RTReturns the right-tailed Student's t-distribution.T.DIST.RT(x,deg_freedom)
T.INVReturns the left-tailed inverse of the Student's t-distribution.T.INV(probability,deg_freedom)
T.TESTReturns the probability that is associated with a Student's t-Test.T.TEST(array1,array2,tails,type)
T.INV.2TReturns the two-tailed inverse of the Student's t-distribution.T.INV.2T(probability,deg_freedom)
TRENDPerforms a linear regression to fit a straight line using least squares criterion to the specified arrays of values. TREND(known_y's, [known_x's], [new_x's], [const])
Known_y's is the set of y-values for the relationship y = mx + b. Known_x's is an optional set of x-values for the relationship; if omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_y's. New_x's is an optional set of new x-values for which you want TREND to return corresponding y-values. If omitted, it is assumed to be the same as known_x's. Const is an optional logical value. It is TRUE or omitted, to calculate the b constant; otherwise b is set to 0 (zero).
TRIMMEANReturns the mean of the interior of a data set.TRIMMEAN(array, percent)
VARAEstimates variance based on a sample, including numbers, text, and logical values.VARA(value1, [value2], ...)
VARPACalculates variance based on the entire population, including numbers, text, and logical values.VARPA(value1, [value2], ...)
VAR.PCalculates variance for the entire population.VAR.P(number1,[number2],...])
*Standard Options
VAR.SCalculates variance for the sample.VAR.S(number1,[number2],...])
*Standard Options
WEIBULL.DISTReturns the Weibull distribution.WEIBULL.DIST(x,alpha,beta,cumulative)
Z.TESTReturns the one-tailed probability-value of a z-test.Z.TEST(array,x,[sigma])

How would you rate this topic?​​​​​​​