Statistical Functions Part Three

+LARGE function + + +

LARGE

+Returns the Rank_c-th largest value in a data set. +

+LARGE(Data; RankC) +Data is the cell range of data. +RankC is the ranking of the value. If RankC is an array, the function becomes an array function. +

+=LARGE(A1:C50;2) gives the second largest value in A1:C50. +=LARGE(A1:C50;B1:B5) entered as an array function gives an array of the c-th largest value in A1:C50 with ranks defined in B1:B5. +

+SMALL function + + +

SMALL

+Returns the Rank_c-th smallest value in a data set. +

+SMALL(Data; RankC) +Data is the cell range of data. +RankC is the rank of the value. If RankC is an array, the function becomes an array function. +

+=SMALL(A1:C50;2) gives the second smallest value in A1:C50. +=SMALL(A1:C50;B1:B5) entered as an array function gives an array of the c-th smallest value in A1:C50 with ranks defined in B1:B5. +

+CONFIDENCE function + + +

CONFIDENCE

+Returns the (1-alpha) confidence interval for a normal distribution. +

+CONFIDENCE(Alpha; StDev; Size) + +Alpha is the level of the confidence interval. + +StDev is the standard deviation for the total population. + +Size is the size of the total population. +

+ +=CONFIDENCE(0.05;1.5;100) gives 0.29. +

+CONFIDENCE.T function + + +

CONFIDENCE.T

+ + Returns the (1-alpha) confidence interval for a Student's t distribution. +

+CONFIDENCE.T(Alpha; StDev; Size) + +Alpha is the level of the confidence interval. + +StDev is the standard deviation for the total population. + +Size is the size of the total population. +

+ +=CONFIDENCE.T(0.05;1.5;100) gives 0.2976325427. +

+CONFIDENCE.NORM function + + +

CONFIDENCE.NORM

+ + Returns the (1-alpha) confidence interval for a normal distribution. +

+CONFIDENCE.NORM(Alpha; StDev; Size) + +Alpha is the level of the confidence interval. + +StDev is the standard deviation for the total population. + +Size is the size of the total population. +

+ +=CONFIDENCE.NORM(0.05;1.5;100) gives 0.2939945977. +

+CORREL function +coefficient of correlation +mw added one entry + +

CORREL

+Returns the correlation coefficient between two data sets. +

+CORREL(Data1; Data2) + +Data1 is the first data set. + +Data2 is the second data set. +

+ +=CORREL(A1:A50;B1:B50) calculates the correlation coefficient as a measure of the linear correlation of the two data sets. +

+COVAR function + + +

COVAR

+Returns the covariance of the product of paired deviations. +

+COVAR(Data1; Data2) + +Data1 is the first data set. + +Data2 is the second data set. +

+ +=COVAR(A1:A30;B1:B30) + +

+ + COVARIANCE.P function + + +

COVARIANCE.P

+ Returns the covariance of the product of paired deviations, for the entire population. +

+ COVARIANCE.P(Data1; Data2) + Data1 is the first data set. + Data2 is the second data set. +

+ =COVARIANCE.P(A1:A30;B1:B30) +

+ + COVARIANCE.S function + + +

COVARIANCE.S

+ Returns the covariance of the product of paired deviations, for a sample of the population. +

+ COVARIANCE.S(Data1; Data2) + Data1 is the first data set. + Data2 is the second data set. +

+ =COVARIANCE.S(A1:A30;B1:B30) +

+CRITBINOM function + + +

CRITBINOM

+Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. +

+CRITBINOM(Trials; SP; Alpha) + +Trials is the total number of trials. + +SP is the probability of success for one trial. + +Alpha is the threshold probability to be reached or exceeded. +

+ +=CRITBINOM(100;0.5;0.1) yields 44. +

+KURT function + + +

KURT

+Returns the kurtosis of a data set (at least 4 values required). +

+KURT() +

+The parameters should specify at least four values. +

+ +=KURT(A1;A2;A3;A4;A5;A6) + +

+LOGINV function +inverse of lognormal distribution +mw added one entry + +

LOGINV

+Returns the inverse of the lognormal distribution. +

+LOGINV(Number [; Mean [; StDev]]) + +Number is the probability value for which the inverse standard logarithmic distribution is to be calculated. + +Mean is the arithmetic mean of the standard logarithmic distribution. + +StDev is the standard deviation of the standard logarithmic distribution. +

+ +=LOGINV(0.05;0;1) returns 0.1930408167. +

+LOGNORM.INV function +inverse of lognormal distribution +mw added one entry + +

LOGNORM.INV

+Returns the inverse of the lognormal distribution. +This function is identical to LOGINV and was introduced for interoperability with other office suites. +

+LOGNORM.INV(Number; Mean; StDev) + +Number (required) is the probability value for which the inverse standard logarithmic distribution is to be calculated. + +Mean (required) is the arithmetic mean of the standard logarithmic distribution. + +StDev (required) is the standard deviation of the standard logarithmic distribution. +

+ +=LOGNORM.INV(0.05;0;1) returns 0.1930408167. +

+LOGNORMDIST function +lognormal distribution +mw added one entry + +

LOGNORMDIST

+Returns the values of a lognormal distribution. +

+LOGNORMDIST(Number [; Mean [; StDev [; Cumulative]]]) + +Number is the probability value for which the standard logarithmic distribution is to be calculated. + +Mean (optional) is the mean value of the standard logarithmic distribution. + +StDev (optional) is the standard deviation of the standard logarithmic distribution. + +Cumulative (optional) = 0 calculates the density function, Cumulative = 1 calculates the distribution. +

+ +=LOGNORMDIST(0.1;0;1) returns 0.01. +

+LOGNORM.DIST function +lognormal distribution +mw added one entry + +

LOGNORM.DIST

+Returns the values of a lognormal distribution. +

+LOGNORM.DIST(Number; Mean; StDev; Cumulative) + +Number (required) is the probability value for which the standard logarithmic distribution is to be calculated. + +Mean (required) is the mean value of the standard logarithmic distribution. + +StDev (required) is the standard deviation of the standard logarithmic distribution. + +Cumulative (required) = 0 calculates the density function, Cumulative = 1 calculates the distribution. +

+ +=LOGNORM.DIST(0.1;0;1;1) returns 0.0106510993. +