User:Sqltool/navframetest
XLeratorDB
FUNCTION PACKAGES
FINANCIAL
XLeratorDB/Financial Documentation
| ACCRINT | Accrued Interest |
| ACCRINTM | Accrued Interest at Maturity |
| COUPDAYS | Coupon Days |
| COUPDAYSBS | Coupon Days - beginning to settlement |
| COUPDAYSNC | Coupon Days - settlement to next coupon |
| COUPNCD | Coupon Days - next coupon date |
| COUPNUM | Number of Coupon Days |
| COUPPCD | Previous Coupon Date |
| CUMIPMT | Cumulative Interest paid |
| CUMPRINC | Cumulative Principal paid |
| DB | Declining Balance |
| DDB | Double Declining Balance |
| DISC | Discount Rate |
| DOLLARDE | Dollar - fraction to Decimal |
| DOLLARFR | Dollar - decimal to Fraction |
| DURATION | Annual Duration of a Security |
| EDATE | Maturity |
| EFFECT | Effective Annual Interest Rate |
| ENPV | Enhanced Net Present Value |
| ENPV_q | Enhanced Net Present Value |
| EOMONTH | Last Day of Month |
| FV | Future Value |
| FVSCHEDULE | Future Value based on Compound Rates |
| INTRATE | Interest Rate of a Security |
| IPMT | Interest Payment based on Constant Rate |
| IRR | Internal Rate of Return |
| IRR_q | Internal Rate of Return |
| MDURATION | Duration |
| MIRR | Modified Internal Rate of Return |
| MIRR_q | Modified Internal Rate of Return |
| NOMINAL | Annual Nominal Interest Rate |
| NPER | Number of Periods |
| NPV | Net Present Value |
| NPV_q | Net Present Value |
| ODDFPRICE | Odd First Period Price |
| ODDFYIELD | Odd First Period Yield |
| ODDLPRICE | Odd Last Period Price |
| ODDLYIELD | Odd Last Period Yield |
| PMT | Payment of an Annuity |
| PPMT | Principal Payment |
| PRICE | Price of a Security |
| PRICEDISC | Price of a Discounted Security |
| PRICEMAT | Price at Maturity |
| PV | Present Value |
| RATE | Interest Rate for an Annuity |
| RECEIVED | Amount Received at Maturity |
| SLN | Straight Line Depreciation |
| SYD | Sum-of-Year's-Digits Depreciation |
| TBILLEQ | Bond Equivalent Yield for a Treasury Bill |
| TBILLPRICE | Price for a Treasury Bill |
| TBILLYIELD | Yield for a Treasury Bill |
| VDB | Depreciation using Declining Balance |
| XIRR | Internal Rate of Return with non-periodic cashflows |
| XIRR_q | Internal Rate of Return with non-periodic cashflows |
| XNPV | Net Present Value for non-periodic |
| XNPV_q | Net Present Value for non-periodic cashflows |
| YEARFRAC | Fraction of Year |
| YIELD | Yield of a Security |
| YIELDDISC | Yield of a Discounted Security |
| YIELDMAT | Yield with Interest at Maturity |
STATISTICS
XLeratorDB/Statistics Documentation
Statistical Functions
| AVEDEV | average of the absolute deviations |
| AVEDEV_q | average of the absolute deviations |
| AVERAGE | average (arithmetic mean) |
| AVERAGE_q | average (arithmetic mean) |
| CHISQ | Chi-square statistic |
| CHISQ_q | Chi-square statistic |
| CHISQN | Chi-square statistic on normalized tables |
| CHISQN_q | Chi-square statistic on normalized tables |
| CHITEST | Pearson chi-square test for independence |
| CHITEST_q | Pearson chi-square test for independence |
| CHITESTN | Pearson chi-square test for independence on normalized tables |
| CHITESTN_q | Pearson chi-square test for independence on normalized tables |
| CORREL | correlation coefficient |
| CORREL_q | correlation coefficient |
| COUNT | Number of rows in a table that contain numbers |
| COUNT_q | Number of rows in a table that contain numbers |
| COVAR | Covariance |
| COVAR_q | Covariance |
| DEVSQ | sum of the squares of deviations |
| DEVSQ_q | sum of the squares of deviations |
| FORECAST | calculate a future value |
| FORECAST_q | calculate a future value |
| FTEST | determine whether two samples have different variances |
| FTEST_q | determine whether two samples have different variances |
| GEOMEAN | geometric mean for a dataset containing positive numbers |
| GEOMEAN_q | geometric mean for a dataset containing positive numbers |
| GROWTH | predicted exponential growth |
| GROWTH_q | predicted exponential growth |
| HARMEAN | harmonic mean of a dataset containing positive numbers |
| HARMEAN_q | harmonic mean of a dataset containing positive numbers |
| INTERCEPT | calculate the point at which a line will intersect the y-axis |
| INTERCEPT_q | calculate the point at which a line will intersect the y-axis |
| KURT | calculate the kurtosis of a dataset |
| KURT_q | calculate the kurtosis of a dataset |
| LARGE | calculate the kth largest value in a dataset |
| LARGE_q | calculate the kth largest value in a dataset |
| MEDIAN | calculate the median value in a dataset |
| MEDIAN_q | calculate the median value in a dataset |
| MODE | calculate the most common value in a dataset |
| MODE_q | calculate the most common value in a dataset |
| PEARSON | Pearson correlation coefficient |
| PEARSON_q | Pearson correlation coefficient |
| PERCENTILE | calculate the kth percentile of value in a dataset |
| PERCENTILE_q | calculate the kth percentile of value in a dataset |
| PERCENTRANK | calculate rank of a value in a dataset as a percentage of the dataset |
| PERCENTRANK_q | calculate rank of a value in a dataset as a percentage of the dataset |
| PROB | calculate probability that values in a range are between two limits |
| PROB_q | calculate probability that values in a range are between two limits |
| QUARTILE | calculate the quartile of a dataset |
| QUARTILE_q | calculate the quartile of a dataset |
| RANK | calculate the rank of a number in a list of numbers |
| RANK_q | calculate the rank of a number in a list of numbers |
| RSQ | Pearson product moment correlation coefficient |
| RSQ_q | Pearson product moment correlation coefficient |
| SKEW | the degree of asymmetry of a distribution |
| SKEW_q | the degree of asymmetry of a distribution |
| SMALL | calculate the kth smallest value in a dataset |
| SMALL_q | calculate the kth smallest value in a dataset |
| STDEV | standard deviation |
| STDEV_q | standard deviation |
| STDEVP | standard deviation for an entire population |
| STDEVP_q | standard deviation for an entire population |
| STEYX | standard error |
| STEYX_q | standard error |
| TREND | calculate the values along a linear trend |
| TREND_q | calculate the values along a linear trend |
| TRIMMEAN | calculate the mean of the interior of a dataset |
| TRIMMEAN_q | calculate the mean of the interior of a dataset |
| TTEST | calculate the probability associated with Student’s t-test |
| TTEST_q | calculate the probability associated with Student’s t-test |
| VAR | variance |
| VAR_q | variance |
| VARP | variance for an entire population |
| VARP_q | variance for an entire population |
| ZTEST | calculate the one-tailed probability of a Z-test |
| ZTEST_q | calculate the one-tailed probability of a Z-test |
Probability Functions
| BETA | beta |
| BETADIST | beta cumulative probability density |
| BETAI | incomplete beta |
| BETAINV | inverse of the beta cumulative probability density |
| BICO | binomial coefficient |
| BINOMDIST | binomial distribution |
| CHIDIST | chi-squared distribution |
| CHIINV | inverse of the chi-squared distribution |
| CONFIDENCE | confidence interval |
| CRITBINOM | criterion value for the cumulative binomial distribution |
| EXPONDIST | exponential distribution |
| FACTLN | natural logarithm of a factorial |
| FDIST | F probability distribution |
| FINV | inverse of the F probability distribution |
| FISHER | Fisher transformation |
| FISHERINV | inverse of the Fisher transformation |
| GAMMA | complete gamma function |
| GAMMADIST | gamma distribution |
| GAMMAINC | incomplete gamma |
| GAMMAINV | inverse of the cumulative gamma distribution |
| GAMMALN | natural logarithm of the complete gamma |
| GAMMAP | regularized gamma function P(a, x) |
| GAMMAQ | regularized gamma function Q(a, x) |
| HYPGEOMDIST | hypergeometric distribution |
| INVGAMMAP | inverse of the incomplete gamma function P(a,x) |
| LOGINV | inverse of the lognormal cumulative distribution |
| LOGNORMDIST | lognormal cumulative distribution |
| NEGBINOMDIST | negative binomial distribution |
| NORMDIST | normal distribution |
| NORMINV | inverse of the normal cumulative distribution |
| NORMSDIST | standard normal cumulative distribution |
| NORMSINV | inverse of the standard normal cumulative distribution |
| PERMUT | permutations |
| POISSON | Poisson distribution |
| STANDARDIZE | normalized value from a distribution |
| TDIST | probability for the Student t-distribution |
| TINV | t-value of the Student's t-distribution |
| WEIBULL | Weibull distribution |
MATH
| ACOSH | Inverse Hyperbolic Cosine |
| ASINH | Inverse Hyperbolic Sine |
| ATANH | Inverse Hyperbolic Tangent |
| CEILING | Ceiling to a multiple |
| COMBIN | Combinatorial |
| COSH | Hyperbolic Cosine |
| CSC | Cosecant |
| EGCD | Euclidean GCD |
| ELCM | Euclidean LCM |
| EVEN | Nearest Even |
| FACT | Factorial |
| FACTDOUBLE | Double Factorial |
| FLOOR | Floor to a multiple |
| GCD | Greatest Common Divisor |
| INT | Integer |
| LCM | Least Common Multiple |
| MROUND | Round to a multiple |
| MULTINOMIAL | Sum of squares |
| MULTINOMIAL_q | Sum of squares |
| ODD | Nearest Odd |
| PRODUCT | Product of Values (similar to SUM) |
| QUOTIENT | Integer portion of a division |
| RANDBETWEEN | Random integer between two values |
| ROMAN | Convert to Roman Numerals |
| ROUNDDOWN | Round toward zero |
| ROUNDUP | Round away from zero |
| SEC | Secant |
| SERIESSUM | Sum of a Power Series |
| SERIESSUM_q | Sum of a Power Series |
| SINH | Hyberbolic Sine |
| SUMSQ | Sum of the Squares |
| TANH | Hyperbolic Tangent |
| TRUNC | Truncate to Integer |
ENGINEERING
XLeratorDB Engineering Documentation
Engineering
| BESSELJ | calculate the Bessel function of the first kind J±v(z) |
| BESSELK | calculate the Bessel function of the first kind Kv(z) |
| BESSELY | calculate the Bessel function of the first kind Yv(z) |
| ERF | calculate the error function |
| ERFC | calculate the complementary error function |
| Modulo | Modulus as calculated in Excel |
| DELTA | test whether two values are equal |
| GESTEP | test greater than or equal to a threshold |
Base Conversions
| EBIN2DEC | convert a positive binary number to decimal |
| BIN2DEC | convert a binary number to a decimal |
| BIN2HEX | convert a binary number to a hexadecimal |
| BIN2OCT | convert a binary number to an octal |
| DEC2BIN | convert a decimal number to binary |
| DEC2HEX | convert a decimal number to hexadecimal |
| DEC2OCT | convert a decimal number to octal |
| HEX2DEC | convert a hexadecimal number to decimal |
| HEX2OCT | convert a hexadecimal number to octal |
| HEX2BIN | convert a hexadecimal number to binary |
| OCT2DEC | convert an octal number to decimal |
| OCT2HEX | convert an octal number to hexadecimal |
| OCT2BIN | convert an octal number to binary |
Complex Numbers
| COMPLEX | convert to a complex number |
| IMREAL | return the real coefficient of a complex number |
| IMAGINARY | return the imaginary coefficient of a complex number |
| IMABS | calculate the absolute value of a complex number |
| IMARGUMENT | return theta as radians |
| IMADD | return the sum of 2 complex numbers |
| IMSUB | return the difference between 2 complex numbers |
| IMMULT | return the product of 2 complex numbers |
| IMDIV | return the quotient of 2 complex numbers |
| IMCOS | return the cosine of a complex number |
| IMSIN | return the sine of a complex number |
| IMPOWER | return a complex number raised to a power |
| IMSQRT | return the square root of a complex number |
| IMLN | return the natural logarithm of a complex number |
| IMEXP | return the exponential of a complex number |
| IMLOG10 | return the common (base 10) logarithm of a complex number |
| IMLOG2 | return the base-2 logarithm of a complex number |
| IMCONJUGATE | return the complex conjugate of a complex number |
| IMSUM | return the sum of a set of complex numbers |
| IMSUM_q | return the sum of a set of complex numbers |
| IMPRODUCT | return the product of a set of complex numbers |
| IMPRODUCT_q | return the product of a set of complex numbers |
UNIT CONVERSIONS
XLeratorDB Unit Converstion Documentation
Area
| CONVERTAREA | Area unit conversions |
| ACRES | Convert area to Acres |
| HECTARES | Convert area to Hectares |
| SQINCHES | Convert area to Square Inches |
| SQFEET | Convert area to Square Feet |
| SQYARDS | Convert area to Square Yards |
| SQMILES | Convert area to Square Miles |
| SQMETERS | Convert area to Square Meters |
Length
| CONVERTLENGTH | Length Unit Conversions |
| INCHES | Convert length to Inches |
| FEET | Convert length to Feet |
| YARDS | Convert length to Yards |
| METERS | Convert length to Meters |
| MILES | Convert length to Miles |
| NMILES | Convert length to Nautical Miles |
| FATHOMS | Convert length to Fathoms |
| FURLONGS | Convert length to Furlongs |
| PICA | Convert length to Picas |
| POINTS | Convert length to Points |
Speed
| CONVERTSPEED | Speed Unit Conversions |
| INSEC | Convert speed to Inches per Second |
| FTSEC | Convert speed to Feet per Second |
| FTMIN | Convert speed to Feet per Minute |
| FTHR | Convert speed to Feet per Hour |
| MSEC | Convert speed to Meters per Second |
| MMIN | Convert speed to Meters per Minute |
| MHR | Convert speed to Meters per Hour |
| KMHR | Convert speed to Kilometers per Hour |
| MISEC | Convert speed to Miles per Second |
| MIMIN | Convert speed to Miles per Minute |
| MIHR | Convert speed to Miles per Hour |
| KNOTS | Convert speed to Knots |
Temperature
| CONVERTTEMP | Temperature Unit Conversions |
| CELSIUS | Convert temperature to Celsius |
| FAHRENHEIT | Convert temperature to Fahrenheit |
| KELVIN | Convert temperature to Kelvin |
| RANKINE | Convert temperature to Rankine |
Volume
| CONVERT VOLUME | Volume Unit Conversions |
| CUINCHES | Convert volume to Cubic Inches |
| CUFEET | Convert volume to Cubic Feet |
| CUYARDS | Convert volume to Cubic Yards |
| CUMETERS | Convert volume to Cubic Meters |
| CUMILES | Convert volume to Cubic Miles |
| FLDRAMS | Convert volume to Fluid Drams |
| FLOUNCES | Convert volume to Fluid Ounces |
| TEASPOONS | Convert volume to Teaspoons |
| TABLESPOONS | Convert volume to Tablespoons |
| CUPS | Convert volume to Cups |
| PINTS | Convert volume to Pints |
| QUARTS | Convert volume to Quarts |
| GALLONS | Convert volume to Gallons |
| LITERS | Convert volume to Liters |
| IMPOUNCES | Convert volume to Imperial Ounces |
| IMPPINTS | Convert volume to Imperial Pints |
| IMPQUARTS | Convert volume to Imperial Quarts |
| IMPGALLONS | Convert volume to Imperial Gallons |
| IMPGILLS | Convert volume to Imperial Gills |
| BARRELS | Convert volume to Barrels |
| BBLS | Convert volume to US Beer Barrels |
| BUSHELS | Convert volume to Bushels |
| GILLS | Convert volume to Gills |
| PECKS | Convert volume to Pecks |
| DRYPINTS | Convert volume to Dry Pints |
| DRYQUARTS | Convert volume to Dry Quarts |
| STERES | Convert volume to Steres |
Weight
| CONVERTWEIGHT | Weight Unit Conversions |
| OUNCES | Convert weight to Ounces |
| GRAINS | Convert weight to Grains |
| DRAMS | Convert weight to Drams |
| POUNDS | Convert weight to Pounds |
| STONES | Convert weight to Stones |
| CWT | Convert weight to Hundred Weight |
| TON | Convert weight to Tons |
| DWT | Convert weight to Pennyweight |
| TROY | Convert weight to Troy Ounces |
| TPOUNDS | Convert weight to Troy Pounds |
| GRAMS | Convert weight to Grams |
| TONNES | Convert weight to Tonnes |
STRINGS
XLeratorDB Strings Documentation
Excel String Functions
| CLEAN | clean non-printable characters text |
| CONCAT | concatenate two text strings with an optional joiner |
| DOLLAR | converts a number to text with a currency format |
| EXACT | returns whether two strings are equal |
| FIND | finds one text value within another |
| FIXED | formats a number as text with a fixed number of decimals |
| PROPER | capitalizes the first letter of each word in a text string |
| SUBSTITUTE | replaces specified instances of text in a text string |
| TEXT | formats a number according to excel format styles |
| TRIM | trims leading and trailing spaces from a text string |
| VALUE | converts text to a number (float) |
Additional String Functions
| CONTAINSSTR | returns whether a text string contains another text string |
| COUNTSTRING | returns the number of occurences of one string within another |
| CRLF | returns the ascii carriage return-line feed |
| DEC2FRAC | returns a text representation of a decimal converted to a fraction |
| ENDSWITH | returns whether a text string ends with another text string |
| FORMATDATE | formats a date according to Microsoft DOTNET formatting standards |
| FORMATNUM | formats a number according to Microsoft DOTNET formatting standards |
| FRAC2DEC | inverse of DEC2FRAC, converts a text representation of a fraction to a decimal number |
| IIFSTR | returns one of two strings depending on a boolean condition |
| INDENT | indents a text string to a specified number of levels with a specified indentation character. works with multi-line text. |
| INSTR | returns the first character position of one text string within another |
| INSTRREV | returns the character position of the last instance of one text string within another. |
| ISALPHA | returns whether a text string contains only alphabetic characters |
| ISNUMERIC | returns whether a text string represents a number |
| JOINSTR | returns the concatenation of columnar data from a table or view |
| JOINSTR_q | returns the concatenation of columnar data from a query |
| NUMWORDS | returns the number of items delimeted by one or more separators |
| PADLEFT | pads text to a specified width using a padding cahracter to the left of the original text |
| PADRIGHT | pads text to a specified width using a padding cahracter to the right of the original text |
| PARSE | returns the requested instance of an item delimited by one or more separators |
| PCT2FRAC | converts a given percentage value to a text representation of a fraction. |
| QUOTED | returns text surrounded with a double quote character |
| QUOTES | returns text surrounded with a single quote character |
| STARTSWITH | returns whether a text string starts with another text string |
| STRIP | strips all punctuation from the beginning and end of a text string |