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

Line 7: Line 7:
 
== AVEDEV(x1'', x2,...'') ==
 
== AVEDEV(x1'', x2,...'') ==
  
Analytica equivalents are [[Mean]](x-[[Mean]](x)) and [[Mean]](x-[[Mean]](x, I), I).
+
Analytica equivalents are [[Mean]](x - [[Mean]](x)) and [[Mean]](x - [[Mean]](x, I), I).
  
 
== AVERAGE(x1'', x2,...''), AVERAGEA(x1'', x2,...'') ==
 
== AVERAGE(x1'', x2,...''), AVERAGEA(x1'', x2,...'') ==
  
Analytica equivalent is  
+
''Analytica equivalent is ''
[[Average]](x, I).
+
:[[Average]](x, I).
  
 
== AVERAGEA==
 
== AVERAGEA==
Line 22: Line 22:
 
== BETADIST(x, alpha, beta'', A, B'') ==
 
== BETADIST(x, alpha, beta'', A, B'') ==
  
Analytica equivalents are  
+
''Analytica equivalents are ''
[[BetaI]](x, alpha, beta)
+
:[[BetaI]](x, alpha, beta)
 
and  
 
and  
[[BetaI]]((x-A)/(B-A), alpha, beta).
+
:[[BetaI]]((x - A)/(B - A), alpha, beta).
  
 
To define a variable as a beta probability distribution, use:
 
To define a variable as a beta probability distribution, use:
[[Beta]](alpha, beta'', A, B'')
+
:[[Beta]](alpha, beta'', A, B'')
  
 
== BETA.DIST(x, alpha, beta, cumulative'', A, B'') ==
 
== BETA.DIST(x, alpha, beta, cumulative'', A, B'') ==
Line 35: Line 35:
 
This is named <code>BETA.INV(p, alpha, beta, A, B)</code> in Excel 2010 , but the equivalent is the same.
 
This is named <code>BETA.INV(p, alpha, beta, A, B)</code> in Excel 2010 , but the equivalent is the same.
  
Analytica equivalents are [[BetaIInv]](p, alpha, beta) and [[BetaIInv]](p, alpha, beta) * (B-A) + A.
+
Analytica equivalents are [[BetaIInv]](p, alpha, beta) and [[BetaIInv]](p, alpha, beta)*(B - A) + A.
  
 
To define a variable as a beta probability distribution, use:
 
To define a variable as a beta probability distribution, use:
[[Beta]](alpha, beta'', A, B'')
+
:[[Beta]](alpha, beta'', A, B'')
  
 
== BINOMDIST(x, n, p) ==
 
== BINOMDIST(x, n, p) ==
  
Analytica equivalent:
+
''Analytica equivalent:''
[[Prob_Binomial]](x, n, p)
+
:[[Prob_Binomial]](x, n, p)
  
To use this function, add the Distribution Densities Library to your model.
+
To use this function, add the [[Distribution Densities Library]] to your model.
  
 
To define a variable as binomially distributed, use:
 
To define a variable as binomially distributed, use:
[[Binomial]](n, p)
+
:[[Binomial]](n, p)
  
 
For the cumulative binomial probability, <code>BINOMDIST(x, n, p, TRUE)</code>, the Analytica equivalent is:
 
For the cumulative binomial probability, <code>BINOMDIST(x, n, p, TRUE)</code>, the Analytica equivalent is:
[[Probability]](Binomial(n, p) <= x)
+
:[[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.
 
Note, however, that this is evaluated using sampling, so for small sample sizes there could be some sampling error in the result.
Line 60: Line 60:
  
 
The [[CumBinomialInv]] function found in the <code>"Distribution Densities.ana"</code> library starting with Analytica 4.4.3 provides the equivalent:
 
The [[CumBinomialInv]] function found in the <code>"Distribution Densities.ana"</code> library starting with Analytica 4.4.3 provides the equivalent:
[[CumBinomialInv]](alpha, trials, probability_s)
+
:[[CumBinomialInv]](alpha, trials, probability_s)
  
 
== CHIDIST(x, dof) or CHISQ.DIST(x, deg_freedom, cumulative) ==
 
== CHIDIST(x, dof) or CHISQ.DIST(x, deg_freedom, cumulative) ==
  
 
Analytica equivalent for CHIDIST or CHISQ.DIST when cumulative is false:
 
Analytica equivalent for CHIDIST or CHISQ.DIST when cumulative is false:
[[Dens_ChiSquared]](x, dof)
+
:[[Dens_ChiSquared]](x, dof)
  
 
Equivalent of CHISQ.DIST when cumulative is true:
 
Equivalent of CHISQ.DIST when cumulative is true:
[[CumChiSquared]](x, dof)
+
:[[CumChiSquared]](x, dof)
  
Both [[Dens_ChiSquared]] and [[CumChiSquared]] are in the <code>Distribution Densities Library</code>.
+
Both [[Dens_ChiSquared]] and [[CumChiSquared]] are in the [[Distribution Densities Library]].
  
== CHIINV(p,dof) or CHISQ.INV(p, dof) ==
+
== CHIINV(p, dof) or CHISQ.INV(p, dof) ==
  
Analytica equivalent:
+
''Analytica equivalent:''
2 * [[GammaIInv]](p, dof/2)
+
:2 * [[GammaIInv]](p, dof/2)
  
 
== CHISQ.INV.RT(p, dof) ==
 
== CHISQ.INV.RT(p, dof) ==
Line 82: Line 82:
  
 
For the 1-D case, where «actual» and «expected» are each 1-D, in Analytica these will have a common index, <code>I</code>, and the Analytica equivalent is:
 
For the 1-D case, where «actual» and «expected» are each 1-D, in Analytica these will have a common index, <code>I</code>, and the Analytica equivalent is:
 +
<pre style="background:white; border:white; margin-left: 1em;">
 
  Var n := Sum(1, I);
 
  Var n := Sum(1, I);
  Var chi2 := Sum((actual-expected)^2 / expected, I);
+
  Var chi2 := Sum((actual - expected)^2/expected, I);
  1-GammaI(chi2/2, (n-1)/2)
+
  1 - GammaI(chi2/2, (n - 1)/2)
 +
</pre>
  
 
For the 2-D contingency table analysis, with indexes <code>I</code> and <code>J</code>, the equivalent is:
 
For the 2-D contingency table analysis, with indexes <code>I</code> and <code>J</code>, the equivalent is:
 +
<pre style="background:white; border:white; margin-left: 1em;">
 
  Var n := Sum(1, I, J);
 
  Var n := Sum(1, I, J);
  Var chi2 := Sum( (actual - expected)^2 / expected, I, J);
+
  Var chi2 := Sum((actual - expected)^2/expected, I, J);
  1-GammaI(chi2/2, (n - 1)/2)
+
  1 - GammaI(chi2/2, (n - 1)/2)
 +
</pre>
  
 
== CONFIDENCE(alpha, sd, n) or CONFIDENCE.NORM(alpha, sd, n) ==
 
== CONFIDENCE(alpha, sd, n) or CONFIDENCE.NORM(alpha, sd, n) ==
  
Analytica equivalent:
+
''Analytica equivalent:''
[[CumNormalInv]](0.5 + (1 - alpha)/2, 0, sd ) / Sqrt(n)
+
:[[CumNormalInv]](0.5 + (1 - alpha)/2, 0, sd ) / Sqrt(n)
  
 
== CONFIDENCE.T(alpha, sd, n) ==
 
== CONFIDENCE.T(alpha, sd, n) ==
  
Analytica equivalent:
+
''Analytica equivalent:''
[[CumStudentTInv]](alpha/2, n - 1)*sd/Sqrt(n)
+
:[[CumStudentTInv]](alpha/2, n - 1)*sd/Sqrt(n)
  
''Note: [[CumStudentTInv]] is in the <code>Distribution Densities</code> library.''
+
''Note'': [[CumStudentTInv]] is in the [[Distribution Densities Library]].
  
 
== CORREL(x, y) ==
 
== CORREL(x, y) ==
Line 111: Line 115:
 
== COUNT(value1'', value2,...''), COUNTA(value1'', value2,...'') ==
 
== COUNT(value1'', value2,...''), COUNTA(value1'', value2,...'') ==
  
Analytica equivalents, e.g.,:
+
''Analytica equivalents'', e.g.,:
[[Sum]](IsNumber(x), I)
+
:[[Sum]]([[IsNumber]](x), I)
[[Sum]](x <> Null, I)
+
:[[Sum]](x <> Null, I)
  
 
== COUNTBLANK ==
 
== 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:
+
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)
+
:[[Sum]](x = null, I)
  
 
== COUNTIF(range, criteria) ==
 
== COUNTIF(range, criteria) ==
  
 
The Analytica equivalent consists of expressing criteria as a boolean expression and then using:
 
The Analytica equivalent consists of expressing criteria as a boolean expression and then using:
[[Sum]](criteria, I)
+
:[[Sum]](criteria, I)
  
 
For example, to count the number of values greater than 55, use:
 
For example, to count the number of values greater than 55, use:
[[Sum]](range > 55, I)
+
:<code>Sum(range > 55, I)</code>
  
 
== COUNTIFS(range1, criteria1'', range2, critieria2, range3, criteria3,...'') ==
 
== COUNTIFS(range1, criteria1'', range2, critieria2, range3, criteria3,...'') ==
Line 135: Line 139:
 
For example, in an array named range with 4 dimensions: I,J,K and L, to count the number of values greater than 55 you would use:
 
For example, in an array named range with 4 dimensions: I,J,K and L, to count the number of values greater than 55 you would use:
  
[[Sum]](range > 55, I, J, K, L)
+
:[[Sum]](range > 55, I, J, K, L)
  
If the criteria varies along one or more of the dimensions, then your criteria would simply be indexed by that.  For example, if you have separate bounds for each value along L, you would create an array, Lb, indexed by L and containing the lower bounds, and then use:
+
If the criteria varies along one or more of the dimensions, then your criteria would simply be indexed by that.  For example, if you have separate bounds for each value along ''L'', you would create an array, ''Lb'', indexed by ''L'' and containing the lower bounds, and then use:
  
[[Sum]](range > lb,  I, J, K, L)
+
:[[Sum]](range > lb,  I, J, K, L)
  
 
== COVAR(array1, array2) or COVARIANCE.S(array1, array2) ==
 
== COVAR(array1, array2) or COVARIANCE.S(array1, array2) ==
  
Analytica equivalent:
+
''Analytica equivalent:''
[[CoVariance]](array1, array2,I)
+
:[[Covariance]](array1, array2,I)
  
 
== COVARIANCE.P(array1, array2) ==
 
== COVARIANCE.P(array1, array2) ==
  
Analytica equivalent:
+
''Analytica equivalent:''
  
[[Var]] n := [[Sum]](1, I);
+
:[[Var]] n := [[Sum]](1, I);
[[CoVariance]](array1, array2, I) * (n - 1)/n
+
:[[Covariance]](array1, array2, I)*(n - 1)/n
  
 
== CRITBINOM(alpha, trials, probability_s) ==
 
== CRITBINOM(alpha, trials, probability_s) ==
  
 
The CRITBINOM computes the inverse CDF for a binomial distribution.  The [[CumBinomialInv]] function found in the <code>"Distribution Densities.ana"</code> library starting with Analytica 4.4.3 provides the equivalent:
 
The CRITBINOM computes the inverse CDF for a binomial distribution.  The [[CumBinomialInv]] function found in the <code>"Distribution Densities.ana"</code> library starting with Analytica 4.4.3 provides the equivalent:
CumBinomialInv(alpha, trials, probability_s)
+
:[[CumBinomialInv]](alpha, trials, probability_s)
  
 
== DEVSQ(number1'', number2,...'') ==
 
== DEVSQ(number1'', number2,...'') ==
  
Analytica equivalents:
+
''Analytica equivalents:''
[[Sum]]((X - [[Mean]](X, I))^2, I)
+
:[[Sum]]((X - [[Mean]](X, I))^2, I)
[[Variance]](X, I)*([[Sum]](1, I) - 1)
+
:[[Variance]](X, I)*([[Sum]](1, I) - 1)
  
 
where the numbers are in array «X» indexed by «I».
 
where the numbers are in array «X» indexed by «I».
Line 169: Line 173:
  
 
If cumulative is false, the Analytica equivalent is:
 
If cumulative is false, the Analytica equivalent is:
[[Dens_Exponential]](x, 1/lambda)
+
:[[Dens_Exponential]](x, 1/lambda)
  
 
When cumulative is true, the Analytica equivalent is:
 
When cumulative is true, the Analytica equivalent is:
[[CumExponential]](x, 1/lambda)
+
:[[CumExponential]](x, 1/lambda)
  
''Note: These functions are in the '''Distribution Densities''' library''
+
''Note'': These functions are in the [[Distribution Densities Library]]
  
 
== F.DIST(x, dof1, dof2, cumulative) ==
 
== F.DIST(x, dof1, dof2, cumulative) ==
  
 
When «cumulative» is true, the Analytica equivalent is:
 
When «cumulative» is true, the Analytica equivalent is:
[[CumFDist]](x, dof1, dof2)
+
:[[CumFDist]](x, dof1, dof2)
  
 
When «cumulative» is false:
 
When «cumulative» is false:
[[Dens_FDist]](x, dof1, dof2)
+
:[[Dens_FDist]](x, dof1, dof2)
  
''Note: [[CumFDist]] and [[Dens_FDist]] are in the '''Distribution Densities''' library''
+
''Note'': [[CumFDist]] and [[Dens_FDist]] are in the [[Distribution Densities Library]]
  
 
== FDIST(x, dof1, dof2) or F.DIST.RT(x, dof1, dof2) ==
 
== FDIST(x, dof1, dof2) or F.DIST.RT(x, dof1, dof2) ==
  
The Analytica equivalent is
+
''The Analytica equivalent is''
1-[[CumFDist]](x, dof1, dof2)
+
:1-[[CumFDist]](x, dof1, dof2)
  
''Note: [[CumFDist]] is the '''Distribution Densities''' library''
+
''Note:'' [[CumFDist]] is the [[Distribution Densities Library]]
  
 
== F.INV(p, dof1, dof2) ==
 
== F.INV(p, dof1, dof2) ==
  
The Analytica equivalent is:
+
''The Analytica equivalent is:''
[[CumFDistInv]](p, dof1, dof2)
+
:[[CumFDistInv]](p, dof1, dof2)
  
 
== FINV(p, dof1, dof2) or F.INV.RT(p, dof1, dof2) ==
 
== FINV(p, dof1, dof2) or F.INV.RT(p, dof1, dof2) ==
  
The Analytica equivalent is:
+
''The Analytica equivalent is:''
[[CumFDistInv]](1 - p, dof1, dof2)
+
:[[CumFDistInv]](1 - p, dof1, dof2)
  
 
== F.TEST(array1, array2) ==
 
== F.TEST(array1, array2) ==
Line 207: Line 211:
 
The Analytica equivalent, assuming «array1» is indexed by «I» and «array2» is indexed by «J» is:
 
The Analytica equivalent, assuming «array1» is indexed by «I» and «array2» is indexed by «J» is:
  
  [[Var]] F0 := [[Variance]](array1, I)/[[Variance]](array2, J);
+
<pre style="background:white; border:white; margin-left: 1em;">
  [[Var]] F  := [[Max]]([F0,1/F0]);
+
  Var F0 := Variance(array1, I)/Variance(array2, J);
  2 * (1-[[CumFDist]](F, [[Sum]](1, I) - 1, [[Sum]](1, J) - 1))
+
  Var F  := Max([F0,1/F0]);
 +
  2 * (1- CumFDist(F, Sum(1, I) - 1, Sum(1, J) - 1))
 +
</pre>
  
 
== FISHER(x) ==
 
== FISHER(x) ==
  
The Analytica equivalent is:
+
''The Analytica equivalent is:''
 
+
:[[Ln]]((1 + x)/(1 - x))/2
[[Ln]]((1 + x)/(1 - x))/2
+
  
 
== FISHERINV(y) ==
 
== FISHERINV(y) ==
  
The Analytica equivalent is:
+
''The Analytica equivalent is:''
[[Var]] e2y := [[Exp]](2*y) Do (e2y - 1)/(e2y + 1)
+
:[[Var]] e2y := [[Exp]](2*y) Do (e2y - 1)/(e2y + 1)
  
 
== FORECAST(x, known_y, known_x) ==
 
== FORECAST(x, known_y, known_x) ==
  
 
Analytica equivalent (Index «I» an the index in common with «known_x» and «known_y»):
 
Analytica equivalent (Index «I» an the index in common with «known_x» and «known_y»):
  [[Index..Do|Index]] K := ['b', 'm'];
+
<pre style="background:white; border:white; margin-left: 1em;">
  [[Var..Do|Var]] B := [[Array]](K, [1, known_x]);
+
  Index K := ['b', 'm'];
  [[Var..Do|Var]] Bx := [[Array]](K, [1, x]);
+
  Var B := Array(K, [1, known_x]);
  [[Sum]]([[Regression]](known_y, B, I, K) * Bx, K)
+
  Var Bx := Array(K, [1, x]);
 +
  Sum(Regression(known_y, B, I, K) * Bx, K)
 +
</pre>
  
 
== FREQUENCY(data_array, bins_array) ==
 
== FREQUENCY(data_array, bins_array) ==
  
Analytica equivalentd:
+
Analytica equivalents:
[[Frequency]](data_array, bins_array)
+
:[[Frequency]](data_array, bins_array)
[[Frequency]](data_array, bins_array,I)
+
:[[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.
+
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(array1, array2) ==
 
== FTEST(array1, array2) ==
  
 
The following [[User-Defined Function]] provides an Analytica equivalent:
 
The following [[User-Defined Function]] provides an Analytica equivalent:
 +
<pre style="background:white; border:white; margin-left: 1em;">
 
  Function FTest(A1: ContextSamp[I], A2 : ContextSamp[J] ; I, J : Index = Run)
 
  Function FTest(A1: ContextSamp[I], A2 : ContextSamp[J] ; I, J : Index = Run)
 
  Definition:
 
  Definition:
    [[Var..Do|Var]] v1 := [[Variance]](A1, I);
+
  Var v1 := Variance(A1, I);
    [[Var..Do|Var]] v2 := [[Variance]](A2, J);
+
  Var v2 := Variance(A2, J);
     [[Var..Do|Var]] n1 := [[Sum]](1, I);
+
     Var n1 := Sum(1, I);
     [[Var..Do|Var]] n2 := [[Sum]](1, J);
+
     Var n2 := Sum(1, J);
     [[Var..Do|Var]] F := v2/v1;
+
     Var F := v2/v1;
     1 - [[CumFDist]](F, n1 - 1, n2 - 1)
+
     1 - CumFDist(F, n1 - 1, n2 - 1)
 +
</pre>
  
Add the above UDF to your model, then you can just use:
+
Add the above [[UDF]] to your model, then you can just use:
FTest(array1, array2, I, J)
+
:FTest(array1, array2, I, J)
 
where «I» and «J» are the indexes of «array1» and «array2» respectively.
 
where «I» and «J» are the indexes of «array1» and «array2» respectively.
  
Line 257: Line 266:
  
 
Analytica equivalent of GAMMADIST(x, a, b):
 
Analytica equivalent of GAMMADIST(x, a, b):
[[Dens_Gamma]](x, a, b)
+
:[[Dens_Gamma]](x, a, b)
  
 
Analytica equivalent of GAMMADIST(x, a, b, TRUE):
 
Analytica equivalent of GAMMADIST(x, a, b, TRUE):
[[GammaI]](x, a, b)
+
:[[GammaI]](x, a, b)
  
 
To define a variable as uncertain with a gamma probability distribution, use:
 
To define a variable as uncertain with a gamma probability distribution, use:
[[Gamma]](a, b)
+
:[[Gamma]](a, b)
  
''Note: [[Dens_Gamma]] is in the '''Distribution Densities''' library''
+
''Note'': [[Dens_Gamma]] is in the [[Distribution Densities Library]]
  
 
== GAMMAINV(p, a, b) or GAMMA.INV(p, a, b) ==
 
== GAMMAINV(p, a, b) or GAMMA.INV(p, a, b) ==
  
Analytica equivalent:
+
''Analytica equivalent:''
[[GammaIInv]](p, a, b)
+
:[[GammaIInv]](p, a, b)
  
 
== GAMMALN(x) ==
 
== GAMMALN(x) ==
  
Analytica equivalent:
+
''Analytica equivalent:''
[[LGamma]](x)
+
:[[LGamma]](x)
  
 
== GEOMEAN(x1, x2,...) ==
 
== GEOMEAN(x1, x2,...) ==
  
 
The Analytica equivalent is this [[Statistical Functions and Importance Weighting|statistical function]]:
 
The Analytica equivalent is this [[Statistical Functions and Importance Weighting|statistical function]]:
Function GeoMean(x : ContextSamp[I] ; I : Index = Run)
+
:Function GeoMean(x : ContextSamp[I] ; I : Index = Run)
Definition: [[Exp]]([[Mean]]([[Ln]](x), I))
+
:Definition: [[Exp]]([[Mean]]([[Ln]](x), I))
  
 
== GROWTH ==
 
== GROWTH ==
Line 287: Line 296:
  
 
Given an array «x» indexed by «I», the Harmonic mean in Analytica is computed as:
 
Given an array «x» indexed by «I», the Harmonic mean in Analytica is computed as:
[[Sum]](1, I) / [[Sum]](1/x, I)
+
:[[Sum]](1, I)/[[Sum]](1/x, I)
  
 
== HYPGEOMDIST(sample_s, number_sample, population_s, number_pop) ==
 
== HYPGEOMDIST(sample_s, number_sample, population_s, number_pop) ==
  
 
The Analytica equivalent is:
 
The Analytica equivalent is:
[[Prob_HyperGeometric]](sample_s, number_sample, population, number_pop)
+
:[[Prob_HyperGeometric]](sample_s, number_sample, population, number_pop)
  
 
== HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative) ==
 
== HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative) ==
  
 
When «cumulative» is false, the Analytica equivalent is:
 
When «cumulative» is false, the Analytica equivalent is:
[[Prob_HyperGeometric]](sample_s, number_sample, population, number_pop)
+
:[[Prob_HyperGeometric]](sample_s, number_sample, population, number_pop)
  
 
When «cumulative» is true, the Analytica equivalent is:
 
When «cumulative» is true, the Analytica equivalent is:
[[Sum]]([[Prob_HyperGeometric]](0..sample_s, number_sample, population, number_pop))
+
:[[Sum]]([[Prob_HyperGeometric]](0..sample_s, number_sample, population, number_pop))
  
 
== INTERCEPT(known_y, known_x) ==
 
== INTERCEPT(known_y, known_x) ==
  
 
Assume «known_y» and «known_x» share index «I».  The Analytica equivalent is:
 
Assume «known_y» and «known_x» share index «I».  The Analytica equivalent is:
Index K := ['b', 'm'];
+
:Index K := ['b', 'm'];
Regression(known_y, Array(K, [1, known_x]), I, K) [K = 'b']
+
:Regression(known_y, Array(K, [1, known_x]), I, K) [K = 'b']
  
 
== KURT(number1'', number2,...'') ==
 
== KURT(number1'', number2,...'') ==
  
Analytica equivalents:
+
"Analytica equivalents:''
[[Kurtosis]](X)
+
:[[Kurtosis]](X)
[[Kurtosis]](X,I)
+
':[[Kurtosis]](X,I)
  
 
== LARGE(array, k) ==
 
== LARGE(array, k) ==
  
 
Assume ''array'' is indexed by I.  One possible Analytica equivalent is:
 
Assume ''array'' is indexed by I.  One possible Analytica equivalent is:
[[Var..Do|Var]] n := [[Sum]](1, I);
+
:[[Var..Do|Var]] n := [[Sum]](1, I);
X[I = [[ArgMax]]([[Rank]](array, I)=n + 1- k, I)]
+
:X[I = [[ArgMax]]([[Rank]](array, I) = n + 1- k, I)]
  
 
== LINEST ==
 
== LINEST ==
Line 324: Line 333:
 
== LOGINV(p, lm, lsd) ==
 
== LOGINV(p, lm, lsd) ==
  
The Analytica equivalent is:
+
''The Analytica equivalent is:''
[[CumLogNormalInv]](p, [[Exp]](lm), [[Exp]](lsd))
+
:[[CumLogNormalInv]](p, [[Exp]](lm), [[Exp]](lsd))
  
 
== LOGNORMDIST(x, lm, ls) ==
 
== LOGNORMDIST(x, lm, ls) ==
  
The Analytica equivalent is:
+
''The Analytica equivalent is:''
[[CumLogNormal]](x, [[Exp]](lm), [[Exp]](ls))
+
:[[CumLogNormal]](x, [[Exp]](lm), [[Exp]](ls))
  
 
== LOGNORM.DIST(x, lm, ls, cumulative) ==
 
== LOGNORM.DIST(x, lm, ls, cumulative) ==
  
When ''cumulative'' is true, the Analytica equivalent is
+
When «cumulative» is true, the Analytica equivalent is
[[CumLogNormal]](x, [[Exp]](lm), [[Exp]](ls))
+
:[[CumLogNormal]](x, [[Exp]](lm), [[Exp]](ls))
  
When ''cumulative'' is false, the Analytica equivalent is
+
When «cumulative» is false, the Analytica equivalent is
[[Dens_LogNormal]](x, [[Exp]](lm), [[Exp]](ls))
+
:[[Dens_LogNormal]](x, [[Exp]](lm), [[Exp]](ls))
  
 
''Note: [[Dens_LogNormal]] and [[CumLogNormal]] are in the '''Distribution Densities''' library''.
 
''Note: [[Dens_LogNormal]] and [[CumLogNormal]] are in the '''Distribution Densities''' library''.
Line 344: Line 353:
 
== LOGNORM.INV(p, lm,  ls) ==
 
== LOGNORM.INV(p, lm,  ls) ==
  
The Analytica equivalent is:
+
''The Analytica equivalent is:''
[[CumLogNormalInv]](p, [[Exp]](lm), [[Exp]](ls))
+
:[[CumLogNormalInv]](p, [[Exp]](lm), [[Exp]](ls))
  
''Note: [[CumLogNormalInv]] is in the '''Distribution Densities''' library''.
+
''Note'': [[CumLogNormalInv]] is in the [[Distribution Densities Library]].
  
 
== MAX(number1'', number2,...'') or MAXA(number1'', number2,...'')==
 
== MAX(number1'', number2,...'') or MAXA(number1'', number2,...'')==
  
 
When MAX is applied in Excel to individual numbers, the Analytica equivalent is:
 
When MAX is applied in Excel to individual numbers, the Analytica equivalent is:
[[Max]]( [number1, number2, ...] )
+
:[[Max]]( [number1, number2, ...] )
 
Take note of the square brackets.
 
Take note of the square brackets.
  
 
When MAX is used in Excel by providing it with a range of cells, the Analytica equivalent is  
 
When MAX is used in Excel by providing it with a range of cells, the Analytica equivalent is  
[[Max]](A,I)
+
:[[Max]](A,I)
 
where «A» is the array of values (analogous to Excel's range) and «I» is the index to take the max over.  In the two-D case, where you want the Max over a 2-D region, this becomes:
 
where «A» is the array of values (analogous to Excel's range) and «I» is the index to take the max over.  In the two-D case, where you want the Max over a 2-D region, this becomes:
[[Max]](A, I, J)
+
:[[Max]](A, I, J)
  
 
In Excel, MAX ignores text (non-numbers) while MAXA does not.  To ignore non-numbers, use the optional «ignoreNonNumbers» parameter:
 
In Excel, MAX ignores text (non-numbers) while MAXA does not.  To ignore non-numbers, use the optional «ignoreNonNumbers» parameter:
[[Max]](A, I, ignoreNonNumbers: true)
+
:[[Max]](A, I, ignoreNonNumbers: true)
  
 
== MEDIAN(number1'', number2,...'') ==
 
== MEDIAN(number1'', number2,...'') ==
  
 
When MEDIAN is applied in Excel to individual numbers, the Analytica equivalent is:
 
When MEDIAN is applied in Excel to individual numbers, the Analytica equivalent is:
Index x := [number1, number2,...];
+
:Index x := [number1, number2,...];
[[GetFract]]( x, 0.5, x )
+
:[[GetFract]](x, 0.5, x)
  
 
In the more usual case, when MEDIAN is used in Excel by providing it with a range of cells, the Analytica equivalent is  
 
In the more usual case, when MEDIAN is used in Excel by providing it with a range of cells, the Analytica equivalent is  
[[GetFract]](A, 0.5, I)
+
:[[GetFract]](A, 0.5, I)
 
where «A» is the array of values (analogous to Excel's range) and «I» is the index to take the median over.
 
where «A» is the array of values (analogous to Excel's range) and «I» is the index to take the median over.
  
Line 376: Line 385:
  
 
When MIN or MINA is applied in Excel to individual numbers, the Analytica equivalent is:
 
When MIN or MINA is applied in Excel to individual numbers, the Analytica equivalent is:
[[Min]]( [number1, number2,...] )
+
:[[Min]]([number1, number2,...])
 
Take note of the square brackets.
 
Take note of the square brackets.
  
 
When MIN or MINA is used in Excel by providing it with a range of cells, the Analytica equivalent is  
 
When MIN or MINA is used in Excel by providing it with a range of cells, the Analytica equivalent is  
[[Min]](A, I)
+
:[[Min]](A, I)
 
where «A» is the array of values (analogous to Excel's range) and «I» is the index to take the minimum over.  In the two-D case, where you want the minimum over a 2-D region, this becomes:
 
where «A» is the array of values (analogous to Excel's range) and «I» is the index to take the minimum over.  In the two-D case, where you want the minimum over a 2-D region, this becomes:
[[Min]](A, I, J)
+
:[[Min]](A, I, J)
  
 
In Excel, MIN ignores text and non-numbers, while MINA does not.  To ignore text, etc., use the optional «ignoreNonNumbers» parameter:
 
In Excel, MIN ignores text and non-numbers, while MINA does not.  To ignore text, etc., use the optional «ignoreNonNumbers» parameter:
[[Min]](A, I, ignoreNonNumbers: true)
+
:[[Min]](A, I, ignoreNonNumbers: true)
  
 
== MODE(range) ==
 
== MODE(range) ==
  
 
Assuming the values in Analytica are in an array «A» indexed by «I», the equivalent is:
 
Assuming the values in Analytica are in an array «A» indexed by «I», the equivalent is:
 
+
:[[Index..Do|Index]] V := [[Unique]](Va1,Va1);
[[Index..Do|Index]] V := [[Unique]](Va1,Va1);
+
:[[ArgMax]]([[Frequency]](Va1, V, Va1),V)
[[ArgMax]]([[Frequency]](Va1, V, Va1),V)
+
  
 
== MODE.MULT(x1'', x2,...'') ==
 
== MODE.MULT(x1'', x2,...'') ==
Line 401: Line 409:
 
== NORMDIST(x, mean, standard_dev'', cumulative'') ==
 
== NORMDIST(x, mean, standard_dev'', cumulative'') ==
  
If ''Cumulative''=True, the Analytica equivalent is:
+
If «cumulative» = True, the Analytica equivalent is:
[[CumNormal]](x, mean, standard_dev)
+
:[[CumNormal]](x, mean, standard_dev)
  
If ''Cumulative''=False, the Analytica equivalent is:
+
If «cumulative» = False, the Analytica equivalent is:
[[Dens_Normal]](x, mean, standard_dev)
+
:[[Dens_Normal]](x, mean, standard_dev)
  
 
Note that to use [[Dens_Normal]], you must include the distribution densities library in your model.
 
Note that to use [[Dens_Normal]], you must include the distribution densities library in your model.
Line 411: Line 419:
 
== NORMINV(p, mean, standard_dev) ==
 
== NORMINV(p, mean, standard_dev) ==
  
Analytica equivalent:
+
''Analytica equivalent:''
[[CumNormalInv]](p,mean,standard_dev)
+
:[[CumNormalInv]](p,mean,standard_dev)
  
 
== NORMSDIST(z) ==
 
== NORMSDIST(z) ==
  
Analytica equivalent:
+
''Analytica equivalent:''
[[CumNormal]](z)
+
:[[CumNormal]](z)
  
 
== NORMSINV(p) ==
 
== NORMSINV(p) ==
  
Analytica equivalent:
+
''Analytica equivalent:''
  [[CumNormalInv]](p)
+
:[[CumNormalInv]](p)
  
 
== PEARSON(array1, array2) ==
 
== PEARSON(array1, array2) ==
  
Analytica equivalent:
+
''Analytica equivalent:''
[[Correlation]](array1, array2, I)
+
:[[Correlation]](array1, array2, I)
 +
 
 
where the data points in «array1» and «array2» both are indexed by «I».
 
where the data points in «array1» and «array2» both are indexed by «I».
  
Line 434: Line 443:
  
 
Assume that the array of values is indexed by «I».  Then the Analytica equivalent is:
 
Assume that the array of values is indexed by «I».  Then the Analytica equivalent is:
[[GetFract]](array, p, I)
+
:[[GetFract]](array, p, I)
  
 
== PERCENTRANK(array, x'', significance'') ==
 
== PERCENTRANK(array, x'', significance'') ==
  
Returns the rank of a value in a data set as a percentage of the data set.  The optional parameter, «significance», defaults to 3 in Excel.  Assume your array is ''A'' and is indexed by ''I''.  If you want the percent rank for all elements of A to full precision use:
+
Returns the rank of a value in a data set as a percentage of the data set.  The optional parameter, «significance», defaults to 3 in Excel.  Assume your array is ''A'' and is indexed by ''I''.  If you want the percent rank for all elements of ''A'' to full precision use:
  
([[Rank]](A, I) - 1) / ([[Size]](I) - 1)
+
:([[Rank]](A, I) - 1)/([[Size]](I) - 1)
  
 
If you want the percent rank for all elements, but only to the indicated significance, use:
 
If you want the percent rank for all elements, but only to the indicated significance, use:
 
+
:[[Floor]](([[Rank]](A, I) - 1)/([[Size]](I) - 1), significance)
[[Floor]]( ([[Rank]](A, I) - 1) / ([[Size]](I) - 1), significance )
+
  
 
If you want to get the percent rank of a single element, use:
 
If you want to get the percent rank of a single element, use:
 
+
:[[Floor]]([[Sum]](A < x, I)/([[Size]](I) - 1), significance)
[[Floor]]( [[Sum]](A < x, I) / ([[Size]](I) - 1), significance )
+
  
 
== PERMUT(n, k) ==
 
== PERMUT(n, k) ==
  
 
Analytica equivalent:
 
Analytica equivalent:
[[Permutations]](n, k)
+
:[[Permutations]](n, k)
  
 
== POISSON(x, mean, cumulative) ==
 
== POISSON(x, mean, cumulative) ==
  
 
When «cumulative is <code>false</code>, the Analytica equivalent is
 
When «cumulative is <code>false</code>, the Analytica equivalent is
[[Prob_Poisson]](x, mean)
+
:[[Prob_Poisson]](x, mean)
  
 
When «cumulative» is <code>true</code>, the Analytica equivalent is
 
When «cumulative» is <code>true</code>, the Analytica equivalent is
[[CumPoisson]](x, mean)
+
:[[CumPoisson]](x, mean)
  
 
In Analytica models, you'll often use the [[Poisson]] distribution function directly, rather than evaluating the probability or cumulative probability at a given point.
 
In Analytica models, you'll often use the [[Poisson]] distribution function directly, rather than evaluating the probability or cumulative probability at a given point.
Line 468: Line 475:
  
 
In Analytica, «x_range» and «prob_range» will share an index, call it «I».  When «upper_limit» is not specified, then the Analytica equivalent is:
 
In Analytica, «x_range» and «prob_range» will share an index, call it «I».  When «upper_limit» is not specified, then the Analytica equivalent is:
[[Sum]](prob_range * (lower_limit <= x_range ), I)
+
:[[Sum]](prob_range * (lower_limit <= x_range ), I)
  
When ''upper_limit'' is specified, this becomes
+
When «upper_limit» is specified, this becomes
[[Sum]](prob_range * (lower_limit <= x_range and x_range <= upper_limit), I)
+
:[[Sum]](prob_range * (lower_limit <= x_range and x_range <= upper_limit), I)
  
 
== QUARTILE(array, quart) ==
 
== QUARTILE(array, quart) ==
  
 
Analytica equivalent, where «array» is assumed indexed by «I»:
 
Analytica equivalent, where «array» is assumed indexed by «I»:
[[GetFract]](array, quart/4, I)
+
:[[GetFract]](array, quart/4, I)
  
 
== RANK(number, range'', order'') ==
 
== 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:
+
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]
+
:[[Rank]](range, I, type: -1)[I = number]
  
When ''order'' is omitted or zero in Excel, the Analytica equivalent is:
+
When «order» is omitted or zero in Excel, the Analytica equivalent is:
[[Rank]](-range, I, type: 1)[I = number]
+
:[[Rank]](-range, I, type: 1)[I = number]
  
 
== RSQ(known_y, known_x) ==
 
== RSQ(known_y, known_x) ==
  
 
Analytica equivalent:
 
Analytica equivalent:
[[Correlation]](known_y, known_x, I)^2
+
:[[Correlation]](known_y, known_x, I)^2
  
 
== SKEW(number1'', number2,...'') ==
 
== SKEW(number1'', number2,...'') ==
  
 
Analytica equivalent (assume the numbers are in array ''X'', indexed by ''I''):
 
Analytica equivalent (assume the numbers are in array ''X'', indexed by ''I''):
[[Skewness]](X, I)
+
:[[Skewness]](X, I)
  
 
When ''X'' is an uncertain distribution, indexed by [[Run]], the equivalent is:
 
When ''X'' is an uncertain distribution, indexed by [[Run]], the equivalent is:
[[Skewness]](X)
+
:[[Skewness]](X)
  
 
== SLOPE(known_y, known_x) ==
 
== SLOPE(known_y, known_x) ==
  
 
Assume ''known_y'' and ''known_x'' share index ''I''.  The Analytica equivalent is:
 
Assume ''known_y'' and ''known_x'' share index ''I''.  The Analytica equivalent is:
Index K := ['b', 'm'];
+
:[[Index]] K := ['b', 'm'];
Regression( known_y, Array(K, [1, known_x]), I, K ) [ K = 'm' ]
+
:[[Regression]](known_y, [[Array]](K, [1, known_x]), I, K) [K = 'm']
 
+
  
 
== SMALL(array, k) ==
 
== SMALL(array, k) ==
  
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)]
+
:X[I = [[ArgMax]]([[Rank]](array, I) = k, I)]
  
  
 
== STANDARDIZE(x, m, sd) ==
 
== STANDARDIZE(x, m, sd) ==
  
Analytica equivalent:
+
''Analytica equivalent:''
(x-m)/sd
+
:(x-m)/sd
  
 
== STDEV(number1'', number2,...'') ==
 
== STDEV(number1'', number2,...'') ==
  
 
Analytica equivalent (assume that the numbers are in array «X» indexed by «I»):
 
Analytica equivalent (assume that the numbers are in array «X» indexed by «I»):
[[SDeviation]](X, I)
+
:[[SDeviation]](X, I)
  
 
== STDEVP ==
 
== STDEVP ==
  
 
Analytica equivalent (assume that the numbers are in array «X» indexed by «I»):
 
Analytica equivalent (assume that the numbers are in array «X» indexed by «I»):
[[SDeviation]](X, I) * ([[Sum]](1, I) - 1)/[[Sum]](1, I)
+
:[[SDeviation]](X, I) * ([[Sum]](1, I) - 1)/[[Sum]](1, I)
  
 
== STEYX(known_ys, known_xs) ==
 
== STEYX(known_ys, known_xs) ==
  
To express the Analytica equivalent, I recommend first introducting an index and a function to your model:
+
To express the Analytica equivalent, we recommend first introducing an index and a function to your model:
Index Bm := ['b', 'm']
+
:[[Index]] Bm := ['b', 'm']
Function Basis_Bm(x) := Table(Bm)(1, x)
+
:Function Basis_Bm(x) := [[Table]](Bm)(1, x)
  
These functions make simple ''y=m*x+b'' linear [[Regression|regression]] very convenient.  With these, the equivalent is, assuming the common index between ''known_ys'' and ''known_xs'' is ''I'':
+
These functions make simple ''y = m*x + b'' linear [[Regression|regression]] very convenient.  With these, the equivalent is, assuming the common index between «known_ys» and «known_xs» is ''I'':
  
[[Var..Do|Var]] c := [[Regression]](known_ys, Basis_Bm(known_xs), I, Bm);
+
:[[Var..Do|Var]] c := [[Regression]](known_ys, Basis_Bm(known_xs), I, Bm);
known_ys - [[Sum]](c * Basis_Bm(known_xs), Bm )
+
:known_ys - [[Sum]](c * Basis_Bm(known_xs), Bm )
  
 
== TDIST(x, dof, tails) ==
 
== TDIST(x, dof, tails) ==
  
The Analytica equivalent is:
+
''The Analytica equivalent is:''
tails * (1 - [[CumStudentT]](x, dof))
+
:tails * (1 - [[CumStudentT]](x, dof))
  
 
== TINV(p, dof) ==
 
== TINV(p, dof) ==
  
Analytica equivalent:
+
''Analytica equivalent:''
[[CumStudentTInv]](1 - p/2, dof)
+
:[[CumStudentTInv]](1 - p/2, dof)
  
 
Excel has a strange notion of a 2-tailed Student-T distribution, which basically multiplies the probability by 2.  See its TDIST function.  TINV seems to do the inverse of this 2-tailed case, which leads to this strange equivalence.
 
Excel has a strange notion of a 2-tailed Student-T distribution, which basically multiplies the probability by 2.  See its TDIST function.  TINV seems to do the inverse of this 2-tailed case, which leads to this strange equivalence.
Line 552: Line 558:
 
== TREND(known_y'', known_x, new_x, const'') ==
 
== 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 0 (false) for a ''y=m*x+b'' curve, and 1 (true) for a ''y=m*b'' fit.  Then the Analytica equivalent is:
+
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 0 (false) for a ''y = m*x + b'' curve, and 1 (true) for a ''y = m*b'' fit.  Then the Analytica equivalent is:
[[Index..Do|Index]] K := ['b', 'm'];
+
:[[Index..Do|Index]] K := ['b', 'm'];
[[Sum]]([[Regression]](known_y, Array(K, [not const, known_x]), I, K) * Array(K, [not const,new_x]), K)
+
:[[Sum]]([[Regression]](known_y, Array(K, [not const, known_x]), I, K) * Array(K, [not const, new_x]), K)
  
The result is the set of predicted ''new_y'' values, indexed by ''J''.
+
The result is the set of predicted «new_y» values, indexed by ''J''.
  
 
== TRIMMEAN(array, percent) ==
 
== TRIMMEAN(array, percent) ==
  
 
Assume the array is indexed by ''I''.  The Analytica equivalent is
 
Assume the array is indexed by ''I''.  The Analytica equivalent is
[[Var..Do|Var]] n := [[Sum]](1, I);
+
:[[Var..Do|Var]] n := [[Sum]](1, I);
[[Mean]]( A[I = [[SortIndex]](A, I)], I, w:@I > percent*n/2 and @I < n + 1-(percent*n/2))
+
:[[Mean]]( A[I = [[SortIndex]](A, I)], I, w: @I > percent*n/2 and @I < n + 1 - (percent*n/2))
  
 
== TTEST ==
 
== TTEST ==
Line 568: Line 574:
  
 
Analytica equivalent (assume numbers are in array «X» indexed by «I»):
 
Analytica equivalent (assume numbers are in array «X» indexed by «I»):
[[Variance]](X, I)
+
:[[Variance]](X, I)
  
 
== VARP ==
 
== VARP ==
  
 
Analytica equivalent (assume numbers are in array «X» indexed by «I»):
 
Analytica equivalent (assume numbers are in array «X» indexed by «I»):
[[Variance]](X, I) * ([[Sum]](1, I) - 1)/[[Sum]](1, I)
+
:[[Variance]](X, I) * ([[Sum]](1, I) - 1)/[[Sum]](1, I)
 
+
  
 
== WEIBULL(x, alpha, beta, cumulative) ==
 
== WEIBULL(x, alpha, beta, cumulative) ==
  
 
When <code>cumulative = false</code>, the Analytica equivalent is:
 
When <code>cumulative = false</code>, the Analytica equivalent is:
[[Dens_Weibull]](x, alpha, beta)
+
:[[Dens_Weibull]](x, alpha, beta)
  
 
When <code>cumulative = true</code>, the Analytica equivalent is:
 
When <code>cumulative = true</code>, the Analytica equivalent is:
[[CumWeibull]](x, alpha, beta)
+
:[[CumWeibull]](x, alpha, beta)
  
 
== ZTEST ==
 
== ZTEST ==

Revision as of 23:29, 16 March 2016


Contents

AVEDEV(x1, x2,...)

Analytica equivalents are Mean(x - Mean(x)) and Mean(x - Mean(x, I), I).

AVERAGE(x1, x2,...), AVERAGEA(x1, x2,...)

Analytica equivalent is

Average(x, I).

AVERAGEA

AVERAGEIF

AVERAGEIFS

BETADIST(x, alpha, beta, A, B)

Analytica equivalents are

BetaI(x, alpha, beta)

and

BetaI((x - A)/(B - A), alpha, beta).

To define a variable as a beta probability distribution, use:

Beta(alpha, beta, A, B)

BETA.DIST(x, alpha, beta, cumulative, A, B)

BETAINV(p, alpha, beta, A, B)

This is named BETA.INV(p, alpha, beta, A, B) in Excel 2010 , but the equivalent is the same.

Analytica equivalents are BetaIInv(p, alpha, beta) and 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.

BINOM.DIST(number_s, trials, probability_s, cumulative)

BINOM.INV(trials, probability_s, alpha)

The CumBinomialInv function found in the "Distribution Densities.ana" library starting with Analytica 4.4.3 provides the equivalent:

CumBinomialInv(alpha, trials, probability_s)

CHIDIST(x, dof) or CHISQ.DIST(x, deg_freedom, cumulative)

Analytica equivalent for CHIDIST or CHISQ.DIST when cumulative is false:

Dens_ChiSquared(x, dof)

Equivalent of CHISQ.DIST when cumulative is true:

CumChiSquared(x, dof)

Both Dens_ChiSquared and CumChiSquared are in the Distribution Densities Library.

CHIINV(p, dof) or CHISQ.INV(p, dof)

Analytica equivalent:

2 * GammaIInv(p, dof/2)

CHISQ.INV.RT(p, dof)

CHITEST(actual,expected) or CHISQ.TEST(actual, expected)

For the 1-D case, where «actual» and «expected» are each 1-D, in Analytica these will have a common index, I, and the Analytica equivalent is:

 Var n := Sum(1, I);
 Var chi2 := Sum((actual - expected)^2/expected, I);
 1 - GammaI(chi2/2, (n - 1)/2)

For the 2-D contingency table analysis, with indexes I and J, the equivalent is:

 Var n := Sum(1, I, J);
 Var chi2 := Sum((actual - expected)^2/expected, I, J);
 1 - GammaI(chi2/2, (n - 1)/2)

CONFIDENCE(alpha, sd, n) or CONFIDENCE.NORM(alpha, sd, n)

Analytica equivalent:

CumNormalInv(0.5 + (1 - alpha)/2, 0, sd ) / Sqrt(n)

CONFIDENCE.T(alpha, sd, n)

Analytica equivalent:

CumStudentTInv(alpha/2, n - 1)*sd/Sqrt(n)

Note: CumStudentTInv is in the Distribution Densities Library.

CORREL(x, y)

Analytica equivalents are Correlation(x, y) and 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)

COUNTIFS(range1, criteria1, range2, critieria2, range3, criteria3,...)

Most cases where you would use this function in Excel involve collections of data that are logically 3 or 4 dimensional, but which have to be broken into multiple tables because of Excel's intrinsic restriction to two dimensions. Hence, multiple ranges are naturally handled via array abstraction without a need for special handling.

For example, in an array named range with 4 dimensions: I,J,K and L, to count the number of values greater than 55 you would use:

Sum(range > 55, I, J, K, L)

If the criteria varies along one or more of the dimensions, then your criteria would simply be indexed by that. For example, if you have separate bounds for each value along L, you would create an array, Lb, indexed by L and containing the lower bounds, and then use:

Sum(range > lb, I, J, K, L)

COVAR(array1, array2) or COVARIANCE.S(array1, array2)

Analytica equivalent:

Covariance(array1, array2,I)

COVARIANCE.P(array1, array2)

Analytica equivalent:

Var n := Sum(1, I);
Covariance(array1, array2, I)*(n - 1)/n

CRITBINOM(alpha, trials, probability_s)

The CRITBINOM computes the inverse CDF for a binomial distribution. The CumBinomialInv function found in the "Distribution Densities.ana" library starting with Analytica 4.4.3 provides the equivalent:

CumBinomialInv(alpha, trials, probability_s)

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(x, lambda, cumulative) or EXPON.DIST(x, lambda, cumulative)

If cumulative is false, the Analytica equivalent is:

Dens_Exponential(x, 1/lambda)

When cumulative is true, the Analytica equivalent is:

CumExponential(x, 1/lambda)

Note: These functions are in the Distribution Densities Library

F.DIST(x, dof1, dof2, cumulative)

When «cumulative» is true, the Analytica equivalent is:

CumFDist(x, dof1, dof2)

When «cumulative» is false:

Dens_FDist(x, dof1, dof2)

Note: CumFDist and Dens_FDist are in the Distribution Densities Library

FDIST(x, dof1, dof2) or F.DIST.RT(x, dof1, dof2)

The Analytica equivalent is

1-CumFDist(x, dof1, dof2)

Note: CumFDist is the Distribution Densities Library

F.INV(p, dof1, dof2)

The Analytica equivalent is:

CumFDistInv(p, dof1, dof2)

FINV(p, dof1, dof2) or F.INV.RT(p, dof1, dof2)

The Analytica equivalent is:

CumFDistInv(1 - p, dof1, dof2)

F.TEST(array1, array2)

The Analytica equivalent, assuming «array1» is indexed by «I» and «array2» is indexed by «J» is:

 Var F0 := Variance(array1, I)/Variance(array2, J);
 Var F  := Max([F0,1/F0]);
 2 * (1- CumFDist(F, Sum(1, I) - 1, Sum(1, J) - 1))

FISHER(x)

The Analytica equivalent is:

Ln((1 + x)/(1 - x))/2

FISHERINV(y)

The Analytica equivalent is:

Var e2y := Exp(2*y) Do (e2y - 1)/(e2y + 1)

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 equivalents:

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(array1, array2)

The following User-Defined Function provides an Analytica equivalent:

 Function FTest(A1: ContextSamp[I], A2 : ContextSamp[J] ; I, J : Index = Run)
 Definition:
   Var v1 := Variance(A1, I);
   Var v2 := Variance(A2, J);
    Var n1 := Sum(1, I);
    Var n2 := Sum(1, J);
    Var F := v2/v1;
    1 - CumFDist(F, n1 - 1, n2 - 1)

Add the above UDF to your model, then you can just use:

FTest(array1, array2, I, J)

where «I» and «J» are the indexes of «array1» and «array2» respectively.

GAMMADIST(x, a, b, cumulative) or GAMMA.DIST(x, a, b, cumulative)

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)

Note: Dens_Gamma is in the Distribution Densities Library

GAMMAINV(p, a, b) or GAMMA.INV(p, a, b)

Analytica equivalent:

GammaIInv(p, a, b)

GAMMALN(x)

Analytica equivalent:

LGamma(x)

GEOMEAN(x1, x2,...)

The Analytica equivalent is this statistical function:

Function GeoMean(x : ContextSamp[I] ; I : Index = Run)
Definition: Exp(Mean(Ln(x), I))

GROWTH

HARMEAN(x1, x2,...)

Given an array «x» indexed by «I», the Harmonic mean in Analytica is computed as:

Sum(1, I)/Sum(1/x, I)

HYPGEOMDIST(sample_s, number_sample, population_s, number_pop)

The Analytica equivalent is:

Prob_HyperGeometric(sample_s, number_sample, population, number_pop)

HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative)

When «cumulative» is false, the Analytica equivalent is:

Prob_HyperGeometric(sample_s, number_sample, population, number_pop)

When «cumulative» is true, the Analytica equivalent is:

Sum(Prob_HyperGeometric(0..sample_s, number_sample, population, number_pop))

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(p, lm, lsd)

The Analytica equivalent is:

CumLogNormalInv(p, Exp(lm), Exp(lsd))

LOGNORMDIST(x, lm, ls)

The Analytica equivalent is:

CumLogNormal(x, Exp(lm), Exp(ls))

LOGNORM.DIST(x, lm, ls, cumulative)

When «cumulative» is true, the Analytica equivalent is

CumLogNormal(x, Exp(lm), Exp(ls))

When «cumulative» is false, the Analytica equivalent is

Dens_LogNormal(x, Exp(lm), Exp(ls))

Note: Dens_LogNormal and CumLogNormal are in the Distribution Densities library.

LOGNORM.INV(p, lm, ls)

The Analytica equivalent is:

CumLogNormalInv(p, Exp(lm), Exp(ls))

Note: CumLogNormalInv is in the Distribution Densities Library.

MAX(number1, number2,...) or MAXA(number1, number2,...)

When MAX is applied in Excel to individual numbers, the Analytica equivalent is:

Max( [number1, number2, ...] )

Take note of the square brackets.

When MAX is used in Excel by providing it with a range of cells, the Analytica equivalent is

Max(A,I)

where «A» is the array of values (analogous to Excel's range) and «I» is the index to take the max over. In the two-D case, where you want the Max over a 2-D region, this becomes:

Max(A, I, J)

In Excel, MAX ignores text (non-numbers) while MAXA does not. To ignore non-numbers, use the optional «ignoreNonNumbers» parameter:

Max(A, I, ignoreNonNumbers: true)

MEDIAN(number1, number2,...)

When MEDIAN is applied in Excel to individual numbers, the Analytica equivalent is:

Index x := [number1, number2,...];
GetFract(x, 0.5, x)

In the more usual case, when MEDIAN is used in Excel by providing it with a range of cells, the Analytica equivalent is

GetFract(A, 0.5, I)

where «A» is the array of values (analogous to Excel's range) and «I» is the index to take the median over.

MIN(number1, number2,...) and MINA(number1, number2,...)

When MIN or MINA is applied in Excel to individual numbers, the Analytica equivalent is:

Min([number1, number2,...])

Take note of the square brackets.

When MIN or MINA is used in Excel by providing it with a range of cells, the Analytica equivalent is

Min(A, I)

where «A» is the array of values (analogous to Excel's range) and «I» is the index to take the minimum over. In the two-D case, where you want the minimum over a 2-D region, this becomes:

Min(A, I, J)

In Excel, MIN ignores text and non-numbers, while MINA does not. To ignore text, etc., use the optional «ignoreNonNumbers» parameter:

Min(A, I, ignoreNonNumbers: true)

MODE(range)

Assuming the values in Analytica are in an array «A» indexed by «I», the equivalent is:

Index V := Unique(Va1,Va1);
ArgMax(Frequency(Va1, V, Va1),V)

MODE.MULT(x1, x2,...)

MODE.SNGL(x1, x2,...)

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(array, p)

Assume that the array of values is indexed by «I». Then the Analytica equivalent is:

GetFract(array, p, I)

PERCENTRANK(array, x, significance)

Returns the rank of a value in a data set as a percentage of the data set. The optional parameter, «significance», defaults to 3 in Excel. Assume your array is A and is indexed by I. If you want the percent rank for all elements of A to full precision use:

(Rank(A, I) - 1)/(Size(I) - 1)

If you want the percent rank for all elements, but only to the indicated significance, use:

Floor((Rank(A, I) - 1)/(Size(I) - 1), significance)

If you want to get the percent rank of a single element, use:

Floor(Sum(A < x, I)/(Size(I) - 1), significance)

PERMUT(n, k)

Analytica equivalent:

Permutations(n, k)

POISSON(x, mean, cumulative)

When «cumulative is false, the Analytica equivalent is

Prob_Poisson(x, mean)

When «cumulative» is true, the Analytica equivalent is

CumPoisson(x, mean)

In Analytica models, you'll often use the Poisson distribution function directly, rather than evaluating the probability or cumulative probability at a given point.

PROB(x_range, prob_range, lower_limit, upper_limit)

In Analytica, «x_range» and «prob_range» will share an index, call it «I». When «upper_limit» is not specified, then the Analytica equivalent is:

Sum(prob_range * (lower_limit <= x_range ), I)

When «upper_limit» is specified, this becomes

Sum(prob_range * (lower_limit <= x_range and x_range <= upper_limit), I)

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(known_ys, known_xs)

To express the Analytica equivalent, we recommend first introducing an index and a function to your model:

Index Bm := ['b', 'm']
Function Basis_Bm(x) := Table(Bm)(1, x)

These functions make simple y = m*x + b linear regression very convenient. With these, the equivalent is, assuming the common index between «known_ys» and «known_xs» is I:

Var c := Regression(known_ys, Basis_Bm(known_xs), I, Bm);
known_ys - Sum(c * Basis_Bm(known_xs), Bm )

TDIST(x, dof, tails)

The Analytica equivalent is:

tails * (1 - CumStudentT(x, dof))

TINV(p, dof)

Analytica equivalent:

CumStudentTInv(1 - p/2, dof)

Excel has a strange notion of a 2-tailed Student-T distribution, which basically multiplies the probability by 2. See its TDIST function. TINV seems to do the inverse of this 2-tailed case, which leads to this strange equivalence.

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 0 (false) for a y = m*x + b curve, and 1 (true) for a y = m*b fit. Then the Analytica equivalent is:

Index K := ['b', 'm'];
Sum(Regression(known_y, Array(K, [not const, known_x]), I, K) * Array(K, [not const, new_x]), K)

The result is the set of predicted «new_y» values, indexed by J.

TRIMMEAN(array, percent)

Assume the array is indexed by I. The Analytica equivalent is

Var n := Sum(1, I);
Mean( A[I = SortIndex(A, I)], I, w: @I > percent*n/2 and @I < n + 1 - (percent*n/2))

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)

When cumulative = false, the Analytica equivalent is:

Dens_Weibull(x, alpha, beta)

When cumulative = true, the Analytica equivalent is:

CumWeibull(x, alpha, beta)

ZTEST

See Also

Comments


You are not allowed to post comments.