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)
Enable comment auto-refresher