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(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

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:

X[I=ArgMax(Rank(array,I)=k,I)]

LINEST

LOGEST

LOGINV

LOGNORMDIST

MAX

MAXA

MEDIAN

MIN

MINA

MODE

NEGBINOMDIST

NORMDIST

NORMINV

NORMSDIST

NORMSINV

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

RANK

RSQ

SKEW

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

STANDARDIZE

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)


WEIBULL(x,alpha,beta,cumulative)

ZTEST

Comments


You are not allowed to post comments.