Difference between revisions of "Excel to Analytica Mappings/Statistical Functions"
| Line 93: | Line 93: | ||
= FISHER = | = FISHER = | ||
= FISHERINV = | = FISHERINV = | ||
| − | = FORECAST = | + | = FORECAST(x,known_y,known_x) = |
| + | |||
| + | Analytica equivalent (Index I an the index in common with ''known_x'' and ''known_y''): | ||
| + | [[Index..Do|Index]] K := ['b','m']; | ||
| + | [[Var..Do|Var]] B := [[Array]](K,[1,known_x]); | ||
| + | [[Var..Do|Var]] Bx := [[Array]](K,[1,x]); | ||
| + | [[Sum]]([[Regression]](known_y,B,I,K) * Bx, K ) | ||
| + | |||
= FREQUENCY(data_array,bins_array) = | = FREQUENCY(data_array,bins_array) = | ||
| Line 129: | Line 136: | ||
= HARMEAN = | = HARMEAN = | ||
= HYPGEOMDIST = | = HYPGEOMDIST = | ||
| − | = INTERCEPT = | + | = 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,...'') = | = KURT(number1'',number2,...'') = | ||
| Line 136: | Line 148: | ||
[[Kurtosis]](X,I) | [[Kurtosis]](X,I) | ||
| − | = LARGE = | + | = LARGE(array,k) = |
| + | |||
| + | Assume ''array'' is indexed by I. One possible Analytica equivalent is: | ||
| + | X[I=[[ArgMax]]([[Rank]](array,I)=k,I)] | ||
| + | |||
= LINEST = | = LINEST = | ||
= LOGEST = | = LOGEST = | ||
| Line 152: | Line 168: | ||
= NORMSDIST = | = NORMSDIST = | ||
= NORMSINV = | = NORMSINV = | ||
| − | = PEARSON = | + | = PEARSON(array1,array2) = |
| + | |||
| + | Analytica equivalent: | ||
| + | [[Correlation]](array1,array2,I) | ||
| + | where the data points in array1 and array2 both are indexed by ''I''. | ||
| + | |||
| + | |||
= PERCENTILE = | = PERCENTILE = | ||
= PERCENTRANK = | = PERCENTRANK = | ||
| Line 162: | Line 184: | ||
= RSQ = | = RSQ = | ||
= SKEW = | = SKEW = | ||
| − | = SLOPE = | + | = 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 = | = SMALL = | ||
= STANDARDIZE = | = STANDARDIZE = | ||
| − | = STDEV = | + | = STDEV(number1'',number2,...'') = |
| − | + | ||
| + | Analytica equivalent (assume that the numbers are in array ''X'' indexed by ''I''): | ||
| + | [[SDeviation]](X,I) | ||
| + | |||
= STDEVP = | = 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 = | = STEYX = | ||
= TDIST = | = TDIST = | ||
= TINV = | = TINV = | ||
| − | = TREND = | + | = 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..Do|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 = | = TRIMMEAN = | ||
= TTEST = | = TTEST = | ||
| − | = VAR = | + | = VAR(number1'',number2,...'') = |
| − | + | ||
| + | Analytica equivalent (assume numbers are in array X indexed by I): | ||
| + | [[Variance]](X,I) | ||
| + | |||
= VARP = | = VARP = | ||
| − | + | ||
| − | = WEIBULL = | + | 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 = | = ZTEST = | ||
Revision as of 04:39, 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:
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)
Enable comment auto-refresher