Excel to Analytica Mappings/Statistical Functions
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