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(number1,number2,...)
Analytica equivalents:
Sum( (X-Mean(X,I))^2, I ) Variance(X,I) * (Sum(1,I)-1)
where the numbers are in array X indexed by I.
EXPONDIST
FDIST
FINV
FISHER
FISHERINV
FORECAST(x,known_y,known_x)
Analytica equivalent (Index I an the index in common with known_x and known_y):
Index K := ['b','m']; Var B := Array(K,[1,known_x]); Var Bx := Array(K,[1,x]); Sum(Regression(known_y,B,I,K) * Bx, K )
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
There is no equivalent built into Analtyica.
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(known_y,known_x)
Assume known_y and known_x share index I. The Analytica equivalent is:
Index K := ['b','m']; Regression( known_y, Array(K,[1,known_x]), I, K ) [ K='b' ]
KURT(number1,number2,...)
Analytica equivalents:
Kurtosis(X) Kurtosis(X,I)
LARGE(array,k)
Assume array is indexed by I. One possible Analytica equivalent is:
Var n := Sum(1,I); X[I=ArgMax(Rank(array,I)=n+1-k,I)]
LINEST
LOGEST
LOGINV
LOGNORMDIST
MAX
MAXA
MEDIAN
MIN
MINA
MODE
NEGBINOMDIST
NORMDIST(x,mean,standard_dev,cumulative)
If Cumulative=True, the Analytica equivalent is:
CumNormal(x,mean,standard_dev)
If Cumulative=False, the Analytica equivalent is:
Dens_Normal(x,mean,standard_dev)
Note that to use Dens_Normal, you must include the distribution densities library in your model.
NORMINV(p,mean,standard_dev)
Analytica equivalent:
CumNormalInv(p,mean,standard_dev)
NORMSDIST(z)
Analytica equivalent:
CumNormal(z)
NORMSINV(p)
Analytica equivalent:
CumNormalInv(p)
PEARSON(array1,array2)
Analytica equivalent:
Correlation(array1,array2,I)
where the data points in array1 and array2 both are indexed by I.
PERCENTILE
PERCENTRANK
PERMUT
POISSON
PROB
QUARTILE(array,quart)
Analytica equivalent, where array is assumed indexed by I:
GetFract(array,quart/4,I)
RANK(number,range,order)
Assume in Analytica that the array identified by range is indexed by I. Then when order is non-zero, the Analytica equivalent is:
Rank(range,I,type:-1)[I=number]
When order is omitted or zero in Excel, the Analytica equivalent is:
Rank(-range,I,type:1)[I=number]
RSQ(known_y,known_x)
Analytica equivalent:
Correlation(known_y,known_x,I)^2
SKEW(number1,number2,...)
Analytica equivalent (assume the numbers are in array X, indexed by I):
Skewness(X,I)
When X is an uncertain distribution, indexed by Run, the equivalent is:
Skewness(X)
SLOPE(known_y,known_x)
Assume known_y and known_x share index I. The Analytica equivalent is:
Index K := ['b','m']; Regression( known_y, Array(K,[1,known_x]), I, K ) [ K='m' ]
SMALL(array,k)
Assume array is indexed by I. One possible Analytica equivalent is:
X[I=ArgMax(Rank(array,I)=k,I)]
STANDARDIZE(x,m,sd)
Analytica equivalent:
(x-m)/sd
STDEV(number1,number2,...)
Analytica equivalent (assume that the numbers are in array X indexed by I):
SDeviation(X,I)
STDEVP
Analytica equivalent (assume that the numbers are in array X indexed by I):
SDeviation(X,I) * (Sum(1,I)-1)/Sum(1,I)
STEYX
TDIST
TINV
TREND(known_y,known_x,new_x,const)
Assume that the data in known_y and known_x are indexed by index I, and the points in new_x are indexed by J. Also assume that const is 1 (true) for a y=m*x+b curve, and 0 (false) for a y=m*b fit. Then the Analytica equivalent is:
Index K := ['b','m']; Sum( Regression(known_y,Array(K,[const,known_x]),I,K) * Array(K,[const,new_x]), K )
The result is the set of predicted new_y values, indexed by J.
TRIMMEAN
TTEST
VAR(number1,number2,...)
Analytica equivalent (assume numbers are in array X indexed by I):
Variance(X,I)
VARP
Analytica equivalent (assume numbers are in array X indexed by I):
Variance(X,I) * (Sum(1,I)-1)/Sum(1,I)
Enable comment auto-refresher