Difference between revisions of "Excel to Analytica Mappings/Statistical Functions"

Line 151: Line 151:
  
 
Assume ''array'' is indexed by I.  One possible Analytica equivalent is:
 
Assume ''array'' is indexed by I.  One possible Analytica equivalent is:
  X[I=[[ArgMax]]([[Rank]](array,I)=k,I)]
+
[[Var..Do|Var]] n := [[Sum]](1,I);
 +
  X[I=[[ArgMax]]([[Rank]](array,I)=n+1-k,I)]
  
 
= LINEST =
 
= LINEST =
Line 180: Line 181:
 
= POISSON =
 
= POISSON =
 
= PROB =
 
= PROB =
= QUARTILE =
+
= QUARTILE(array,quart) =
= RANK =
+
 
= RSQ =
+
Analytica equivalent, where ''array'' is assumed indexed by ''I'':
= SKEW =
+
[[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) =
 
= SLOPE(known_y,known_x) =
  
Line 191: Line 214:
  
  
= SMALL =
+
= SMALL(array,k) =
= STANDARDIZE =
+
 
 +
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,...'') =
 
= STDEV(number1'',number2,...'') =
  

Revision as of 04:49, 13 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(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:

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

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


WEIBULL(x,alpha,beta,cumulative)

ZTEST

Comments


You are not allowed to post comments.