Difference between revisions of "Excel to Analytica Mappings/Statistical Functions"
| Line 43: | Line 43: | ||
Note, however, that this is evaluated using sampling, so for small sample sizes there could be some sampling error in the result. | Note, however, that this is evaluated using sampling, so for small sample sizes there could be some sampling error in the result. | ||
| − | = CHIDIST = | + | = CHIDIST(x,dof) = |
| + | |||
| + | Analytica equivalent: | ||
| + | [[Dens_ChiSquared]](x,dof) | ||
| + | |||
= CHIINV = | = CHIINV = | ||
| + | |||
= CHITEST = | = CHITEST = | ||
| + | |||
= CONFIDENCE = | = CONFIDENCE = | ||
| − | = CORREL = | + | = CORREL(x,y) = |
| − | = COUNT = | + | |
| − | + | Analytica equivalents: | |
| + | [[Correlation]](x,y) | ||
| + | [[Correlation]](x,y,I) | ||
| + | |||
| + | Use the first form, without the index, when measuring correlation across the [[Run]] index (e.g., the Monte Carlo uncertainty dimension). | ||
| + | |||
| + | = COUNT(value1'',value2,...), COUNTA(value1'',value2,...'') = | ||
| + | |||
| + | Analytica equivalents, e.g.,: | ||
| + | [[Sum]]( IsNumber(x), I ) | ||
| + | [[Sum]]( x<>Null, I ) | ||
| + | |||
= COUNTBLANK = | = COUNTBLANK = | ||
| − | = COUNTIF = | + | |
| − | = COVAR = | + | The notion of a blank Excel cell does not directly translate, but in Analytica we would generally consider a cell of an array to be blank when its value is null. Thus, the Analytica equivalent would be: |
| + | |||
| + | [[Sum]]( x=null, I ) | ||
| + | |||
| + | = COUNTIF(range,criteria) = | ||
| + | |||
| + | The Analytica equivalent consists of expressing criteria as a boolean expression and then using: | ||
| + | [[Sum]](criteria,I) | ||
| + | |||
| + | For example, to count the number of values greater than 55, use: | ||
| + | [[Sum]](range>55,I) | ||
| + | |||
| + | = COVAR(array1,array2) = | ||
| + | |||
| + | Analytica equivalent: | ||
| + | [[CoVariance]](array1,array2,I) | ||
| + | |||
= CRITBINOM = | = CRITBINOM = | ||
= DEVSQ = | = DEVSQ = | ||
| Line 61: | Line 94: | ||
= FISHERINV = | = FISHERINV = | ||
= FORECAST = | = FORECAST = | ||
| − | = FREQUENCY = | + | = FREQUENCY(data_array,bins_array) = |
| + | |||
| + | Analytica equivalentd: | ||
| + | [[Frequency]](data_array,bins_array) | ||
| + | [[Frequency]](data_array,bins_array,I) | ||
| + | |||
| + | The second form is used when data_array is indexed by I. The first form takes the frequency of the uncertain sample, i.e., along the [[Run]] index. | ||
| + | |||
= FTEST = | = FTEST = | ||
| − | = GAMMADIST = | + | |
| − | = GAMMAINV = | + | = GAMMADIST(x,a,b) = |
| − | = GAMMALN = | + | |
| + | Analytica equivalent of GAMMADIST(x,a,b): | ||
| + | [[Dens_Gamma]](x,a,b) | ||
| + | |||
| + | Analytica equivalent of GAMMADIST(x,a,b,TRUE): | ||
| + | [[GammaI]](x,a,b) | ||
| + | |||
| + | To define a variable as uncertain with a gamma probability distribution, use: | ||
| + | [[Gamma]](a,b) | ||
| + | |||
| + | = GAMMAINV(p,a,b) = | ||
| + | |||
| + | Analytica equivalent: | ||
| + | [[GammaIInv]](p,a,b) | ||
| + | |||
| + | = GAMMALN(x) = | ||
| + | |||
| + | Analytica equivalent: | ||
| + | [[LGamma]](x) | ||
| + | |||
= GEOMEAN = | = GEOMEAN = | ||
= GROWTH = | = GROWTH = | ||
| Line 71: | Line 130: | ||
= HYPGEOMDIST = | = HYPGEOMDIST = | ||
= INTERCEPT = | = INTERCEPT = | ||
| − | = KURT = | + | = KURT(number1'',number2,...'') = |
| + | |||
| + | Analytica equivalents: | ||
| + | [[Kurtosis]](X) | ||
| + | [[Kurtosis]](X,I) | ||
| + | |||
= LARGE = | = LARGE = | ||
= LINEST = | = LINEST = | ||
Revision as of 17:48, 11 January 2008
AVEDEV(x1,x2,...)
Analytica equivalents:
Mean(x-Mean(x)) Mean(x-Mean(x,I),I)
AVERAGE(x1,x2,...), AVERAGEA(x1,x2,...)
Analytica equivalent:
Average(x,I)
BETADIST(x,alpha,beta,A,B)
Analytica equivalents:
BetaI(x,alpha,beta) BetaI((x-A)/(B-A),alpha,beta)
To define a variable as a beta probability distribution, use:
Beta(alpha,beta,A,B)
BETAINV(p,alpha,beta,A,B)
Analytica equivalents:
BetaIInv(p,alpha,beta) BetaIInv(p,alpha,beta) * (B-A) + A
To define a variable as a beta probability distribution, use:
Beta(alpha,beta,A,B)
BINOMDIST(x,n,p)
Analytica equivalent:
Prob_Binomial(x,n,p)
To use this function, add the Distribution Densities Library to your model.
To define a variable as binomially distributed, use:
Binomial(n,p)
For the cumulative binomial probability, BINOMDIST(x,n,p,TRUE), the Analytica equivalent is:
Probability(Binomial(n,p)<=x)
Note, however, that this is evaluated using sampling, so for small sample sizes there could be some sampling error in the result.
CHIDIST(x,dof)
Analytica equivalent:
Dens_ChiSquared(x,dof)
CHIINV
CHITEST
CONFIDENCE
CORREL(x,y)
Analytica equivalents:
Correlation(x,y) Correlation(x,y,I)
Use the first form, without the index, when measuring correlation across the Run index (e.g., the Monte Carlo uncertainty dimension).
COUNT(value1,value2,...), COUNTA(value1,value2,...)
Analytica equivalents, e.g.,:
Sum( IsNumber(x), I ) Sum( x<>Null, I )
COUNTBLANK
The notion of a blank Excel cell does not directly translate, but in Analytica we would generally consider a cell of an array to be blank when its value is null. Thus, the Analytica equivalent would be:
Sum( x=null, I )
COUNTIF(range,criteria)
The Analytica equivalent consists of expressing criteria as a boolean expression and then using:
Sum(criteria,I)
For example, to count the number of values greater than 55, use:
Sum(range>55,I)
COVAR(array1,array2)
Analytica equivalent:
CoVariance(array1,array2,I)
CRITBINOM
DEVSQ
EXPONDIST
FDIST
FINV
FISHER
FISHERINV
FORECAST
FREQUENCY(data_array,bins_array)
Analytica equivalentd:
Frequency(data_array,bins_array) Frequency(data_array,bins_array,I)
The second form is used when data_array is indexed by I. The first form takes the frequency of the uncertain sample, i.e., along the Run index.
FTEST
GAMMADIST(x,a,b)
Analytica equivalent of GAMMADIST(x,a,b):
Dens_Gamma(x,a,b)
Analytica equivalent of GAMMADIST(x,a,b,TRUE):
GammaI(x,a,b)
To define a variable as uncertain with a gamma probability distribution, use:
Gamma(a,b)
GAMMAINV(p,a,b)
Analytica equivalent:
GammaIInv(p,a,b)
GAMMALN(x)
Analytica equivalent:
LGamma(x)
GEOMEAN
GROWTH
HARMEAN
HYPGEOMDIST
INTERCEPT
KURT(number1,number2,...)
Analytica equivalents:
Kurtosis(X) Kurtosis(X,I)
Enable comment auto-refresher