Home / Monte Carlo / Model Building / Supported Functions
Supported Functions¶
Quantum XL uses a proprietary math engine that is considerably faster than Excel's engine. However, Quantum XL does not support all of the functions that Excel supports. Below is a list of the functions and operators that Quantum XL supports.
| -- The trigonometric functions are in radians. |
|---|
| -- Text arguments are not supported. |
Math and trigonometry functions
Operators and constants¶
| Function | Description | Example |
|---|---|---|
| () | Prioritizes an expression | 5*(1+1) = 10 |
| ^ | Raised to the power of | 4 ^ 5 = 1024 |
| * | Multiply by | 3 * 6 = 18 |
| / | Divide by | 9 / 2 = 4.5 |
| \ | Integer divide by | 9 \ 2 = 4 |
| MOD | Modulo (remainder) | 7 mod 4 = 3 |
| + | Add | 1 + 1 = 2 |
| -- | Subtract | 9 -- 5 = 4 |
| > | Greater than | 9 > 2 = 1 |
| < | Less than | 7 < 4 = 0 |
| PI | Number PI | PI = 3.14 |
Math and trigonometry functions¶
| Function | Description | Example |
|---|---|---|
| ABS | Absolute value of a number | ABS(--1) = 1 |
| ATAN | Arctangent, or inverse tangent, of a number | ATAN(1) = 0.785398 = PI/4 |
| COMBIN | Number of combinations for a given number of items | COMBIN(8,2) = 28 |
| COS | Cosine | COS(PI) = --1 |
| DEGREES | Converts radians into degrees | DEGREES(PI) = 180 |
| EVEN | Round up to the nearest even integer | EVEN(1.5) = 2 |
| EXP | e raised to the power of number | EXP(1) = 2.718282 |
| FACT | Factorial of a number | FACT(4) = 24 |
| FLOOR | Rounds number down, toward zero, to the nearest multiple of significance | FLOOR(2.5,1) = 2 |
| INT | Rounds a number down to the nearest integer. | INT(10.2) = 10 |
| LN | Natural logarithm of a number | LN(EXP(2.1)) = 2.1 |
| LOG | Returns the logarithm of a number to the specified base | LOG(100,10) = 2 |
| LOG10 | Base 10 logarithm | LOG10(100) = LOG(100,10) = 2 |
| MAX | The largest value in a set of values | MAX(1,2,3,10) = 10 |
| MIN | The smallest number in a set of values | MIN(1,2,3,10) = 1 |
| ODD | Round up to the nearest odd integer | ODD(--2) = --3 |
| POWER | Returns the result of a number raised to a power | POWER(5.2)=25 |
| PRODUCT | Multiplies all the numbers given as arguments and returns the product | PRODUCT(1,3,5) = 15 |
| QUOTIENT | Returns the integer portion of a division | QUOTIENT(5,2) = 2 |
| RADIANS | Converts degrees to radians | RADIANS(180) = 3.14 |
| ROUND | Rounds a number to a specified number of digits | ROUND(2.15,1) = 2.2 |
| ROUNDDOWN | Rounds a number down, toward 0 (zero) | ROUNDDOWN(3.2,0) = 3 |
| ROUNDUP | Rounds a number up, away from 0 (zero) | ROUNDUP(3.2,0)=4 |
| SIN | Sine of the given angle | SIN(PI/2) = 1 |
| SQRT | Square root of a number | SQRT(9) = 3 |
| SUM | Adds all the numbers in a range of cells. | SUM(1,2,3,4) = 10 |
| SUMPRODUCT | Multiplies corresponding components in the given arrays, and returns the sum of those products | SUMPRODUCT({1,2,3},{2,3,4}) = 20 |
| TAN | Tangent of the given angle | TAN(45*PI()/180 ) = 1 |
| TRUNC | Truncates a number to an integer by removing the fractional part of the number | TRUNC(8.9) = 8 |
Logical functions¶
| Function | Description | Example |
|---|---|---|
| AND | Logical AND. Returns 0 if at least one argument is 0. | AND(1,0) = 0 |
| IF | Conditional test. | IF(1>2, --10, 20) = 20 |
| OR | Logical OR. Returns 1 if at least one argument is not 0. | OR(0,1,0) = 1 |
Statistical functions¶
| Function | Description | Example |
|---|---|---|
| AVEDEV | Average of the absolute deviations of data points from their mean. | AVEDEV(1,3,5) = 1.33333 |
| AVERAGE | Average (arithmetic mean) of the arguments. | AVERAGE(1,3,5) = 3 |
| BETADIST | Beta cumulative distribution. | BETADIST(2,8,10,1,3) = 0.685471 |
| CHIINV | Inverse of the one-tailed probability of the chi-squared distribution. | CHIINV(0.050001,10) = 18.307 |
| EXPONDIST | Exponential distribution. | EXPONDIST(0.5,10,1) = 0.993 |
| GAMMADIST | Gamma distribution. | GAMMADIST(10,9,2,1) = 0.068 |
| GEOMEAN | Geometric mean of an array or range of positive data. | GEOMEAN(1,2,3) = 1.82 |
| HARMEAN | Harmonic mean of a data set. | HARMEAN(1,2,3) = 1.64 |
| LOGINV | Inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard dev. | LOGINV(0.039084,3.5,1.2) = 4.000025 |
| LOGNORMDIST | Cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard dev. | LOGNORMDIST(4,3.5,1.2) = 0.039084 |
| MEDIAN | Median of the given numbers. | MEDIAN(1,2,3,4,5,6) = 3.5 |
| NORMDIST | Normal distribution for the specified mean and standard deviation. | NORMDIST(42,40,1.5,1) = 0.9 |
| PEARSON | Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from --1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets. | PEARSON({1,2,3},{1,2,4})=0.98 |
| PERMUT | Number of permutations for a given number of objects that can be selected from number objects. | PERMU(100,3) = 970200 |
| POISSON | Poisson distribution. | POISSON(2,5,1)=0.124652 |
| STDEV | Estimates standard deviation based on a sample. | STDEV(1,2,3,4,5) = 1.581139 |
| STDEVP | Calculates standard deviation based on the entire population given as arguments. | STDEVP(1,2,3,4,5) = 1.4142 |
| TINV | Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom. | TINV(5.46%,60) = 1.96 |
| VARP | Calculates variance based on the entire population. | VARP(1,2,3) = 0.667 |
| WEIBULL | Returns the Weibull distribution. | WEIBULL(105,20,100,1) |
Engineering functions¶
| Function | Description | Example | Notes |
|---|---|---|---|
| BesselI | Returns the modified Bessel function In(x) | BESSELI(1.5,1)=.981666 | Requires Analysis Toolpack |
| BesselJ | Returns the Bessel function Jn(x) | BESSELJ(3.4,5)=.071785 | Requires Analysis Toolpack |
| BesselK | Returns the modified Bessel function Kn(x) | BESSELK(1.11,2)=1.264246 | Requires Analysis Toolpack |
Financial functions¶
| Function | Description | Example | Notes |
|---|---|---|---|
| DB | Returns the depreciation of an asset for a specified period using the fixed-declining balance method. | DB(1000000,100000,6,1,7)=$186,083.33 | |
| DDB | Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify. | DDB(2400,300,10,1,2)=$480.00 | |
| EFFECT | Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year. | EFFECT(0.0525, 4) = 0.0535 | |
| FV | Returns the future value of an investment based on periodic, constant payments and a constant interest rate. | FV(0.06,10,--200,--500,1) = $2581.40 | |
| IPMT | Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. | IPMT(0.01,3,3,8000) = $(--292.45) | |
| IRR | Returns the internal rate of return for a series of cash flows represented by the numbers in values. | IRR(--70000,12000,15000,18000) = --18% | |
| MIRR | Returns the modified internal rate of return for a series of periodic cash flows. | MIRR(--70,12,15,18,0.1,0.12) = --11% | |
| NOMINAL | Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year. | NOMINAL(0.053543,4) = 5.25% | |
| NPER | Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. | NPER(0.12/12, --100,--1000,10000) = 60 | |
| NPV | Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). | NPV(0.01,--10000,3000,4200,6800) = 1188.44 | |
| PMT | Calculates the payment for a loan based on constant payments and a constant interest rate. | PMT(0.08/12,10,10000)=$(--1037.03) | |
| PV | Returns the present value of an investment. | PV(0.1/12,1,2*12,2400) = --2403,97 | |
| RATE | Returns the interest rate per period of an annuity. | RATE(4*12,--200,8000) = 1% | |
| SLN | Returns the straight-line depreciation of an asset for one period. | SLN(30000,7500,10) = $2250 | |
| SYD | Returns the sum-of-years' digits depreciation of an asset for a specified period. | SYD(30000,7500,10,1) = $4090.91 | |
External functions¶
(These functions will work in Excel only when Quantum XL is running.)
| Function | Description | Example |
|---|---|---|
| Image Functions | ||
| The image functions in Quantum XL are based on Lab (sometimes written L, a, b) color space as defined by the International Commission on Illumination (CIE). L is the lightness of color (L=0 is black and L=100 is white), a the red/green shift, and b is the yellow/blue shift. | ||
| CHROMA (L,A,B) | Returns the Chroma for a reference L, a, b | CHROMA(12,3,--4)=5 |
| HUE (L,A,B) | Returns the Hue for a reference L, a, b | HUE(44,2,12)=80.53768 |
| DELTAE2000 (L1,A1,B1,L2,A2,B2) | Returns the DeltaE 2000 value for two points in color space | DELTAE2000(2,12,12,30,12,12)=18.59669 |
| Other Functions | ||
| BetaPDF | Beta Probability Density Function | |
| BetaPDF | Beta Probability Density Function | |
| BinomialInverse | Inverse Binomial Cumulative Probability Function | |
| ChiSquarePDF | Chi Square Probability Density Function | |
| CombinationsWithRepetitions | Combinations of 'Number' things taken 'Chosen' at a time, WITH repetitions. | |
| ExponentialInverse | Inverse Exponential Cumulative Distribution Function | |
| GeometricCDF | Geometric Cumulative Distribution Function | |
| GeometricInverse | Inverse Geometric Cumulative Distribution Function | |
| GeometricPDF | Geometric Probability Density Function | |
| GumbelPDF | Gumbel Probability Density Function | |
| GumbelCDF | Gumbel Cumulative Distribution Function | |
| GumbelInverse | Inverse Gumbel Cumulative Distribution Function | |
| HyperGeometricPDF | Hypergeometric Probability Density Function | |
| HyperGeometricInverse | Inverse Hypergeometric Cumulative Distribution Function | |
| LogNormalPDF | Lognormal Probability Density Function | |
| NegativeBinomialPDF | Negative Binomial Probability Density Function | |
| tPDF | t Probability Density Function | |
| coth | Hyperbolic cotangent | |
| sech | Hyperbolic secant | |
| csch | Hyperbolic cosecant |