001/* 002// This software is subject to the terms of the Eclipse Public License v1.0 003// Agreement, available at the following URL: 004// http://www.eclipse.org/legal/epl-v10.html. 005// You must accept the terms of that agreement to use this software. 006// 007// Copyright (C) 2008-2009 Pentaho 008// All Rights Reserved. 009*/ 010package mondrian.olap.fun.vba; 011 012import mondrian.olap.InvalidArgumentException; 013import mondrian.olap.fun.JavaFunDef; 014 015import static mondrian.olap.fun.JavaFunDef.Description; 016import static mondrian.olap.fun.JavaFunDef.FunctionName; 017 018/** 019 * Implementations of functions in the Excel worksheet library. 020 * 021 * <p>Functions are loaded into the function table by reflection. 022 * 023 * @author jhyde 024 * @since Dec 31, 2007 025 */ 026public abstract class Excel { 027 // There follows a list of all functions defined in Excel. Functions are 028 // marked 'todo:' if they still need to be implemented; 'skip:' if they 029 // are implemented elsewhere, such as in Vba or there there is an explicit 030 // implementation of FunDef for them. A FunDef typically allows a more 031 // efficient implementation. 032 033 // AccrInt Returns the accrued interest for a security that pays periodic 034 // interest. 035 036 // AccrIntM Returns the accrued interest for a security that pays interest 037 // at maturity. 038 039 @FunctionName("Acos") 040 @JavaFunDef.Signature("Acos(number)") 041 @Description( 042 "Returns the arccosine, or inverse cosine, of a number. The arccosine " 043 + "is the angle whose cosine is Arg1. The returned angle is given in " 044 + "radians in the range 0 (zero) to pi.") 045 public static double acos(double number) { 046 return Math.acos(number); 047 } 048 049 @FunctionName("Acosh") 050 @JavaFunDef.Signature("Acosh(number)") 051 @Description( 052 "Returns the inverse hyperbolic cosine of a number. Number must be " 053 + "greater than or equal to 1. The inverse hyperbolic cosine is the " 054 + "value whose hyperbolic cosine is Arg1, so Acosh(Cosh(number)) " 055 + "equals Arg1.") 056 public static double acosh(double x) { 057 return Math.log(x + Math.sqrt((x * x) - 1.0)); 058 } 059 060 // Todo: AmorDegrc Returns the depreciation for each accounting 061 // period. This function is provided for the French accounting 062 // system. 063 064 // Todo: AmorLinc Returns the depreciation for each accounting 065 // period. This function is provided for the French accounting 066 // system. 067 068 // Skip: And Returns TRUE if all its arguments are TRUE; returns 069 // FALSE if one or more argument is FALSE. 070 071 // Todo: Asc For Double-byte character set (DBCS) languages, 072 // changes full-width (double-byte) characters to half-width 073 // (single-byte) characters. 074 075 @FunctionName("Asin") 076 @JavaFunDef.Signature("Asin(number)") 077 @Description( 078 "Returns the arcsine, or inverse sine, of a number. The arcsine is the " 079 + "angle whose sine is Arg1. The returned angle is given in radians in " 080 + "the range -pi/2 to pi/2.") 081 public static double asin(double number) { 082 return Math.asin(number); 083 } 084 085 @FunctionName("Asinh") 086 @JavaFunDef.Signature("Asinh(number)") 087 @Description( 088 "Returns the inverse hyperbolic sine of a number. The inverse " 089 + "hyperbolic sine is the value whose hyperbolic sine is Arg1, " 090 + "so Asinh(Sinh(number)) equals Arg1.") 091 public static double asinh(double x) { 092 return Math.log(x + Math.sqrt(1.0 + (x * x))); 093 } 094 095 @FunctionName("Atan2") 096 @JavaFunDef.Signature("Atan2(x, y)") 097 @Description( 098 "Returns the arctangent, or inverse tangent, of the specified x- and " 099 + "y-coordinates. The arctangent is the angle from the x-axis to a " 100 + "line containing the origin (0, 0) and a point with coordinates " 101 + "(x_num, y_num). The angle is given in radians between -pi and pi, " 102 + "excluding -pi.") 103 public static double atan2(double y, double x) { 104 return Math.atan2(y, x); 105 } 106 107 @FunctionName("Atanh") 108 @JavaFunDef.Signature("Atanh(number)") 109 @Description( 110 "Returns the inverse hyperbolic tangent of a number. Number " 111 + "must be between -1 and 1 (excluding -1 and 1).") 112 public static double atanh(double x) { 113 return .5 * Math.log((1.0 + x) / (1.0 - x)); 114 } 115 116 // Todo: AveDev Returns the average of the absolute deviations of data 117 // points from their mean. AveDev is a measure of the variability in a data 118 // set. 119 120 // Todo: Average Returns the average (arithmetic mean) of the arguments. 121 122 // Todo: AverageIf Returns the average (arithmetic mean) of all the cells in 123 // a range that meet a given criteria. 124 125 // Todo: AverageIfs Returns the average (arithmetic mean) of all cells that 126 // meet multiple criteria. 127 128 // Todo: BahtText Converts a number to Thai text and adds a suffix of 129 // "Baht." 130 131 // Todo: BesselI Returns the modified Bessel function, which is equivalent 132 // to the Bessel function evaluated for purely imaginary arguments. 133 134 // Todo: BesselJ Returns the Bessel function. 135 136 // Todo: BesselK Returns the modified Bessel function, which is equivalent 137 // to the Bessel functions evaluated for purely imaginary arguments. 138 139 // Todo: BesselY Returns the Bessel function, which is also called the Weber 140 // function or the Neumann function. 141 142 // Todo: BetaDist Returns the beta cumulative distribution function. 143 144 // Todo: BetaInv Returns the inverse of the cumulative distribution function 145 // for a specified beta distribution. That is, if probability = 146 // BetaDist(x,...), then BetaInv(probability,...) = x. 147 148 // Todo: Bin2Dec Converts a binary number to decimal. 149 150 // Todo: Bin2Hex Converts a binary number to hexadecimal. 151 152 // Todo: Bin2Oct Converts a binary number to octal. 153 154 // Todo: BinomDist Returns the individual term binomial distribution 155 // probability. 156 157 // Todo: Ceiling Returns number rounded up, away from zero, to the nearest 158 // multiple of significance. 159 160 // Todo: ChiDist Returns the one-tailed probability of the chi-squared 161 // distribution. 162 163 // Todo: ChiInv Returns the inverse of the one-tailed probability of the 164 // chi-squared distribution. 165 166 // Todo: ChiTest Returns the test for independence. 167 168 // Todo: Choose Uses Arg1 as the index to return a value from the list of 169 // value arguments. 170 171 // Todo: Clean Removes all nonprintable characters from text. 172 173 // Todo: Combin Returns the number of combinations for a given number of 174 // items. Use Combin to determine the total possible number of groups for a 175 // given number of items. 176 177 // Todo: Complex Converts real and imaginary coefficients into a complex 178 // number of the form x + yi or x + yj. 179 180 // Todo: Confidence Returns a value that you can use to construct a 181 // confidence interval for a population mean. 182 183 // Todo: Convert Converts a number from one measurement system to 184 // another. For example, Convert can translate a table of distances in miles 185 // to a table of distances in kilometers. 186 187 // Todo: Correl Returns the correlation coefficient of the Arg1 and Arg2 188 // cell ranges. 189 190 @FunctionName("Cosh") 191 @Description("Returns the hyperbolic cosine of a number.") 192 public static double cosh(double number) { 193 return Math.cosh(number); 194 } 195 196 // Todo: Count Counts the number of cells that contain numbers and counts 197 // numbers within the list of arguments. 198 199 // Todo: CountA Counts the number of cells that are not empty and the values 200 // within the list of arguments. 201 202 // Todo: CountBlank Counts empty cells in a specified range of cells. 203 204 // Todo: CountIf Counts the number of cells within a range that meet the 205 // given criteria. 206 207 // Todo: CountIfs Counts the number of cells within a range that meet 208 // multiple criteria. 209 210 // Todo: CoupDayBs Returns the number of days from the beginning of the 211 // coupon period to the settlement date. 212 213 // Todo: CoupDays Returns the number of days in the coupon period that 214 // contains the settlement date. 215 216 // Todo: CoupDaysNc Returns the number of days from the settlement date to 217 // the next coupon date. 218 219 // Todo: CoupNcd Returns a number that represents the next coupon date after 220 // the settlement date. 221 222 // Todo: CoupNum Returns the number of coupons payable between the 223 // settlement date and maturity date, rounded up to the nearest whole 224 // coupon. 225 226 // Todo: CoupPcd The description for this item will appear in the final 227 // release of Office 2007. 228 229 // Todo: Covar Returns covariance, the average of the products of deviations 230 // for each data point pair. 231 232 // Todo: CritBinom Returns the smallest value for which the cumulative 233 // binomial distribution is greater than or equal to a criterion value. 234 235 // Todo: CumIPmt Returns the cumulative interest paid on a loan between 236 // start_period and end_period. 237 238 // Todo: CumPrinc Returns the cumulative principal paid on a loan between 239 // start_period and end_period. 240 241 // Todo: DAverage Averages the values in a column of a list or database that 242 // match conditions you specify. 243 244 // Todo: Days360 Returns the number of days between two dates based on a 245 // 360-day year (twelve 30-day months), which is used in some accounting 246 // calculations. 247 248 // Todo: Db Returns the depreciation of an asset for a specified period 249 // using the fixed-declining balance method. 250 251 // Todo: Dbcs The description for this item will appear in the final release 252 // of Office 2007. 253 254 // Todo: DCount Counts the cells that contain numbers in a column of a list 255 // or database that match conditions that you specify. 256 257 // Todo: DCountA Counts the nonblank cells in a column of a list or database 258 // that match conditions that you specify. 259 260 // Todo: Ddb Returns the depreciation of an asset for a specified period 261 // using the double-declining balance method or some other method you 262 // specify. 263 264 // Todo: Dec2Bin Converts a decimal number to binary. 265 266 // Todo: Dec2Hex Converts a decimal number to hexadecimal. 267 268 // Todo: Dec2Oct Converts a decimal number to octal. 269 270 // Todo: Degrees Converts radians into degrees. 271 272 273 @FunctionName("Degrees") 274 @Description("Converts radians to degrees.") 275 public static double degrees(double number) { 276 // 180 degrees = Pi radians 277 return number * 180.0 / Math.PI; 278 } 279 280 // Todo: Delta Tests whether two values are equal. Returns 1 if number1 = 281 // number2; returns 0 otherwise. 282 283 // Todo: DevSq Returns the sum of squares of deviations of data points from 284 // their sample mean. 285 286 // Todo: DGet Extracts a single value from a column of a list or database 287 // that matches conditions that you specify. 288 289 // Todo: Disc Returns the discount rate for a security. 290 291 // Todo: DMax Returns the largest number in a column of a list or database 292 // that matches conditions you that specify. 293 294 // Todo: DMin Returns the smallest number in a column of a list or database 295 // that matches conditions that you specify. 296 297 // Todo: Dollar The function described in this Help topic converts a number 298 // to text format and applies a currency symbol. The name of the function 299 // (and the symbol that it applies) depends upon your language settings. 300 301 // Todo: DollarDe Converts a dollar price expressed as a fraction into a 302 // dollar price expressed as a decimal number. Use DOLLARDE to convert 303 // fractional dollar numbers, such as securities prices, to decimal numbers. 304 305 // Todo: DollarFr Converts a dollar price expressed as a decimal number into 306 // a dollar price expressed as a fraction. Use DOLLARFR to convert decimal 307 // numbers to fractional dollar numbers, such as securities prices. 308 309 // Todo: DProduct Multiplies the values in a column of a list or database 310 // that match conditions that you specify. 311 312 // Todo: DStDev Estimates the standard deviation of a population based on a 313 // sample by using the numbers in a column of a list or database that match 314 // conditions that you specify. 315 316 // Todo: DStDevP Calculates the standard deviation of a population based on 317 // the entire population by using the numbers in a column of a list or 318 // database that match conditions that you specify. 319 320 // Todo: DSum Adds the numbers in a column of a list or database that match 321 // conditions that you specify. 322 323 // Todo: Duration Returns the Macauley duration for an assumed par value of 324 // $100. Duration is defined as the weighted average of the present value of 325 // the cash flows and is used as a measure of a bond price's response to 326 // changes in yield. 327 328 // Todo: DVar Estimates the variance of a population based on a sample by 329 // using the numbers in a column of a list or database that match conditions 330 // that you specify. 331 332 // Todo: DVarP Calculates the variance of a population based on the entire 333 // population by using the numbers in a column of a list or database that 334 // match conditions that you specify. 335 336 // Todo: EDate Returns the serial number that represents the date that is 337 // the indicated number of months before or after a specified date (the 338 // start_date). Use EDATE to calculate maturity dates or due dates that fall 339 // on the same day of the month as the date of issue. 340 341 // Todo: Effect Returns the effective annual interest rate, given the 342 // nominal annual interest rate and the number of compounding periods per 343 // year. 344 345 // Todo: EoMonth Returns the serial number for the last day of the month 346 // that is the indicated number of months before or after start_date. Use 347 // EOMONTH to calculate maturity dates or due dates that fall on the last 348 // day of the month. 349 350 // Todo: Erf Returns the error function integrated between lower_limit and 351 // upper_limit. 352 353 // Todo: ErfC The description for this item will appear in the final release 354 // of Office 2007. 355 356 // Todo: Even Returns number rounded up to the nearest even integer. You can 357 // use this function for processing items that come in twos. For example, a 358 // packing crate accepts rows of one or two items. The crate is full when 359 // the number of items, rounded up to the nearest two, matches the crate's 360 // capacity. 361 362 // Todo: ExponDist Returns the exponential distribution. Use EXPONDIST to 363 // model the time between events, such as how long an automated bank teller 364 // takes to deliver cash. For example, you can use EXPONDIST to determine 365 // the probability that the process takes at most 1 minute. 366 367 // Todo: Fact Returns the factorial of a number. The factorial of a number 368 // is equal to 1*2*3*...* number. 369 370 // Todo: FactDouble Returns the double factorial of a number. 371 372 // Todo: FDist Returns the F probability distribution. You can use this 373 // function to determine whether two data sets have different degrees of 374 // diversity. For example, you can examine the test scores of men and women 375 // entering high school and determine if the variability in the females is 376 // different from that found in the males. 377 378 // Todo: Find Finds specific information in a worksheet. 379 380 // Todo: FindB FIND and FINDB locate one text string within a second text 381 // string, and return the number of the starting position of the first text 382 // string from the first character of the second text string. 383 384 // Todo: FInv Returns the inverse of the F probability distribution. If p = 385 // FDIST(x,...), then FINV(p,...) = x. 386 387 // Todo: Fisher Returns the Fisher transformation at x. This transformation 388 // produces a function that is normally distributed rather than skewed. Use 389 // this function to perform hypothesis testing on the correlation 390 // coefficient. 391 392 // Todo: FisherInv Returns the inverse of the Fisher transformation. Use 393 // this transformation when analyzing correlations between ranges or arrays 394 // of data. If y = FISHER(x), then FISHERINV(y) = x. 395 396 // Todo: Fixed Rounds a number to the specified number of decimals, formats 397 // the number in decimal format using a period and commas, and returns the 398 // result as text. 399 400 // Todo: Floor Rounds number down, toward zero, to the nearest multiple of 401 // significance. 402 403 // Todo: Forecast Calculates, or predicts, a future value by using existing 404 // values. The predicted value is a y-value for a given x-value. The known 405 // values are existing x-values and y-values, and the new value is predicted 406 // by using linear regression. You can use this function to predict future 407 // sales, inventory requirements, or consumer trends. 408 409 // Todo: Frequency Calculates how often values occur within a range of 410 // values, and then returns a vertical array of numbers. For example, use 411 // FREQUENCY to count the number of test scores that fall within ranges of 412 // scores. Because FREQUENCY returns an array, it must be entered as an 413 // array formula. 414 415 // Todo: FTest Returns the result of an F-test. An F-test returns the 416 // two-tailed probability that the variances in array1 and array2 are not 417 // significantly different. Use this function to determine whether two 418 // samples have different variances. For example, given test scores from 419 // public and private schools, you can test whether these schools have 420 // different levels of test score diversity. 421 422 // Todo: Fv Returns the future value of an investment based on periodic, 423 // constant payments and a constant interest rate. 424 425 // Todo: FVSchedule Returns the future value of an initial principal after 426 // applying a series of compound interest rates. Use FVSCHEDULE to calculate 427 // the future value of an investment with a variable or adjustable rate. 428 429 // Todo: GammaDist Returns the gamma distribution. You can use this function 430 // to study variables that may have a skewed distribution. The gamma 431 // distribution is commonly used in queuing analysis. 432 433 // Todo: GammaInv Returns the inverse of the gamma cumulative 434 // distribution. If p = GAMMADIST(x,...), then GAMMAINV(p,...) = x. 435 436 // Todo: GammaLn Returns the natural logarithm of the gamma function, ?(x). 437 438 // Todo: Gcd Returns the greatest common divisor of two or more 439 // integers. The greatest common divisor is the largest integer that divides 440 // both number1 and number2 without a remainder. 441 442 // Todo: GeoMean Returns the geometric mean of an array or range of positive 443 // data. For example, you can use GEOMEAN to calculate average growth rate 444 // given compound interest with variable rates. 445 446 // Todo: GeStep Returns 1 if number ? step; returns 0 (zero) otherwise. Use 447 // this function to filter a set of values. For example, by summing several 448 // GESTEP functions you calculate the count of values that exceed a 449 // threshold. 450 451 // Todo: Growth Calculates predicted exponential growth by using existing 452 // data. GROWTH returns the y-values for a series of new x-values that you 453 // specify by using existing x-values and y-values. You can also use the 454 // GROWTH worksheet function to fit an exponential curve to existing 455 // x-values and y-values. 456 457 // Todo: HarMean Returns the harmonic mean of a data set. The harmonic mean 458 // is the reciprocal of the arithmetic mean of reciprocals. 459 460 // Todo: Hex2Bin Converts a hexadecimal number to binary. 461 462 // Todo: Hex2Dec Converts a hexadecimal number to decimal. 463 464 // Todo: Hex2Oct Converts a hexadecimal number to octal. 465 466 // Todo: HLookup Searches for a value in the top row of a table or an array 467 // of values, and then returns a value in the same column from a row you 468 // specify in the table or array. Use HLOOKUP when your comparison values 469 // are located in a row across the top of a table of data, and you want to 470 // look down a specified number of rows. Use VLOOKUP when your comparison 471 // values are located in a column to the left of the data you want to find. 472 473 // Todo: HypGeomDist Returns the hypergeometric distribution. HYPGEOMDIST 474 // returns the probability of a given number of sample successes, given the 475 // sample size, population successes, and population size. Use HYPGEOMDIST 476 // for problems with a finite population, where each observation is either a 477 // success or a failure, and where each subset of a given size is chosen 478 // with equal likelihood. 479 480 // Todo: IfError Returns a value you specify if a formula evaluates to an 481 // error; otherwise, returns the result of the formula. Use the IFERROR 482 // function to trap and handle errors in a formula. 483 484 // Todo: ImAbs Returns the absolute value (modulus) of a complex number in x 485 // + yi or x + yj text format. 486 487 // Todo: Imaginary Returns the imaginary coefficient of a complex number in 488 // x + yi or x + yj text format. 489 490 // Todo: ImArgument Returns the argument (theta), an angle expressed in 491 // radians, such that: 492 493 // Todo: ImConjugate Returns the complex conjugate of a complex number in x 494 // + yi or x + yj text format. 495 496 // Todo: ImCos Returns the cosine of a complex number in x + yi or x + yj 497 // text format. 498 499 // Todo: ImDiv Returns the quotient of two complex numbers in x + yi or x + 500 // yj text format. 501 502 // Todo: ImExp Returns the exponential of a complex number in x + yi or x + 503 // yj text format. 504 505 // Todo: ImLn Returns the natural logarithm of a complex number in x + yi or 506 // x + yj text format. 507 508 // Todo: ImLog10 Returns the common logarithm (base 10) of a complex number 509 // in x + yi or x + yj text format. 510 511 // Todo: ImLog2 Returns the base-2 logarithm of a complex number in x + yi 512 // or x + yj text format. 513 514 // Todo: ImPower Returns a complex number in x + yi or x + yj text format 515 // raised to a power. 516 517 // Todo: ImProduct Returns the product of 2 to 29 complex numbers in x + yi 518 // or x + yj text format. 519 520 // Todo: ImReal Returns the real coefficient of a complex number in x + yi 521 // or x + yj text format. 522 523 // Todo: ImSin Returns the sine of a complex number in x + yi or x + yj text 524 // format. 525 526 // Todo: ImSqrt Returns the square root of a complex number in x + yi or x + 527 // yj text format. 528 529 // Todo: ImSub Returns the difference of two complex numbers in x + yi or x 530 // + yj text format. 531 532 // Todo: ImSum Returns the sum of two or more complex numbers in x + yi or x 533 // + yj text format. 534 535 // Todo: Index Returns a value or the reference to a value from within a 536 // table or range. There are two forms of the INDEX function: the array form 537 // and the reference form. 538 539 // Todo: Intercept Calculates the point at which a line will intersect the 540 // y-axis by using existing x-values and y-values. The intercept point is 541 // based on a best-fit regression line plotted through the known x-values 542 // and known y-values. Use the INTERCEPT function when you want to determine 543 // the value of the dependent variable when the independent variable is 0 544 // (zero). For example, you can use the INTERCEPT function to predict a 545 // metal's electrical resistance at 0C when your data points were taken at 546 // room temperature and higher. 547 548 // Todo: IntRate Returns the interest rate for a fully invested security. 549 550 // Todo: Ipmt Returns the interest payment for a given period for an 551 // investment based on periodic, constant payments and a constant interest 552 // rate. 553 554 // Todo: Irr Returns the internal rate of return for a series of cash flows 555 // represented by the numbers in values. These cash flows do not have to be 556 // even, as they would be for an annuity. However, the cash flows must occur 557 // at regular intervals, such as monthly or annually. The internal rate of 558 // return is the interest rate received for an investment consisting of 559 // payments (negative values) and income (positive values) that occur at 560 // regular periods. 561 562 // Todo: IsErr Checks the type of value and returns TRUE or FALSE depending 563 // if the value refers to any error value except #N/A. 564 565 // Todo: IsError Checks the type of value and returns TRUE or FALSE 566 // depending if the value refers to any error value (#N/A, #VALUE!, #REF!, 567 // #DIV/0!, #NUM!, #NAME?, or #NULL!). 568 569 // Todo: IsEven Checks the type of value and returns TRUE or FALSE depending 570 // if the value is even. 571 572 // Todo: IsLogical Checks the type of value and returns TRUE or FALSE 573 // depending if the value refers to a logical value. 574 575 // Todo: IsNA Checks the type of value and returns TRUE or FALSE depending 576 // if the value refers to the #N/A (value not available) error value. 577 578 // Todo: IsNonText Checks the type of value and returns TRUE or FALSE 579 // depending if the value refers to any item that is not text. (Note that 580 // this function returns TRUE if value refers to a blank cell.) 581 582 // Todo: IsNumber Checks the type of value and returns TRUE or FALSE 583 // depending if the value refers to a number. 584 585 // Todo: IsOdd Checks the type of value and returns TRUE or FALSE depending 586 // if the value is odd. 587 588 // Todo: Ispmt Calculates the interest paid during a specific period of an 589 // investment. This function is provided for compatibility with Lotus 1-2-3. 590 591 // Todo: IsText Checks the type of value and returns TRUE or FALSE depending 592 // if the value refers to text. 593 594 // Todo: Kurt Returns the kurtosis of a data set. Kurtosis characterizes the 595 // relative peakedness or flatness of a distribution compared with the 596 // normal distribution. Positive kurtosis indicates a relatively peaked 597 // distribution. Negative kurtosis indicates a relatively flat distribution. 598 599 // Todo: Large Returns the k-th largest value in a data set. You can use 600 // this function to select a value based on its relative standing. For 601 // example, you can use LARGE to return the highest, runner-up, or 602 // third-place score. 603 604 // Todo: Lcm Returns the least common multiple of integers. The least common 605 // multiple is the smallest positive integer that is a multiple of all 606 // integer arguments number1, number2, and so on. Use LCM to add fractions 607 // with different denominators. 608 609 // Todo: LinEst Calculates the statistics for a line by using the "least 610 // squares" method to calculate a straight line that best fits your data, 611 // and returns an array that describes the line. Because this function 612 // returns an array of values, it must be entered as an array formula. 613 614 // Todo: Ln Returns the natural logarithm of a number. Natural logarithms 615 // are based on the constant e (2.71828182845904). 616 617 618 // See Vba 619 // Skip: Log Returns the logarithm of a number to the base you specify. 620 621 @FunctionName("Log10") 622 @Description("Returns the base-10 logarithm of a number.") 623 public static double log10(double number) { 624 return Math.log10(number); 625 } 626 627 // Todo: LogEst In regression analysis, calculates an exponential curve that 628 // fits your data and returns an array of values that describes the 629 // curve. Because this function returns an array of values, it must be 630 // entered as an array formula. 631 632 // Todo: LogInv Use the lognormal distribution to analyze logarithmically 633 // transformed data. 634 635 // Todo: LogNormDist Returns the cumulative lognormal distribution of x, 636 // where ln(x) is normally distributed with parameters mean and 637 // standard_dev. Use this function to analyze data that has been 638 // logarithmically transformed. 639 640 // Todo: Lookup Returns a value either from a one-row or one-column range or 641 // from an array. The LOOKUP function has two syntax forms: the vector form 642 // and the array form. 643 644 // Todo: Match Returns the relative position of an item in an array that 645 // matches a specified value in a specified order. Use MATCH instead of one 646 // of the LOOKUP functions when you need the position of an item in a range 647 // instead of the item itself. 648 649 // Skip: Max Returns the largest value in a set of values. Todo: MDeterm 650 // Returns the matrix determinant of an array. 651 652 /** 653 * The MOD function. Not technically in the Excel package, but this seemed 654 * like a good place to put it, since Excel has a MOD function. 655 * 656 * @param first First 657 * @param second Second 658 * @return First modulo second 659 */ 660 @FunctionName("Mod") 661 @JavaFunDef.Signature("Mod(n, d)") 662 @Description("Returns the remainder of dividing n by d.") 663 public static double mod( 664 Object first, 665 Object second) 666 { 667 double iFirst; 668 if (!(first instanceof Number)) { 669 throw new InvalidArgumentException( 670 "Invalid parameter. " 671 + "first parameter " + first 672 + " of Mod function must be of type number"); 673 } else { 674 iFirst = ((Number) first).doubleValue(); 675 } 676 double iSecond; 677 if (!(second instanceof Number)) { 678 throw new InvalidArgumentException( 679 "Invalid parameter. " 680 + "second parameter " + second 681 + " of Mod function must be of type number"); 682 } else { 683 iSecond = ((Number) second).doubleValue(); 684 } 685 // Use formula "mod(n, d) = n - d * int(n / d)". 686 if (iSecond == 0) { 687 throw new ArithmeticException("/ by zero"); 688 } 689 return iFirst - iSecond * Vba.intNative(iFirst / iSecond); 690 } 691 692 // Todo: MDuration Returns the modified Macauley duration for a security 693 // with an assumed par value of $100. 694 695 // Skip: Median Returns the median of the given numbers. The median is the 696 // number in the middle of a set of numbers. Skip: Min Returns the smallest 697 // number in a set of values. Todo: MInverse Returns the inverse matrix for 698 // the matrix stored in an array. 699 700 // Todo: MIrr Returns the modified internal rate of return for a series of 701 // periodic cash flows. MIRR considers both the cost of the investment and 702 // the interest received on reinvestment of cash. 703 704 // Todo: MMult Returns the matrix product of two arrays. The result is an 705 // array with the same number of rows as array1 and the same number of 706 // columns as array2. 707 708 // Todo: Mode Returns the most frequently occurring, or repetitive, value in 709 // an array or range of data. 710 711 // Todo: MRound Returns a number rounded to the desired multiple. 712 713 // Todo: MultiNomial Returns the ratio of the factorial of a sum of values 714 // to the product of factorials. 715 716 // Todo: NegBinomDist Returns the negative binomial 717 // distribution. NEGBINOMDIST returns the probability that there will be 718 // number_f failures before the number_s-th success, when the constant 719 // probability of a success is probability_s. This function is similar to 720 // the binomial distribution, except that the number of successes is fixed, 721 // and the number of trials is variable. Like the binomial, trials are 722 // assumed to be independent. 723 724 // Todo: NetworkDays Returns the number of whole working days between 725 // start_date and end_date. Working days exclude weekends and any dates 726 // identified in holidays. Use NETWORKDAYS to calculate employee benefits 727 // that accrue based on the number of days worked during a specific term. 728 729 // Todo: Nominal Returns the nominal annual interest rate, given the 730 // effective rate and the number of compounding periods per year. 731 732 // Todo: NormDist Returns the normal distribution for the specified mean and 733 // standard deviation. This function has a very wide range of applications 734 // in statistics, including hypothesis testing. 735 736 // Todo: NormInv Returns the inverse of the normal cumulative distribution 737 // for the specified mean and standard deviation. 738 739 // Todo: NormSDist Returns the standard normal cumulative distribution 740 // function. The distribution has a mean of 0 (zero) and a standard 741 // deviation of one. Use this function in place of a table of standard 742 // normal curve areas. 743 744 // Todo: NormSInv Returns the inverse of the standard normal cumulative 745 // distribution. The distribution has a mean of zero and a standard 746 // deviation of one. 747 748 // Todo: NPer Returns the number of periods for an investment based on 749 // periodic, constant payments and a constant interest rate. 750 751 // Todo: Npv Calculates the net present value of an investment by using a 752 // discount rate and a series of future payments (negative values) and 753 // income (positive values). 754 755 // Todo: Oct2Bin Converts an octal number to binary. 756 757 // Todo: Oct2Dec Converts an octal number to decimal. 758 759 // Todo: Oct2Hex Converts an octal number to hexadecimal. 760 761 // Todo: Odd Returns number rounded up to the nearest odd integer. 762 763 // Todo: OddFPrice Returns the price per $100 face value of a security 764 // having an odd (short or long) first period. 765 766 // Todo: OddFYield Returns the yield of a security that has an odd (short or 767 // long) first period. 768 769 // Todo: OddLPrice Returns the price per $100 face value of a security 770 // having an odd (short or long) last coupon period. 771 772 // Todo: OddLYield Returns the yield of a security that has an odd (short or 773 // long) last period. 774 775 // Skip: Or Returns TRUE if any argument is TRUE; returns FALSE if all 776 // arguments are FALSE. Todo: Pearson Returns the Pearson product moment 777 // correlation coefficient, r, a dimensionless index that ranges from -1.0 778 // to 1.0 inclusive and reflects the extent of a linear relationship between 779 // two data sets. 780 781 782 // We have a more efficient implementation of percentile 783 784 // Skip: Percentile Returns the k-th percentile of values in a range. You 785 // can use this function to establish a threshold of acceptance. For 786 // example, you can decide to examine candidates who score above the 90th 787 // percentile. 788 789 // Todo: PercentRank Returns the rank of a value in a data set as a 790 // percentage of the data set. This function can be used to evaluate the 791 // relative standing of a value within a data set. For example, you can use 792 // PERCENTRANK to evaluate the standing of an aptitude test score among all 793 // scores for the test. 794 795 // Todo: Permut Returns the number of permutations for a given number of 796 // objects that can be selected from number objects. A permutation is any 797 // set or subset of objects or events where internal order is 798 // significant. Permutations are different from combinations, for which the 799 // internal order is not significant. Use this function for lottery-style 800 // probability calculations. 801 802 // Todo: Phonetic Extracts the phonetic (furigana) characters from a text 803 // string. 804 805 806 @FunctionName("Pi") 807 @Description( 808 "Returns the number 3.14159265358979, the mathematical constant pi, " 809 + "accurate to 15 digits.") 810 public static double pi() { 811 return Math.PI; 812 } 813 814 // Todo: Pmt Calculates the payment for a loan based on constant payments 815 // and a constant interest rate. 816 817 // Todo: Poisson Returns the Poisson distribution. A common application of 818 // the Poisson distribution is predicting the number of events over a 819 // specific time, such as the number of cars arriving at a toll plaza in 1 820 // minute. 821 822 823 @FunctionName("Power") 824 @Description("Returns the result of a number raised to a power.") 825 public static double power(double x, double y) { 826 return Math.pow(x, y); 827 } 828 829 // Todo: Ppmt Returns the payment on the principal for a given period for an 830 // investment based on periodic, constant payments and a constant interest 831 // rate. 832 833 // Todo: Price Returns the price per $100 face value of a security that pays 834 // periodic interest. 835 836 // Todo: PriceDisc Returns the price per $100 face value of a discounted 837 // security. 838 839 // Todo: PriceMat Returns the price per $100 face value of a security that 840 // pays interest at maturity. 841 842 // Todo: Prob Returns the probability that values in a range are between two 843 // limits. If upper_limit is not supplied, returns the probability that 844 // values in x_range are equal to lower_limit. 845 846 // Todo: Product Multiplies all the numbers given as arguments and returns 847 // the product. 848 849 // Todo: Proper Capitalizes the first letter in a text string and any other 850 // letters in text that follow any character other than a letter. Converts 851 // all other letters to lowercase letters. 852 853 // Todo: Pv Returns the present value of an investment. The present value is 854 // the total amount that a series of future payments is worth now. For 855 // example, when you borrow money, the loan amount is the present value to 856 // the lender. 857 858 // Todo: Quartile Returns the quartile of a data set. Quartiles often are 859 // used in sales and survey data to divide populations into groups. For 860 // example, you can use QUARTILE to find the top 25 percent of incomes in a 861 // population. 862 863 // Todo: Quotient Returns the integer portion of a division. Use this 864 // function when you want to discard the remainder of a division. 865 866 867 @FunctionName("Radians") 868 @Description("Converts degrees to radians.") 869 public static double radians(double number) { 870 // 180 degrees = Pi radians 871 return number / 180.0 * Math.PI; 872 } 873 874 // Todo: RandBetween Returns a random integer number between the numbers you 875 // specify. A new random integer number is returned every time the worksheet 876 // is calculated. 877 878 // Skip: Rank Returns the rank of a number in a list of numbers. The rank of 879 // a number is its size relative to other values in a list. (If you were to 880 // sort the list, the rank of the number would be its position.) Todo: Rate 881 // Returns the interest rate per period of an annuity. RATE is calculated by 882 // iteration and can have zero or more solutions. If the successive results 883 // of RATE do not converge to within 0.0000001 after 20 iterations, RATE 884 // returns the #NUM! error value. 885 886 // Todo: Received Returns the amount received at maturity for a fully 887 // invested security. 888 889 // Todo: Replace Replaces part of a text string, based on the number of 890 // characters you specify, with a different text string. 891 892 // Todo: ReplaceB REPLACEB replaces part of a text string, based on the 893 // number of bytes you specify, with a different text string. 894 895 // Todo: Rept Repeats text a given number of times. Use REPT to fill a cell 896 // with a number of instances of a text string. 897 898 // Todo: Roman Converts an arabic numeral to roman, as text. 899 900 // Todo: Round Rounds a number to a specified number of digits. 901 902 // Todo: RoundDown Rounds a number down, toward zero. 903 904 // Todo: RoundUp Rounds a number up, away from 0 (zero). 905 906 // Todo: RSq Returns the square of the Pearson product moment correlation 907 // coefficient through data points in known_y's and known_x's. For more 908 // information, see PEARSON. The r-squared value can be interpreted as the 909 // proportion of the variance in y attributable to the variance in x. 910 911 // Todo: RTD This method connects to a source to receive real-time data. 912 913 // Todo: Search SEARCH and SEARCHB locate one text string within a second 914 // text string, and return the number of the starting position of the first 915 // text string from the first character of the second text string. 916 917 // Todo: SearchB SEARCH and SEARCHB locate one text string within a second 918 // text string, and return the number of the starting position of the first 919 // text string from the first character of the second text string. 920 921 // Todo: SeriesSum Returns the sum of a power series based on the formula: 922 923 // Todo: Sinh Returns the hyperbolic sine of a number. 924 925 926 @FunctionName("Sinh") 927 @Description("Returns the hyperbolic sine of a number.") 928 public static double sinh(double number) { 929 return Math.sinh(number); 930 } 931 932 // Todo: Skew Returns the skewness of a distribution. Skewness characterizes 933 // the degree of asymmetry of a distribution around its mean. Positive 934 // skewness indicates a distribution with an asymmetric tail extending 935 // toward more positive values. Negative skewness indicates a distribution 936 // with an asymmetric tail extending toward more negative values. 937 938 // Todo: Sln Returns the straight-line depreciation of an asset for one 939 // period. 940 941 // Todo: Slope Returns the slope of the linear regression line through data 942 // points in known_y's and known_x's. The slope is the vertical distance 943 // divided by the horizontal distance between any two points on the line, 944 // which is the rate of change along the regression line. 945 946 // Todo: Small Returns the k-th smallest value in a data set. Use this 947 // function to return values with a particular relative standing in a data 948 // set. 949 950 951 @FunctionName("SqrtPi") 952 @Description("Returns the square root of (number * pi).") 953 public static double sqrtPi(double number) { 954 return Math.sqrt(number * Math.PI); 955 } 956 957 // Todo: Standardize Returns a normalized value from a distribution 958 // characterized by mean and standard_dev. 959 960 // Todo: StDev Estimates standard deviation based on a sample. The standard 961 // deviation is a measure of how widely values are dispersed from the 962 // average value (the mean). 963 964 // Todo: StDevP Calculates standard deviation based on the entire population 965 // given as arguments. The standard deviation is a measure of how widely 966 // values are dispersed from the average value (the mean). 967 968 // Todo: StEyx Returns the standard error of the predicted y-value for each 969 // x in the regression. The standard error is a measure of the amount of 970 // error in the prediction of y for an individual x. 971 972 // Todo: Substitute Substitutes new_text for old_text in a text string. Use 973 // SUBSTITUTE when you want to replace specific text in a text string; use 974 // REPLACE when you want to replace any text that occurs in a specific 975 // location in a text string. 976 977 // Todo: Subtotal Creates subtotals. 978 979 // Todo: Sum Adds all the numbers in a range of cells. 980 981 // Todo: SumIf Adds the cells specified by a given criteria. 982 983 // Todo: SumIfs Adds the cells in a range that meet multiple criteria. 984 985 // Todo: SumProduct Multiplies corresponding components in the given arrays, 986 // and returns the sum of those products. 987 988 // Todo: SumSq Returns the sum of the squares of the arguments. 989 990 // Todo: SumX2MY2 Returns the sum of the difference of squares of 991 // corresponding values in two arrays. 992 993 // Todo: SumX2PY2 Returns the sum of the sum of squares of corresponding 994 // values in two arrays. The sum of the sum of squares is a common term in 995 // many statistical calculations. 996 997 // Todo: SumXMY2 Returns the sum of squares of differences of corresponding 998 // values in two arrays. 999 1000 // Todo: Syd Returns the sum-of-years' digits depreciation of an asset for a 1001 // specified period. 1002 1003 1004 @FunctionName("Tanh") 1005 @Description("Returns the hyperbolic tangent of a number.") 1006 public static double tanh(double number) { 1007 return Math.tanh(number); 1008 } 1009 1010 // Todo: TBillEq Returns the bond-equivalent yield for a Treasury bill. 1011 1012 // Todo: TBillPrice Returns the price per $100 face value for a Treasury 1013 // bill. 1014 1015 // Todo: TBillYield Returns the yield for a Treasury bill. 1016 1017 // Todo: TDist Returns the Percentage Points (probability) for the Student 1018 // t-distribution where a numeric value (x) is a calculated value of t for 1019 // which the Percentage Points are to be computed. The t-distribution is 1020 // used in the hypothesis testing of small sample data sets. Use this 1021 // function in place of a table of critical values for the t-distribution. 1022 1023 // Todo: Text Converts a value to text in a specific number format. 1024 1025 // Todo: TInv Returns the t-value of the Student's t-distribution as a 1026 // function of the probability and the degrees of freedom. 1027 1028 // Todo: Transpose Returns a vertical range of cells as a horizontal range, 1029 // or vice versa. TRANSPOSE must be entered as an array formula in a range 1030 // that has the same number of rows and columns, respectively, as an array 1031 // has columns and rows. Use TRANSPOSE to shift the vertical and horizontal 1032 // orientation of an array on a worksheet. 1033 1034 // Todo: Trend Returns values along a linear trend. Fits a straight line 1035 // (using the method of least squares) to the arrays known_y's and 1036 // known_x's. Returns the y-values along that line for the array of new_x's 1037 // that you specify. 1038 1039 // Todo: Trim Removes all spaces from text except for single spaces between 1040 // words. Use TRIM on text that you have received from another application 1041 // that may have irregular spacing. 1042 1043 // Todo: TrimMean Returns the mean of the interior of a data set. TRIMMEAN 1044 // calculates the mean taken by excluding a percentage of data points from 1045 // the top and bottom tails of a data set. You can use this function when 1046 // you wish to exclude outlying data from your analysis. 1047 1048 // Todo: TTest Returns the probability associated with a Student's 1049 // t-Test. Use TTEST to determine whether two samples are likely to have 1050 // come from the same two underlying populations that have the same mean. 1051 1052 // Todo: USDollar The description for this item will appear in the final 1053 // release of Office 2007. 1054 1055 // Todo: Var Estimates variance based on a sample. 1056 1057 // Todo: VarP Calculates variance based on the entire population. 1058 1059 // Todo: Vdb Returns the depreciation of an asset for any period you 1060 // specify, including partial periods, using the double-declining balance 1061 // method or some other method you specify. VDB stands for variable 1062 // declining balance. 1063 1064 // Todo: VLookup Searches for a value in the first column of a table array 1065 // and returns a value in the same row from another column in the table 1066 // array. 1067 1068 // Todo: Weekday Returns the day of the week corresponding to a date. The 1069 // day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by 1070 // default. 1071 1072 // Todo: WeekNum Returns a number that indicates where the week falls 1073 // numerically within a year. 1074 1075 // Todo: Weibull Returns the Weibull distribution. Use this distribution in 1076 // reliability analysis, such as calculating a device's mean time to 1077 // failure. 1078 1079 // Todo: WorkDay Returns a number that represents a date that is the 1080 // indicated number of working days before or after a date (the starting 1081 // date). Working days exclude weekends and any dates identified as 1082 // holidays. Use WORKDAY to exclude weekends or holidays when you calculate 1083 // invoice due dates, expected delivery times, or the number of days of work 1084 // performed. 1085 1086 // Todo: Xirr Returns the internal rate of return for a schedule of cash 1087 // flows that is not necessarily periodic. To calculate the internal rate of 1088 // return for a series of periodic cash flows, use the IRR function. 1089 1090 // Todo: Xnpv The description for this item will appear in the final release 1091 // of Office 2007. 1092 1093 // Todo: YearFrac Calculates the fraction of the year represented by the 1094 // number of whole days between two dates (the start_date and the 1095 // end_date). Use the YEARFRAC worksheet function to identify the proportion 1096 // of a whole year's benefits or obligations to assign to a specific term. 1097 1098 // Todo: YieldDisc Returns the annual yield for a discounted security. 1099 1100 // Todo: YieldMat Returns the annual yield of a security that pays interest 1101 // at maturity. 1102 1103 // Todo: ZTest Returns the one-tailed probability-value of a z-test. For a 1104 // given hypothesized population mean, ZTEST returns the probability that 1105 // the sample mean would be greater than the average of observations in the 1106 // data set (array) -- that is, the observed sample mean. 1107 1108} 1109 1110// End Excel.java