kspread

kspread_functions_math.cc

00001 /* This file is part of the KDE project
00002    Copyright (C) 1998-2002 The KSpread Team
00003                            www.koffice.org/kspread
00004    Copyright (C) 2005 Tomas Mecir <mecirt@gmail.com>
00005 
00006    This library is free software; you can redistribute it and/or
00007    modify it under the terms of the GNU Library General Public
00008    License as published by the Free Software Foundation; either
00009    version 2 of the License.
00010 
00011    This library is distributed in the hope that it will be useful,
00012    but WITHOUT ANY WARRANTY; without even the implied warranty of
00013    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00014    Library General Public License for more details.
00015 
00016    You should have received a copy of the GNU Library General Public License
00017    along with this library; see the file COPYING.LIB.  If not, write to
00018    the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
00019  * Boston, MA 02110-1301, USA.
00020 */
00021 
00022 // built-in math functions
00023 
00024 #include <kdebug.h>
00025 #include <klocale.h>
00026 
00027 #include "functions.h"
00028 #include "valuecalc.h"
00029 #include "valueconverter.h"
00030 
00031 // these two are needed for SUBTOTAL:
00032 #include "kspread_cell.h"
00033 #include "kspread_sheet.h"
00034 
00035 // needed for RANDBINOM and so
00036 #include <math.h>
00037 
00038 using namespace KSpread;
00039 
00040 // RANDBINOM and RANDNEGBINOM won't support arbitrary precision
00041 
00042 // prototypes
00043 Value func_abs (valVector args, ValueCalc *calc, FuncExtra *);
00044 Value func_ceil (valVector args, ValueCalc *calc, FuncExtra *);
00045 Value func_ceiling (valVector args, ValueCalc *calc, FuncExtra *);
00046 Value func_count (valVector args, ValueCalc *calc, FuncExtra *);
00047 Value func_counta (valVector args, ValueCalc *calc, FuncExtra *);
00048 Value func_countblank (valVector args, ValueCalc *calc, FuncExtra *);
00049 Value func_countif (valVector args, ValueCalc *calc, FuncExtra *);
00050 Value func_cur (valVector args, ValueCalc *calc, FuncExtra *);
00051 Value func_div (valVector args, ValueCalc *calc, FuncExtra *);
00052 Value func_eps (valVector args, ValueCalc *calc, FuncExtra *);
00053 Value func_even (valVector args, ValueCalc *calc, FuncExtra *);
00054 Value func_exp (valVector args, ValueCalc *calc, FuncExtra *);
00055 Value func_fact (valVector args, ValueCalc *calc, FuncExtra *);
00056 Value func_factdouble (valVector args, ValueCalc *calc, FuncExtra *);
00057 Value func_fib (valVector args, ValueCalc *calc, FuncExtra *);
00058 Value func_floor (valVector args, ValueCalc *calc, FuncExtra *);
00059 Value func_gcd (valVector args, ValueCalc *calc, FuncExtra *);
00060 Value func_int (valVector args, ValueCalc *calc, FuncExtra *);
00061 Value func_inv (valVector args, ValueCalc *calc, FuncExtra *);
00062 Value func_kproduct (valVector args, ValueCalc *calc, FuncExtra *);
00063 Value func_lcm (valVector args, ValueCalc *calc, FuncExtra *);
00064 Value func_ln (valVector args, ValueCalc *calc, FuncExtra *);
00065 Value func_log2 (valVector args, ValueCalc *calc, FuncExtra *);
00066 Value func_log10 (valVector args, ValueCalc *calc, FuncExtra *);
00067 Value func_logn (valVector args, ValueCalc *calc, FuncExtra *);
00068 Value func_max (valVector args, ValueCalc *calc, FuncExtra *);
00069 Value func_maxa (valVector args, ValueCalc *calc, FuncExtra *);
00070 Value func_mdeterm (valVector args, ValueCalc *calc, FuncExtra *);
00071 Value func_min (valVector args, ValueCalc *calc, FuncExtra *);
00072 Value func_mina (valVector args, ValueCalc *calc, FuncExtra *);
00073 Value func_mmult (valVector args, ValueCalc *calc, FuncExtra *);
00074 Value func_mod (valVector args, ValueCalc *calc, FuncExtra *);
00075 Value func_mround (valVector args, ValueCalc *calc, FuncExtra *);
00076 Value func_mult (valVector args, ValueCalc *calc, FuncExtra *);
00077 Value func_multinomial (valVector args, ValueCalc *calc, FuncExtra *);
00078 Value func_odd (valVector args, ValueCalc *calc, FuncExtra *);
00079 Value func_pow (valVector args, ValueCalc *calc, FuncExtra *);
00080 Value func_quotient (valVector args, ValueCalc *calc, FuncExtra *);
00081 Value func_product (valVector args, ValueCalc *calc, FuncExtra *);
00082 Value func_rand (valVector args, ValueCalc *calc, FuncExtra *);
00083 Value func_randbetween (valVector args, ValueCalc *calc, FuncExtra *);
00084 Value func_randbernoulli (valVector args, ValueCalc *calc, FuncExtra *);
00085 Value func_randbinom (valVector args, ValueCalc *calc, FuncExtra *);
00086 Value func_randexp (valVector args, ValueCalc *calc, FuncExtra *);
00087 Value func_randnegbinom (valVector args, ValueCalc *calc, FuncExtra *);
00088 Value func_randnorm (valVector args, ValueCalc *calc, FuncExtra *);
00089 Value func_randpoisson (valVector args, ValueCalc *calc, FuncExtra *);
00090 Value func_rootn (valVector args, ValueCalc *calc, FuncExtra *);
00091 Value func_round (valVector args, ValueCalc *calc, FuncExtra *);
00092 Value func_rounddown (valVector args, ValueCalc *calc, FuncExtra *);
00093 Value func_roundup (valVector args, ValueCalc *calc, FuncExtra *);
00094 Value func_sign (valVector args, ValueCalc *calc, FuncExtra *);
00095 Value func_sqrt (valVector args, ValueCalc *calc, FuncExtra *);
00096 Value func_sqrtpi (valVector args, ValueCalc *calc, FuncExtra *);
00097 Value func_subtotal (valVector args, ValueCalc *calc, FuncExtra *);
00098 Value func_sum (valVector args, ValueCalc *calc, FuncExtra *);
00099 Value func_suma (valVector args, ValueCalc *calc, FuncExtra *);
00100 Value func_sumif (valVector args, ValueCalc *calc, FuncExtra *);
00101 Value func_sumsq (valVector args, ValueCalc *calc, FuncExtra *);
00102 Value func_trunc (valVector args, ValueCalc *calc, FuncExtra *);
00103 
00104 
00105 // Value func_multipleOP (valVector args, ValueCalc *calc, FuncExtra *);
00106 
00107 // registers all math functions
00108 void RegisterMathFunctions()
00109 {
00110   FunctionRepository* repo = FunctionRepository::self();
00111   Function *f;
00112 
00113 /*
00114   f = new Function ("MULTIPLEOPERATIONS", func_multipleOP);
00115   repo->add (f);
00116 */
00117 
00118   // functions that don't take array parameters
00119   f = new Function ("ABS",           func_abs);
00120   repo->add (f);
00121   f = new Function ("CEIL",          func_ceil);
00122   repo->add (f);
00123   f = new Function ("CEILING",       func_ceiling);
00124   f->setParamCount (1, 2);
00125   repo->add (f);
00126   f = new Function ("CUR",           func_cur);
00127   repo->add (f);
00128   f = new Function ("EPS",           func_eps);
00129   f->setParamCount (0);
00130   repo->add (f);
00131   f = new Function ("EVEN",          func_even);
00132   repo->add (f);
00133   f = new Function ("EXP",           func_exp);
00134   repo->add (f);
00135   f = new Function ("FACT",          func_fact);
00136   repo->add (f);
00137   f = new Function ("FACTDOUBLE",    func_factdouble);
00138   repo->add (f);
00139   f = new Function ("FIB",           func_fib); // KSpread-specific, like Quattro-Pro's FIB
00140   repo->add (f);
00141   f = new Function ("FLOOR",         func_floor);
00142   repo->add (f);
00143   f = new Function ("INT",           func_int);
00144   repo->add (f);
00145   f = new Function ("INV",           func_inv);
00146   repo->add (f);
00147   f = new Function ("LN",            func_ln);
00148   repo->add (f);
00149   f = new Function ("LOG",           func_log10);
00150   repo->add (f);
00151   f = new Function ("LOG2",          func_log2);
00152   repo->add (f);
00153   f = new Function ("LOG10",         func_log10);   // same as LOG
00154   repo->add (f);
00155   f = new Function ("LOGN",          func_logn);
00156   f->setParamCount (2);
00157   repo->add (f);
00158   f = new Function ("MOD",           func_mod);
00159   f->setParamCount (2);
00160   repo->add (f);
00161   f = new Function ("MROUND",        func_mround);
00162   f->setParamCount (2);
00163   repo->add (f);
00164   f = new Function ("MULTINOMIAL",   func_multinomial);
00165   f->setParamCount (1, -1);
00166   repo->add (f);
00167   f = new Function ("ODD",           func_odd);
00168   repo->add (f);
00169   f = new Function ("POW",         func_pow);
00170   f->setParamCount (2);
00171   repo->add (f);
00172   f = new Function ("POWER",         func_pow);
00173   f->setParamCount (2);
00174   repo->add (f);
00175   f = new Function ("QUOTIENT",      func_quotient);
00176   f->setParamCount (2);
00177   repo->add (f);
00178   f = new Function ("RAND",          func_rand);
00179   f->setParamCount (0);
00180   repo->add (f);
00181   f = new Function ("RANDBERNOULLI", func_randbernoulli);
00182   repo->add (f);
00183   f = new Function ("RANDBETWEEN",   func_randbetween);
00184   f->setParamCount (2);
00185   repo->add (f);
00186   f = new Function ("RANDBINOM",     func_randbinom);
00187   f->setParamCount (2);
00188   repo->add (f);
00189   f = new Function ("RANDEXP",       func_randexp);
00190   repo->add (f);
00191   f = new Function ("RANDNEGBINOM",  func_randnegbinom);
00192   f->setParamCount (2);
00193   repo->add (f);
00194   f = new Function ("RANDNORM",      func_randnorm);
00195   f->setParamCount (2);
00196   repo->add (f);
00197   f = new Function ("RANDPOISSON",   func_randpoisson);
00198   repo->add (f);
00199   f = new Function ("ROOTN",         func_rootn);
00200   f->setParamCount (2);
00201   repo->add (f);
00202   f = new Function ("ROUND",         func_round);
00203   f->setParamCount (1, 2);
00204   repo->add (f);
00205   f = new Function ("ROUNDDOWN",     func_rounddown);
00206   f->setParamCount (1, 2);
00207   repo->add (f);
00208   f = new Function ("ROUNDUP",       func_roundup);
00209   f->setParamCount (1, 2);
00210   repo->add (f);
00211   f = new Function ("SIGN",          func_sign);
00212   repo->add (f);
00213   f = new Function ("SQRT",          func_sqrt);
00214   repo->add (f);
00215   f = new Function ("SQRTPI",        func_sqrtpi);
00216   repo->add (f);
00217   f = new Function ("TRUNC",         func_trunc);
00218   f->setParamCount (1, 2);
00219   repo->add (f);
00220 
00221   // functions that operate over arrays
00222   f = new Function ("COUNT",         func_count);
00223   f->setParamCount (1, -1);
00224   f->setAcceptArray ();
00225   repo->add (f);
00226   f = new Function ("COUNTA",        func_counta);
00227   f->setParamCount (1, -1);
00228   f->setAcceptArray ();
00229   repo->add (f);
00230   f = new Function ("COUNTBLANK",    func_countblank);
00231   f->setParamCount (1, -1);
00232   f->setAcceptArray ();
00233   repo->add (f);
00234   f = new Function ("COUNTIF",       func_countif);
00235   f->setParamCount (2);
00236   f->setAcceptArray ();
00237   repo->add (f);
00238   f = new Function ("DIV",           func_div);
00239   f->setParamCount (1, -1);
00240   f->setAcceptArray ();
00241   repo->add (f);
00242   f = new Function ("G_PRODUCT",     func_kproduct); // Gnumeric compatibility
00243   f->setParamCount (1, -1);
00244   f->setAcceptArray ();
00245   repo->add (f);
00246   f = new Function ("GCD",           func_gcd);
00247   f->setParamCount (1, -1);
00248   f->setAcceptArray ();
00249   repo->add (f);
00250   f = new Function ("KPRODUCT",      func_kproduct);
00251   f->setParamCount (1, -1);
00252   f->setAcceptArray ();
00253   repo->add (f);
00254   f = new Function ("LCM",           func_lcm);
00255   f->setParamCount (1, -1);
00256   f->setAcceptArray ();
00257   repo->add (f);
00258   f = new Function ("MAX",           func_max);
00259   f->setParamCount (1, -1);
00260   f->setAcceptArray ();
00261   repo->add (f);
00262   f = new Function ("MAXA",          func_maxa);
00263   f->setParamCount (1, -1);
00264   f->setAcceptArray ();
00265   repo->add (f);
00266   f = new Function ("MDETERM",          func_mdeterm);
00267   f->setParamCount (1);
00268   f->setAcceptArray ();
00269   repo->add (f);
00270   f = new Function ("MIN",           func_min);
00271   f->setParamCount (1, -1);
00272   f->setAcceptArray ();
00273   repo->add (f);
00274   f = new Function ("MINA",          func_mina);
00275   f->setParamCount (1, -1);
00276   f->setAcceptArray ();
00277   repo->add (f);
00278   f = new Function ("MMULT",          func_mmult);
00279   f->setParamCount (2);
00280   f->setAcceptArray ();
00281   repo->add (f);
00282   f = new Function ("MULTIPLY",      func_product);  // same as PRODUCT
00283   f->setParamCount (1, -1);
00284   f->setAcceptArray ();
00285   repo->add (f);
00286   f = new Function ("PRODUCT",       func_product);
00287   f->setParamCount (1, -1);
00288   f->setAcceptArray ();
00289   repo->add (f);
00290   f = new Function ("SUM",           func_sum);
00291   f->setParamCount (1, -1);
00292   f->setAcceptArray ();
00293   repo->add (f);
00294   f = new Function ("SUMA",          func_suma);
00295   f->setParamCount (1, -1);
00296   f->setAcceptArray ();
00297   repo->add (f);
00298   f = new Function ("SUBTOTAL",      func_subtotal);
00299   f->setParamCount (2);
00300   f->setAcceptArray ();
00301   f->setNeedsExtra (true);
00302   repo->add (f);
00303   f = new Function ("SUMIF",         func_sumif);
00304   f->setParamCount (2, 3);
00305   f->setAcceptArray ();
00306   repo->add (f);
00307   f = new Function ("SUMSQ",         func_sumsq);
00308   f->setParamCount (1, -1);
00309   f->setAcceptArray ();
00310   repo->add (f);
00311 }
00312 
00313 // Function: SQRT
00314 Value func_sqrt (valVector args, ValueCalc *calc, FuncExtra *)
00315 {
00316   return calc->sqrt (args[0]);
00317 }
00318 
00319 // Function: SQRTPI
00320 Value func_sqrtpi (valVector args, ValueCalc *calc, FuncExtra *)
00321 {
00322   // sqrt (val * PI)
00323   return calc->sqrt (calc->mul (args[0], calc->pi()));
00324 }
00325 
00326 // Function: ROOTN
00327 Value func_rootn (valVector args, ValueCalc *calc, FuncExtra *)
00328 {
00329   return calc->pow (args[0], calc->div (1, args[1]));
00330 }
00331 
00332 // Function: CUR
00333 Value func_cur (valVector args, ValueCalc *calc, FuncExtra *)
00334 {
00335   return calc->pow (args[0], 1.0/3.0);
00336 }
00337 
00338 // Function: ABS
00339 Value func_abs (valVector args, ValueCalc *calc, FuncExtra *)
00340 {
00341   return calc->abs (args[0]);
00342 }
00343 
00344 // Function: exp
00345 Value func_exp (valVector args, ValueCalc *calc, FuncExtra *)
00346 {
00347   return calc->exp (args[0]);
00348 }
00349 
00350 // Function: ceil
00351 Value func_ceil (valVector args, ValueCalc *calc, FuncExtra *)
00352 {
00353   return calc->roundUp (args[0], 0);
00354 }
00355 
00356 // Function: ceiling
00357 Value func_ceiling (valVector args, ValueCalc *calc, FuncExtra *)
00358 {
00359   Value number = args[0];
00360   Value res;
00361   if (args.count() == 2)
00362     res = args[1];
00363   else
00364     res = calc->gequal (number, 0.0) ? 1.0 : -1.0;
00365 
00366   if (calc->isZero(res))
00367     return Value::errorDIV0();
00368 
00369   Value d = calc->div (number, res);
00370   if (calc->greater (0, d))
00371     return Value::errorVALUE();
00372 
00373   Value rud = calc->roundDown (d);
00374   if (calc->approxEqual (rud, d))
00375     d = calc->mul (rud, res);
00376   else
00377     d = calc->mul (calc->roundUp (d), res);
00378 
00379   return d;
00380 }
00381 
00382 // Function: floor
00383 Value func_floor (valVector args, ValueCalc *calc, FuncExtra *)
00384 {
00385   return calc->roundDown (args[0], 0);
00386 }
00387 
00388 // Function: ln
00389 Value func_ln (valVector args, ValueCalc *calc, FuncExtra *)
00390 {
00391   return calc->ln (args[0]);
00392 }
00393 
00394 // Function: LOGn
00395 Value func_logn (valVector args, ValueCalc *calc, FuncExtra *)
00396 {
00397   return calc->log (args[0], args[1]);
00398 }
00399 
00400 // Function: LOG2
00401 Value func_log2 (valVector args, ValueCalc *calc, FuncExtra *)
00402 {
00403   return calc->log (args[0], 2.0);
00404 }
00405 
00406 // Function: LOG10
00407 Value func_log10 (valVector args, ValueCalc *calc, FuncExtra *)
00408 {
00409   return calc->log (args[0]);
00410 }
00411 
00412 // Function: sum
00413 Value func_sum (valVector args, ValueCalc *calc, FuncExtra *)
00414 {
00415   return calc->sum (args, false);
00416 }
00417 
00418 // Function: suma
00419 Value func_suma (valVector args, ValueCalc *calc, FuncExtra *)
00420 {
00421   return calc->sum (args, true);
00422 }
00423 
00424 Value func_sumif (valVector args, ValueCalc *calc, FuncExtra *)
00425 {
00426   Value checkRange = args[0];
00427   QString condition = calc->conv()->asString (args[1]).asString();
00428   Value sumRange = checkRange;
00429   if (args.count() == 3)
00430     sumRange = args[2];
00431 
00432   Condition cond;
00433   calc->getCond (cond, condition);
00434 
00435   return calc->sumIf (sumRange, checkRange, cond);
00436 }
00437 
00438 // Function: product
00439 Value func_product (valVector args, ValueCalc *calc, FuncExtra *)
00440 {
00441   return calc->product (args, 0.0);
00442 }
00443 
00444 // Function: kproduct
00445 Value func_kproduct (valVector args, ValueCalc *calc, FuncExtra *)
00446 {
00447   return calc->product (args, 1.0);
00448 }
00449 
00450 // Function: DIV
00451 Value func_div (valVector args, ValueCalc *calc, FuncExtra *)
00452 {
00453   Value val = args[0];
00454   for (unsigned int i = 1; i < args.count(); ++i)
00455   {
00456     val = calc->div (val, args[i]);
00457     if (val.isError())
00458       return val;
00459   }
00460   return val;
00461 }
00462 
00463 // Function: SUMSQ
00464 Value func_sumsq (valVector args, ValueCalc *calc, FuncExtra *)
00465 {
00466   Value res;
00467   calc->arrayWalk (args, res, calc->awFunc ("sumsq"), 0);
00468   return res;
00469 }
00470 
00471 // Function: MAX
00472 Value func_max (valVector args, ValueCalc *calc, FuncExtra *)
00473 {
00474   Value m = calc->max (args, false);
00475   return m.isEmpty() ? Value(0.0) : m;
00476 }
00477 
00478 // Function: MAXA
00479 Value func_maxa (valVector args, ValueCalc *calc, FuncExtra *)
00480 {
00481   Value m = calc->max (args);
00482   return m.isEmpty() ? Value(0.0) : m;
00483 }
00484 
00485 // Function: MIN
00486 Value func_min (valVector args, ValueCalc *calc, FuncExtra *)
00487 {
00488   Value m = calc->min (args, false);
00489   return m.isEmpty() ? Value(0.0) : m;
00490 }
00491 
00492 // Function: MINA
00493 Value func_mina (valVector args, ValueCalc *calc, FuncExtra *)
00494 {
00495   Value m = calc->min (args);
00496   return m.isEmpty() ? Value(0.0) : m;
00497 }
00498 
00499 // Function: INT
00500 Value func_int (valVector args, ValueCalc *calc, FuncExtra *)
00501 {
00502   return calc->conv()->asInteger (args[0]);
00503 }
00504 
00505 // Function: QUOTIENT
00506 Value func_quotient (valVector args, ValueCalc *calc, FuncExtra *)
00507 {
00508   if (calc->isZero (args[1]))
00509     return Value::errorDIV0();
00510   return calc->conv()->asInteger (calc->div (args[0], args[1]));
00511 }
00512 
00513 
00514 // Function: eps
00515 Value func_eps (valVector, ValueCalc *calc, FuncExtra *)
00516 {
00517   return calc->eps ();
00518 }
00519 
00520 Value func_randexp (valVector args, ValueCalc *calc, FuncExtra *)
00521 {
00522   // -1 * d * log (random)
00523   return calc->mul (calc->mul (args[0], -1), calc->random());
00524 }
00525 
00526 Value func_randbinom (valVector args, ValueCalc *calc, FuncExtra *)
00527 {
00528   // this function will not support arbitrary precision
00529 
00530   double d  = calc->conv()->asFloat (args[0]).asFloat();
00531   int    tr = calc->conv()->asInteger (args[1]).asInteger();
00532 
00533   if ( d < 0 || d > 1 )
00534     return Value::errorVALUE();
00535 
00536   if ( tr < 0 )
00537     return Value::errorVALUE();
00538 
00539   // taken from gnumeric
00540   double x = pow(1 - d, tr);
00541   double r = (double) rand() / ( RAND_MAX + 1.0 );
00542   double t = x;
00543   int i = 0;
00544 
00545   while (r > t)
00546   {
00547     x *= (((tr - i) * d) / ((1 + i) * (1 - d)));
00548     i++;
00549     t += x;
00550   }
00551 
00552   return Value (i);
00553 }
00554 
00555 Value func_randnegbinom (valVector args, ValueCalc *calc, FuncExtra *)
00556 {
00557   // this function will not support arbitrary precision
00558 
00559   double d  = calc->conv()->asFloat (args[0]).asFloat();
00560   int    f = calc->conv()->asInteger (args[1]).asInteger();
00561 
00562   if ( d < 0 || d > 1 )
00563     return Value::errorVALUE();
00564 
00565   if ( f < 0 )
00566     return Value::errorVALUE();
00567 
00568 
00569   // taken from Gnumeric
00570   double x = pow(d, f);
00571   double r = (double) rand() / ( RAND_MAX + 1.0 );
00572   double t = x;
00573   int i = 0;
00574 
00575   while (r > t)
00576   {
00577     x *= ( ( ( f + i ) * ( 1 - d ) ) / (1 + i) ) ;
00578     i++;
00579     t += x;
00580   }
00581 
00582   return Value (i);
00583 }
00584 
00585 Value func_randbernoulli (valVector args, ValueCalc *calc, FuncExtra *)
00586 {
00587   Value rnd = calc->random ();
00588   return Value (calc->greater (rnd, args[0]) ? 1.0 : 0.0);
00589 }
00590 
00591 Value func_randnorm (valVector args, ValueCalc *calc, FuncExtra *)
00592 {
00593   Value mu = args[0];
00594   Value sigma = args[1];
00595 
00596   //using polar form of the Box-Muller transformation
00597   //refer to http://www.taygeta.com/random/gaussian.html for more info
00598 
00599   Value x1, x2, w;
00600   do {
00601     // x1,x2 = 2 * random() - 1
00602     x1 = calc->random (2.0);
00603     x2 = calc->random (2.0);
00604     x1 = calc->sub (x1, 1);
00605     x1 = calc->sub (x2, 1);
00606     w = calc->add (calc->sqr(x1), calc->sqr (x2));
00607   } while (calc->gequal (w, 1.0));   // w >= 1.0
00608 
00609   //sqrt ((-2.0 * log (w)) / w) :
00610   w = calc->sqrt (calc->div (calc->mul (-2.0, calc->ln (w)), w));
00611   Value res = calc->mul (x1, w);
00612 
00613   res = calc->add (calc->mul (res, sigma), mu);  // res*sigma + mu
00614   return res;
00615 }
00616 
00617 Value func_randpoisson (valVector args, ValueCalc *calc, FuncExtra *)
00618 {
00619   if (calc->lower (args[0], 0))
00620     return Value::errorVALUE();
00621 
00622   // taken from Gnumeric...
00623   Value x = calc->exp (calc->mul (-1, args[0]));   // e^(-A)
00624   Value r = calc->random ();
00625   Value t = x;
00626   int i = 0;
00627 
00628   while (calc->greater (r, t)) {   // r > t
00629     x = calc->mul (x, calc->div (args[0], i + 1));  // x *= (A/(i+1))
00630     t = calc->add (t, x);    //t += x
00631     i++;
00632   }
00633 
00634   return Value (i);
00635 }
00636 
00637 // Function: rand
00638 Value func_rand (valVector, ValueCalc *calc, FuncExtra *)
00639 {
00640   return calc->random ();
00641 }
00642 
00643 // Function: RANDBETWEEN
00644 Value func_randbetween (valVector args, ValueCalc *calc, FuncExtra *)
00645 {
00646   Value v1 = args[0];
00647   Value v2 = args[1];
00648   if (calc->greater (v2, v1)) {
00649     v1 = args[1];
00650     v2 = args[0];
00651   }
00652   return calc->add (v1, calc->random (calc->sub (v2, v1)));
00653 }
00654 
00655 // Function: POW
00656 Value func_pow (valVector args, ValueCalc *calc, FuncExtra *)
00657 {
00658   return calc->pow (args[0], args[1]);
00659 }
00660 
00661 // Function: MOD
00662 Value func_mod (valVector args, ValueCalc *calc, FuncExtra *)
00663 {
00664   return calc->mod (args[0], args[1]);
00665 }
00666 
00667 // Function: fact
00668 Value func_fact (valVector args, ValueCalc *calc, FuncExtra *)
00669 {
00670   return calc->fact (args[0]);
00671 }
00672 
00673 // Function: FACTDOUBLE
00674 Value func_factdouble (valVector args, ValueCalc *calc, FuncExtra *)
00675 {
00676   return calc->factDouble (args[0]);
00677 }
00678 
00679 // Function: MULTINOMIAL
00680 Value func_multinomial (valVector args, ValueCalc *calc, FuncExtra *)
00681 {
00682   // (a+b+c)! / a!b!c!  (any number of params possible)
00683   Value num = 0, den = 1;
00684   for (unsigned int i = 0; i < args.count(); ++i) {
00685     num = calc->add (num, args[i]);
00686     den = calc->mul (den, calc->fact (args[i]));
00687   }
00688   num = calc->fact (num);
00689   return calc->div (num, den);
00690 }
00691 
00692 // Function: sign
00693 Value func_sign (valVector args, ValueCalc *calc, FuncExtra *)
00694 {
00695   return Value (calc->sign (args[0]));
00696 }
00697 
00698 // Function: INV
00699 Value func_inv (valVector args, ValueCalc *calc, FuncExtra *)
00700 {
00701   return calc->mul (args[0], -1);
00702 }
00703 
00704 Value func_mround (valVector args, ValueCalc *calc, FuncExtra *)
00705 {
00706   Value d = args[0];
00707   Value m = args[1];
00708 
00709   // signs must be the same
00710   if ((calc->greater (d, 0) && calc->lower (m, 0))
00711       || (calc->lower (d, 0) && calc->greater (m, 0)))
00712     return Value::errorVALUE();
00713 
00714   int sign = 1;
00715 
00716   if (calc->lower (d, 0))
00717   {
00718     sign = -1;
00719     d = calc->mul (d, -1);
00720     m = calc->mul (m, -1);
00721   }
00722 
00723   // from gnumeric:
00724   Value mod = calc->mod (d, m);
00725   Value div = calc->sub (d, mod);
00726 
00727   Value result = div;
00728   if (calc->greater (mod, calc->div (m, 2)))  // mod > m/2
00729     result = calc->add (result, m);     // result += m
00730   result = calc->mul (result, sign);    // add the sign
00731 
00732   return result;
00733 }
00734 
00735 // Function: ROUNDDOWN
00736 Value func_rounddown (valVector args, ValueCalc *calc, FuncExtra *)
00737 {
00738   if (args.count() == 2)
00739     return calc->roundDown (args[0], args[1]);
00740   return calc->roundDown (args[0], 0);
00741 }
00742 
00743 // Function: ROUNDUP
00744 Value func_roundup (valVector args, ValueCalc *calc, FuncExtra *)
00745 {
00746   if (args.count() == 2)
00747     return calc->roundUp (args[0], args[1]);
00748   return calc->roundUp (args[0], 0);
00749 }
00750 
00751 // Function: ROUND
00752 Value func_round (valVector args, ValueCalc *calc, FuncExtra *)
00753 {
00754   if (args.count() == 2)
00755     return calc->round (args[0], args[1]);
00756   return calc->round (args[0], 0);
00757 }
00758 
00759 // Function: EVEN
00760 Value func_even (valVector args, ValueCalc *calc, FuncExtra *)
00761 {
00762   const Value value = calc->roundUp (args[0], 0);
00763   return calc->isZero( calc->mod(value, 2) ) ? value : calc->add(value, 1);
00764 }
00765 
00766 // Function: ODD
00767 Value func_odd (valVector args, ValueCalc *calc, FuncExtra *)
00768 {
00769   const Value value = calc->roundUp (args[0], 0);
00770   return calc->isZero( calc->mod(value, 2) ) ? calc->add(value, 1) : value;
00771 }
00772 
00773 Value func_trunc (valVector args, ValueCalc *calc, FuncExtra *)
00774 {
00775   if (args.count() == 1)
00776     return calc->roundDown (args[0]);
00777   return calc->roundDown (args[0], args[1]);
00778 }
00779 
00780 // Function: COUNT
00781 Value func_count (valVector args, ValueCalc *calc, FuncExtra *)
00782 {
00783   return calc->count (args, false);
00784 }
00785 
00786 // Function: COUNTA
00787 Value func_counta (valVector args, ValueCalc *calc, FuncExtra *)
00788 {
00789   return calc->count (args);
00790 }
00791 
00792 // Function: COUNTBLANK
00793 Value func_countblank (valVector args, ValueCalc *, FuncExtra *)
00794 {
00795   int cnt = 0;
00796   for (unsigned int i = 0; i < args.count(); ++i)
00797     if (args[i].isArray()) {
00798       int rows = args[i].rows();
00799       int cols = args[i].columns();
00800       for (int r = 0; r < rows; ++r)
00801         for (int c = 0; c < cols; ++c)
00802           if (args[i].element (c, r).isEmpty())
00803             cnt++;
00804     } else
00805       if (args[i].isEmpty())
00806         cnt++;
00807   return Value (cnt);
00808 }
00809 
00810 // Function: COUNTIF
00811 Value func_countif (valVector args, ValueCalc *calc, FuncExtra *)
00812 {
00813   Value range = args[0];
00814   QString condition = calc->conv()->asString (args[1]).asString();
00815 
00816   Condition cond;
00817   calc->getCond (cond, condition);
00818 
00819   return calc->countIf (range, cond);
00820 }
00821 
00822 // Function: FIB
00823 Value func_fib (valVector args, ValueCalc *calc, FuncExtra *)
00824 {
00825 /*
00826 Lucas' formula for the nth Fibonacci number F(n) is given by
00827 
00828          ((1+sqrt(5))/2)^n - ((1-sqrt(5))/2)^n
00829   F(n) = ------------------------------------- .
00830                          sqrt(5)
00831 
00832 */
00833   Value n = args[0];
00834   Value s = calc->sqrt (5.0);
00835   // u1 = ((1+sqrt(5))/2)^n
00836   Value u1 = calc->pow (calc->div (calc->add (1, s), 2), n);
00837   // u2 = ((1-sqrt(5))/2)^n
00838   Value u2 = calc->pow (calc->div (calc->sub (1, s), 2), n);
00839 
00840   Value result = calc->div (calc->sub (u1, u2), s);
00841   return result;
00842 }
00843 
00844 static Value func_gcd_helper(const Value &val, ValueCalc *calc)
00845 {
00846   Value res = 0;
00847   if (!val.isArray ())
00848     return val;
00849   for (unsigned int row = 0; row < val.rows(); ++row)
00850     for (unsigned int col = 0; col < val.columns(); ++col)
00851     {
00852       Value v = val.element (col, row);
00853       if (v.isArray ())
00854         v = func_gcd_helper (v, calc);
00855       res = calc->gcd (res, v);
00856     }
00857   return res;
00858 }
00859 
00860 // Function: GCD
00861 Value func_gcd (valVector args, ValueCalc *calc, FuncExtra *)
00862 {
00863   Value result = 0;
00864   for (unsigned int i = 0; i < args.count(); ++i)
00865     if (args[i].isArray())
00866       result = calc->gcd (result, func_gcd_helper (args[i], calc));
00867     else
00868       result = calc->gcd (result, args[i]);
00869   return result;
00870 }
00871 
00872 static Value func_lcm_helper(const Value &val, ValueCalc *calc)
00873 {
00874   Value res = 0;
00875   if (!val.isArray ())
00876     return val;
00877   for (unsigned int row = 0; row < val.rows(); ++row)
00878     for (unsigned int col = 0; col < val.columns(); ++col)
00879     {
00880       Value v = val.element (col, row);
00881       if (v.isArray ())
00882         v = func_lcm_helper (v, calc);
00883       res = calc->lcm (res, v);
00884     }
00885   return res;
00886 }
00887 
00888 // Function: lcm
00889 Value func_lcm (valVector args, ValueCalc *calc, FuncExtra *)
00890 {
00891   Value result = 0;
00892   for (unsigned int i = 0; i < args.count(); ++i)
00893     if (args[i].isArray())
00894       result = calc->lcm (result, func_lcm_helper (args[i], calc));
00895     else
00896       result = calc->lcm (result, args[i]);
00897   return result;
00898 }
00899 
00900 Value determinant (ValueCalc *calc, Value matrix)
00901 {
00902   // this is a --- SLOOOW --- recursive function
00903   // using this for something bigger than 10x10 or so = suicide :P
00904   // but I'm too lazy to adjust gnumeric's code - remains as a TODO then
00905   // as a note, gnumeric uses LUP decomposition to compute this
00906 
00907   // take first row, generate smaller matrices, recursion, multiply
00908   Value res = 0.0;
00909   int n = matrix.columns();
00910   if (n == 1) return matrix.element (0, 0);
00911   if (n == 2) return calc->sub (
00912       calc->mul (matrix.element (1,1), matrix.element (0,0)),
00913       calc->mul (matrix.element (1,0), matrix.element (0,1)));
00914 
00915   // n >= 3
00916   for (int i = 0; i < n; ++i) {
00917     Value smaller (n-1, n-1);
00918     int col = 0;
00919     for (int c = 0; c < n; ++c)
00920       if (c != i) {
00921         // copy column c to column col in new matrix
00922         for (int r = 1; r < n; r++)
00923           smaller.setElement (col, r-1, matrix.element (c, r));
00924         col++;
00925       }
00926     Value minor = determinant (calc, smaller);
00927     if (i % 2 == 1) minor = calc->mul (minor, -1);
00928     res = calc->add (res, calc->mul (minor, matrix.element (i, 0)));
00929   }
00930   return res;
00931 }
00932 
00933 // Function: mdeterm
00934 Value func_mdeterm (valVector args, ValueCalc *calc, FuncExtra *)
00935 {
00936   Value m = args[0];
00937   unsigned r = m.rows ();
00938   unsigned c = m.columns ();
00939   if (r != c)   // must be a square matrix
00940     return Value::errorVALUE();
00941 
00942   return determinant (calc, args[0]);
00943 }
00944 
00945 // Function: mmult
00946 Value func_mmult (valVector args, ValueCalc *calc, FuncExtra *)
00947 {
00948   Value m1 = args[0];
00949   Value m2 = args[1];
00950   unsigned r1 = m1.rows ();
00951   unsigned c1 = m1.columns ();
00952   unsigned r2 = m2.rows ();
00953   unsigned c2 = m2.columns ();
00954   if (c1 != r2)  // row/column counts must match
00955     return Value::errorVALUE();
00956 
00957   // create the resulting matrix
00958   Value res (c2, r1);
00959 
00960   // perform the multiplication - O(n^3) algorithm
00961   for (uint row = 0; row < r1; ++row)
00962     for (uint col = 0; col < c2; ++col) {
00963       Value val = 0.0;
00964       for (uint pos = 0; pos < c1; ++pos)
00965         val = calc->add (val,
00966             calc->mul (m1.element (pos, row), m2.element (col, pos)));
00967       res.setElement (col, row, val);
00968     }
00969   return res;
00970 }
00971 
00972 // Function: SUBTOTAL
00973 // This function requires access to the Sheet and so on, because
00974 // it needs to check whether cells contain the SUBTOTAL formula or not ...
00975 // Cells containing a SUBTOTAL formula must be ignored.
00976 Value func_subtotal (valVector args, ValueCalc *calc, FuncExtra *e)
00977 {
00978   int function = calc->conv()->asInteger (args[0]).asInteger();
00979   Value range = args[1];
00980   int r1 = -1, c1 = -1, r2 = -1, c2 = -1;
00981   if (e) {
00982     r1 = e->ranges[1].row1;
00983     c1 = e->ranges[1].col1;
00984     r2 = e->ranges[1].row2;
00985     c2 = e->ranges[1].col2;
00986   }
00987 
00988   // if we have a range, run through it, and put an empty value to the place
00989   // of all occurences of the SUBTOTAL function
00990   Value empty;
00991   if ((r1 > 0) && (c1 > 0) && (r2 > 0) && (c2 > 0)) {
00992     for (int r = r1; r <= r2; ++r)
00993       for (int c = c1; c <= c2; ++c) {
00994         Cell *cell = e->sheet->cellAt (c, r);
00995         if (cell->isDefault())
00996           continue;
00997         if (cell->isFormula() && cell->text().find ("SUBTOTAL", 0, false) != -1)
00998           // cell contains the word SUBTOTAL - replace value with empty
00999           range.setElement (c-c1, r-r1, empty);
01000       }
01001   }
01002 
01003   // Good. Now we can execute the necessary function on the range.
01004   Value res;
01005   Function *f;
01006   valVector a;
01007   switch (function) {
01008    case 1: // Average
01009     res = calc->avg (range, false);
01010     break;
01011    case 2: // Count
01012     res = calc->count (range, false);
01013     break;
01014    case 3: // CountA
01015     res = calc->count (range);
01016     break;
01017    case 4: // MAX
01018     res = calc->max (range, false);
01019     break;
01020    case 5: // Min
01021     res = calc->min (range, false);
01022     break;
01023    case 6: // Product
01024     res = calc->product (range, 0.0, false);
01025     break;
01026    case 7: // StDev
01027     res = calc->stddev (range, false);
01028     break;
01029    case 8: // StDevP
01030     res = calc->stddevP (range, false);
01031     break;
01032    case 9: // Sum
01033     res = calc->sum (range, false);
01034     break;
01035    case 10: // Var
01036     f = FunctionRepository::self()->function ("VAR");
01037     if (!f) return Value::errorVALUE();
01038     a.reserve (1);
01039     a[0] = range;
01040     res = f->exec (a, calc, 0);
01041     break;
01042    case 11: // VarP
01043     f = FunctionRepository::self()->function ("VARP");
01044     if (!f) return Value::errorVALUE();
01045     a.reserve (1);
01046     a[0] = range;
01047     res = f->exec (a, calc, 0);
01048     break;
01049    default:
01050     return Value::errorVALUE();
01051   }
01052   return res;
01053 }
01054 
01055 /*
01056 Commented out.
01057 Absolutely no idea what this thing is supposed to do.
01058 To anyone who would enable this code: it still uses koscript calls - you need
01059 to convert it to the new style prior to uncommenting.
01060 
01061 // Function: MULTIPLEOPERATIONS
01062 Value func_multipleOP (valVector args, ValueCalc *calc, FuncExtra *)
01063 {
01064   if (gCell)
01065   {
01066     context.setValue( new KSValue( ((Interpreter *) context.interpreter() )->cell()->value().asFloat() ) );
01067     return true;
01068   }
01069 
01070   gCell = ((Interpreter *) context.interpreter() )->cell();
01071 
01072   QValueList<KSValue::Ptr>& args = context.value()->listValue();
01073   QValueList<KSValue::Ptr>& extra = context.extraData()->listValue();
01074 
01075   if ( !KSUtil::checkArgumentsCount( context, 5, "MULTIPLEOPERATIONS", true ) )
01076   {
01077     gCell = 0;
01078     return false;
01079   }
01080 
01081   // 0: cell must contain formula with double/int result
01082   // 0, 1, 2, 3, 4: must contain integer/double
01083   for (int i = 0; i < 5; ++i)
01084   {
01085     if ( !KSUtil::checkType( context, args[i], KSValue::DoubleType, true ) )
01086     {
01087       gCell = 0;
01088       return false;
01089     }
01090   }
01091 
01092   //  ((Interpreter *) context.interpreter() )->document()->emitBeginOperation();
01093 
01094   double oldCol = args[1]->doubleValue();
01095   double oldRow = args[3]->doubleValue();
01096   kdDebug() << "Old values: Col: " << oldCol << ", Row: " << oldRow << endl;
01097 
01098   Cell * cell;
01099   Sheet * sheet = ((Interpreter *) context.interpreter() )->sheet();
01100 
01101   Point point( extra[1]->stringValue() );
01102   Point point2( extra[3]->stringValue() );
01103   Point point3( extra[0]->stringValue() );
01104 
01105   if ( ( args[1]->doubleValue() != args[2]->doubleValue() )
01106        || ( args[3]->doubleValue() != args[4]->doubleValue() ) )
01107   {
01108     cell = sheet->cellAt( point.pos.x(), point.pos.y() );
01109     cell->setValue( args[2]->doubleValue() );
01110     kdDebug() << "Setting value " << args[2]->doubleValue() << " on cell " << point.pos.x()
01111               << ", " << point.pos.y() << endl;
01112 
01113     cell = sheet->cellAt( point2.pos.x(), point.pos.y() );
01114     cell->setValue( args[4]->doubleValue() );
01115     kdDebug() << "Setting value " << args[4]->doubleValue() << " on cell " << point2.pos.x()
01116               << ", " << point2.pos.y() << endl;
01117   }
01118 
01119   Cell * cell1 = sheet->cellAt( point3.pos.x(), point3.pos.y() );
01120   cell1->calc( false );
01121 
01122   double d = cell1->value().asFloat();
01123   kdDebug() << "Cell: " << point3.pos.x() << "; " << point3.pos.y() << " with value "
01124             << d << endl;
01125 
01126   kdDebug() << "Resetting old values" << endl;
01127 
01128   cell = sheet->cellAt( point.pos.x(), point.pos.y() );
01129   cell->setValue( oldCol );
01130 
01131   cell = sheet->cellAt( point2.pos.x(), point2.pos.y() );
01132   cell->setValue( oldRow );
01133 
01134   cell1->calc( false );
01135 
01136   // ((Interpreter *) context.interpreter() )->document()->emitEndOperation();
01137 
01138   context.setValue( new KSValue( (double) d ) );
01139 
01140   gCell = 0;
01141   return true;
01142 }
01143 
01144 */
KDE Home | KDE Accessibility Home | Description of Access Keys