User-authored calculations for .NET
Note: Numbers in scientific notation can be represented as, for example 6.67384E-11, meaning 6.67384 × 10-11
Arithmetic operators |
|
| + - * / | The standard arithmetic operators |
| % | modulus, or remainder |
| ^ ** | "to the power of". x^y can be written as x**y or pow(x,y) |
Logical boolean operators |
|
| && || | And, Or |
| ! | Not |
Bitwise operators |
|
| & | xor | And, Or, Xor |
Comparison operators |
|
| > < | Greater than, less than |
| >= <= | Greater than or equal to, less than or equal to |
| = == | Is equal to (either form is acceptable) |
| != | is not equal to |
Constants |
|
| e | Euler's constant, approximately 2.7182818 |
| pi | Pi, approximately 3.14159265 |
Aggregate functions |
Returns | Notes |
| avg(a, b, c, ...) | The mean of all arguments | Supports the ('children') keyword |
| max(a, b, c, ...) | The highest of all the arguments | Supports the ('children') keyword |
| min(a, b, c, ...) | The lowest of all the arguments | Supports the ('children') keyword |
| sum(a, b, c, ...) | The sum of the arguments | Supports the ('children') keyword |
Date functions |
Returns | Notes |
| date(yyyy,mm,dd[,UTC|LOCAL]) | a floating point number equal to the OLEAutomation date (an "Excel-style" date) | Provides the ability to enter a hard-coded date in a function |
| datetime(yyyy,mm,dd,hh,mi,ss[, UTC/LOCAL]) | a floating point number equal to the OLEAutomation date (an "Excel-style" date) | Provides the ability to enter a hard-coded datetime in a function |
| now() |
Generates OLEAutomation date which represents the current instant in time |
|
| year(x) | Returns an integer representing the year component of a date | Argument can be a date, date arithmetic or a floating point number that evaluates to a valid OLEAutomation date. |
| month(x) | Returns the month component of a date as an integer between 1 and 12 | |
| day(x) | returns the day-of-month component of a date as an integer between 1 and 31 | |
| hour(x) | returns the hour component of a date, as an integer between 0 and 23 | |
| minute(x) | returns the minute component of a date, as an integer between 0 and 59 | |
| second(x) | returns the second component of a date, as an integer between 0 and 59 | |
Trigonometry functions |
Returns | Notes |
| sin(x) | The sine of the argument. | The argument must evaluate to a number in radians |
| cos(x) | The cosine of the argument | The argument must evaluate to a number in radians |
| tan(x) | The tangent of the argument | The argument must evaluate to a number in radians |
| asin(x) | inverse sine (arcsine) in radians | The argument must be between -1 and 1, inclusive |
| acos(x) | inverse cosine in radians | The argument must be between -1 and 1, inclusive |
| atan(x) | inverse tangent (arctangent) in radians | |
Hyperbolic trigonometry functions |
Returns | Notes |
| sinh(x) | The hyperbolic sine of the argument. | |
| cosh(x) | The hyperbolic cosine of the argument. | |
| tanh(x) | The hyperbolic tangent of the argument. | |
Rounding functions |
Returns | Notes |
| ceiling(x) | smallest integer that is greater than or equal to the argument | |
| floor(x) | largest integer less than or equal to the argument | |
| round(x [,d]) | Rounds the argument, to the nearest 'd' decimal places | decimal places argument is optional |
| trunc(x) | the integer part of a number | rounds to the nearest integer towards zero |
Log functions |
Returns | Notes |
| ln(x) | the natural (base e) logarithm of x | |
| log(x,y) | the log of x in the base y | |
| log10(x) | the base 10 logarithm of x | |
| exp(x) | e raised to the specified power | |
| pow(x,y) | x to the power y | can also be written as x**y. |
| sqrt(x) | the square root of the argument | |
Business functions |
Returns | Notes |
| fv(rate, nper, pmt[, pv][, type]) | The future value of an investment based on periodic, constant payments and a constant interest rate |
rate: The interest rate per period nper: number of payment periods pmt: The payment made each period pv: optional; the present value of the investment. Defaults to 0 type: optional; payment timing indicator. Use 0 for "end of period", 1 for "beginning of period". Defaults to 0 |
| npv(rate, val1 [, ...valN]) | The net present value of a series of future payments (negative values) and income (positive values) assuming a given discount rate |
rate: The discount (reference) rate val1..valN: The net values for each period |
| rangepart(value, min[, max]) | Returns that part of a value that lies between min and max |
Returns 0 if value < min Returns (max-min) if value > max Returns (value-min) if min < value < max or if max is not present Handy for dealing with tax tables or commission structures |
Miscellaneous functions |
Returns | Notes |
| abs(x) | The absolute value of the argument | |
| if(test,truepart,falsepart) | If 'test' is true, returns 'truepart', else 'falsepart' | |
| rand() | A random number between 0 and 1 | |
| [...] | Treats the entire argument inside square brackets as a the name of a sub-expression, or else a tagname | Handy when the entity name contains characters that would otherwise prevent it from being recognized correctly, e.g. [total tonnes / day] |
| tag() | Treats the entire argument in the bracket as a tagname | Handy when the tagname contains characters that would otherwise prevent it from being recognized correctly, e.g. tag(BA:PHASE.1) |