Excel to Analytica Mappings/Math Functions

< Excel to Analytica Mappings
Revision as of 23:48, 29 October 2008 by Lchrisman (Talk | contribs) (GCD(number1'',number2,...''))

This page shows how Excel Mathematical functions translate to Analytica equivalents

ABS(x)

Analytica equivalent:

Abs(x)

ACOS(x)

Analytica equivalent:

Radians(ArcCos(x))

ACOSH(x)

Analytica equivalent:

Ln(x+Sqrt( (x-1)*(x+1) )

ASIN(x)

Analytica equivalent:

Radians(ArcSin(x))

ASINH

Analytica equivalent:

Ln(x+Sqrt(x^2+1))

ATAN(x)

Analytica equivalent:

Radians(ArcTan(x))

ATAN2(x,y)

Analytica equivalent:

Radians(ArcTan2(y,x))

ATANH

Analytica equivalent:

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

CEILING(x)

Analytica equivalent:

Ceil(x)

Analytica does not provide an equivalent to CEILING(x,significance). You can approximate this using:

Ceil( x / abs(significance) ) * significance

This is only an approximation since round-off errors can through it off slightly.

COMBIN(n,k)

Analytica equivalent:

Combinations(k,n)

COS(x)

Analytica equivalent:

Cos(Degrees(x))

COSH

Analytica equivalent:

Cosh(x)

DEGREES(angle_in_radians)

Analytica equivalent:

Degrees(angle_in_radians)

EVEN(x)

Analytica equivalent:

2*Round(x/2)

EXP(x)

Analytica equivalent:

Exp(x)

FACT(n)

Analytica equivalent:

Factorial(n)

FACTDOUBLE(n)

This function is not provided by Analytica. The following user-defined function can be used:

Function FactDouble( n : scalar )
Definition: Product( Sequence(n,1,2) )

FLOOR(x)

Analytica equivalent:

Floor(x)

Analytica does not have an exact equivalent to FLOOR(x,significance), but this can be approximated as:

Floor(x/significance) * significance

This is only an approximation because numeric roundoff may cause slight descrepancies.

GCD(number1,number2,...)

Analytica does not have a built-in GCD function. If you need this, add the following two User-Defined Functions:

Function GCD2(x,y:scalar)
Description: Returns the greatest common divisor of two numbers.
Definition: While (x>1 and y>1) do ( 
               if x>y then ( x:=Mod(x,y) ) else (y:=Mod(y,x))
            );
            Max([x,y])
Function GCD(A:Vector[I] ; I : optional Index)
Description: Returns the greatest common divisor of an array of numbers.  
Definition: Var g:=Slice(A,1);
            if Size(A)>1 then For j:=2..Size(A) do g:=GCD2(g,Slice(A,j));
            g

With these, you can use, e.g.:

GCD([2297295,2457,10395]) → 189

or

GCD(A,I)

INT(x)

Analytica equivalent:

Floor(x)

LCM(number1,number2,...)

LN(x)

Analytica equivalent:

Ln(x)

LOG(x,base)

Analytica equivalent:

Ln(x) / Ln(base)

LOG10(x)

Analytica equivalent:

LogTen(x)

MDETERM(array)

Analytica equivalent:

Determinant(array,I,J)

where I and J are indexes of array.

MINVERSE(array)

Analytica equivalent:

Invert(array,I,J)

where I and J are indexes of array.

MMULT(array1,array2)

Analytica equivalent:

MatrixMultiply(array1,I1,J1,array2,I2,J2)

where I1 and J1 are the indexes of array1, and I2 and J2 are the indexes of array2. J1 and I2 must be of the same length.

MOD(number,divisor)

Analytica equivalent:

Mod(number,divisor)

MROUND(number,multiple)

There is no precise equivalent to this, but you can accomplish this using:

Round(number/multiple) * multiple

MULTINOMIAL(a,b,c,...)

If the numbers are small enough so that numeric overflow is not a problem, this can be encoded as follows, where the numbers are in array A along index I:

Factorial(Sum(A,I)) / Sum(Factorial(A),I)

ODD(number)

Analytica equivalent:

Ceil((number-1)/2) * 2 + 1

PI()

Analytica equivalent:

Pi

POWER(number,power)

Analytica equivalent:

number^power

PRODUCT(number1,number2,...)

Analytica equivalent:

number1 * number2 * ....

or if your numbers are in an array:

Product(A,I)

QUOTIENT(numerator,denominator)

Analytica equivalent:

numerator / denominator

RADIANS(angle_in_degrees)

Analytica equivalent:

Radians(angle_in_degrees)

RAND( )

Analytica equivalent:

Random()

RANDBETWEEN(bottom,top)

Analytica equivalent:

Random(Uniform(bottom,top,integer:true))

ROMAN

ROUND(number,num_digits)

The Analytica equivalent of ROUND(number,0) is:

Round(number)

ROUNDDOWN(number,num_digits)

Analytica equivalent of ROUNDDOWN(number,0) is:

If number<0 then Ceil(x) else Floor(x)

ROUNDUP(number,num_digits)

Analytica equivalent of ROUNDUP(number,0) is:

If number>0 then Ceil(x) else Floor(x)

SERIESSUM(x,n,m,coefficients)

When doing the equivalent in Analytica, your coefficient array will have an index, say I. The equivalent is then:

Sum( coefficients * x^(n+(@I-1)*m), I )

SIGN(x)

Analytica equivalent:

x/Abs(x)

SIN(x)

Analytica equivalent:

Sin(Degrees(x))

SINH(x)

Analytica equivalent:

Sinh(x)

SQRT(x)

Analytica equivalent:

Sqrt(x)

SQRTPI(x)

Analytica equivalent:

Sqrt(x*Pi)

SUBTOTAL

SUM(number1,number2,....)

Analytica equivalent:

number1 + number2 + ...

or when the numbers are in an array:

Sum(A,I)

SUMIF(range,criteria,sum_range)

The Analytica equivalent is:

Sum( if criteria then range else 0, I )

The index I would typically be shared by both criteria and range arrays. For example:

Sum( if est>0 then est else 0, Time )

When you are certain that range contains only finite numbers (no INF or NaN), then you can also use:

Sum( criteria * range, I )

E.g.:

Sum( (est>0) * est, I )

SUMPRODUCT(array1,array2,....)

Analytica equivalents:

Sum(array1*array2*..., I )
Sum(Product(A,J),I)

SUMSQ(number1,number2,...)

Analytica equivalents:

Sum([number1,number2,....]^2)
Sum(A^2,I)

SUMX2MY2(array-x,array_y)

Analytica equivalent:

Sum(array_x^2 - array_y^2,I)

SUMX2PY2(array_x,array_y)

Analytica equivalent:

Sum(array_x^2 + array_y^2,I)

SUMXMY2(array_x,array_y)

Analytica equivalent:

Sum( (array_x-array_y)^2, I )

TAN(x)

Analytica equivalent:

Tan(Degrees(x))

TANH(x)

Analytica equivalent:

Tanh(x)

TRUNC(x,num_digits)

Analytica equivalent of TRUNC(x):

x - Mod(x,1)

The equivalent of TRUNC(x,digits) is:

x - Mod(x,10^(-digits))
Comments


You are not allowed to post comments.