00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024 #include <kdebug.h>
00025 #include <klocale.h>
00026
00027 #include "functions.h"
00028 #include "valuecalc.h"
00029 #include "valueconverter.h"
00030
00031
00032 #include "kspread_cell.h"
00033 #include "kspread_sheet.h"
00034
00035
00036 #include <math.h>
00037
00038 using namespace KSpread;
00039
00040
00041
00042
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
00106
00107
00108 void RegisterMathFunctions()
00109 {
00110 FunctionRepository* repo = FunctionRepository::self();
00111 Function *f;
00112
00113
00114
00115
00116
00117
00118
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);
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);
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 (2);
00204 repo->add (f);
00205 f = new Function ("ROUNDDOWN", func_rounddown);
00206 f->setParamCount (2);
00207 repo->add (f);
00208 f = new Function ("ROUNDUP", func_roundup);
00209 f->setParamCount (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
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);
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);
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
00314 Value func_sqrt (valVector args, ValueCalc *calc, FuncExtra *)
00315 {
00316 return calc->sqrt (args[0]);
00317 }
00318
00319
00320 Value func_sqrtpi (valVector args, ValueCalc *calc, FuncExtra *)
00321 {
00322
00323 return calc->sqrt (calc->mul (args[0], calc->pi()));
00324 }
00325
00326
00327 Value func_rootn (valVector args, ValueCalc *calc, FuncExtra *)
00328 {
00329 return calc->pow (args[0], calc->div (1, args[1]));
00330 }
00331
00332
00333 Value func_cur (valVector args, ValueCalc *calc, FuncExtra *)
00334 {
00335 return calc->pow (args[0], 1.0/3.0);
00336 }
00337
00338
00339 Value func_abs (valVector args, ValueCalc *calc, FuncExtra *)
00340 {
00341 return calc->abs (args[0]);
00342 }
00343
00344
00345 Value func_exp (valVector args, ValueCalc *calc, FuncExtra *)
00346 {
00347 return calc->exp (args[0]);
00348 }
00349
00350
00351 Value func_ceil (valVector args, ValueCalc *calc, FuncExtra *)
00352 {
00353 return calc->roundUp (args[0], 0);
00354 }
00355
00356
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
00383 Value func_floor (valVector args, ValueCalc *calc, FuncExtra *)
00384 {
00385 return calc->roundDown (args[0], 0);
00386 }
00387
00388
00389 Value func_ln (valVector args, ValueCalc *calc, FuncExtra *)
00390 {
00391 return calc->ln (args[0]);
00392 }
00393
00394
00395 Value func_logn (valVector args, ValueCalc *calc, FuncExtra *)
00396 {
00397 return calc->log (args[0], args[1]);
00398 }
00399
00400
00401 Value func_log2 (valVector args, ValueCalc *calc, FuncExtra *)
00402 {
00403 return calc->log (args[0], 2.0);
00404 }
00405
00406
00407 Value func_log10 (valVector args, ValueCalc *calc, FuncExtra *)
00408 {
00409 return calc->log (args[0]);
00410 }
00411
00412
00413 Value func_sum (valVector args, ValueCalc *calc, FuncExtra *)
00414 {
00415 return calc->sum (args, false);
00416 }
00417
00418
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
00439 Value func_product (valVector args, ValueCalc *calc, FuncExtra *)
00440 {
00441 return calc->product (args, 0.0);
00442 }
00443
00444
00445 Value func_kproduct (valVector args, ValueCalc *calc, FuncExtra *)
00446 {
00447 return calc->product (args, 1.0);
00448 }
00449
00450
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
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
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
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
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
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
00500 Value func_int (valVector args, ValueCalc *calc, FuncExtra *)
00501 {
00502 return calc->conv()->asInteger (args[0]);
00503 }
00504
00505
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
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
00523 return calc->mul (calc->mul (args[0], -1), calc->random());
00524 }
00525
00526 Value func_randbinom (valVector args, ValueCalc *calc, FuncExtra *)
00527 {
00528
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
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
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
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
00597
00598
00599 Value x1, x2, w;
00600 do {
00601
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));
00608
00609
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);
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
00623 Value x = calc->exp (calc->mul (-1, args[0]));
00624 Value r = calc->random ();
00625 Value t = x;
00626 int i = 0;
00627
00628 while (calc->greater (r, t)) {
00629 x = calc->mul (x, calc->div (args[0], i + 1));
00630 t = calc->add (t, x);
00631 i++;
00632 }
00633
00634 return Value (i);
00635 }
00636
00637
00638 Value func_rand (valVector, ValueCalc *calc, FuncExtra *)
00639 {
00640 return calc->random ();
00641 }
00642
00643
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
00656 Value func_pow (valVector args, ValueCalc *calc, FuncExtra *)
00657 {
00658 return calc->pow (args[0], args[1]);
00659 }
00660
00661
00662 Value func_mod (valVector args, ValueCalc *calc, FuncExtra *)
00663 {
00664 return calc->mod (args[0], args[1]);
00665 }
00666
00667
00668 Value func_fact (valVector args, ValueCalc *calc, FuncExtra *)
00669 {
00670 return calc->fact (args[0]);
00671 }
00672
00673
00674 Value func_factdouble (valVector args, ValueCalc *calc, FuncExtra *)
00675 {
00676 return calc->factDouble (args[0]);
00677 }
00678
00679
00680 Value func_multinomial (valVector args, ValueCalc *calc, FuncExtra *)
00681 {
00682
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
00693 Value func_sign (valVector args, ValueCalc *calc, FuncExtra *)
00694 {
00695 return Value (calc->sign (args[0]));
00696 }
00697
00698
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
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
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)))
00729 result = calc->add (result, m);
00730 result = calc->mul (result, sign);
00731
00732 return result;
00733 }
00734
00735
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
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
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
00760 Value func_even (valVector args, ValueCalc *calc, FuncExtra *)
00761 {
00762 return calc->isEven (args[0]);
00763 }
00764
00765
00766 Value func_odd (valVector args, ValueCalc *calc, FuncExtra *)
00767 {
00768 return (!calc->isEven (args[0]));
00769 }
00770
00771 Value func_trunc (valVector args, ValueCalc *calc, FuncExtra *)
00772 {
00773 if (args.count() == 1)
00774 return calc->roundDown (args[0]);
00775 return calc->roundDown (args[0], args[1]);
00776 }
00777
00778
00779 Value func_count (valVector args, ValueCalc *calc, FuncExtra *)
00780 {
00781 return calc->count (args, false);
00782 }
00783
00784
00785 Value func_counta (valVector args, ValueCalc *calc, FuncExtra *)
00786 {
00787 return calc->count (args);
00788 }
00789
00790
00791 Value func_countblank (valVector args, ValueCalc *, FuncExtra *)
00792 {
00793 int cnt = 0;
00794 for (unsigned int i = 0; i < args.count(); ++i)
00795 if (args[i].isArray()) {
00796 int rows = args[i].rows();
00797 int cols = args[i].columns();
00798 for (int r = 0; r < rows; ++r)
00799 for (int c = 0; c < cols; ++c)
00800 if (args[i].element (c, r).isEmpty())
00801 cnt++;
00802 } else
00803 if (args[i].isEmpty())
00804 cnt++;
00805 return Value (cnt);
00806 }
00807
00808
00809 Value func_countif (valVector args, ValueCalc *calc, FuncExtra *)
00810 {
00811 Value range = args[0];
00812 QString condition = calc->conv()->asString (args[1]).asString();
00813
00814 Condition cond;
00815 calc->getCond (cond, condition);
00816
00817 return calc->countIf (range, cond);
00818 }
00819
00820
00821 Value func_fib (valVector args, ValueCalc *calc, FuncExtra *)
00822 {
00823
00824
00825
00826
00827
00828
00829
00830
00831 Value n = args[0];
00832 Value s = calc->sqrt (5.0);
00833
00834 Value u1 = calc->pow (calc->div (calc->add (1, s), 2), n);
00835
00836 Value u2 = calc->pow (calc->div (calc->sub (1, s), 2), n);
00837
00838 Value result = calc->div (calc->sub (u1, u2), s);
00839 return result;
00840 }
00841
00842 static Value func_gcd_helper(const Value &val, ValueCalc *calc)
00843 {
00844 Value res = 0;
00845 if (!val.isArray ())
00846 return val;
00847 for (unsigned int row = 0; row < val.rows(); ++row)
00848 for (unsigned int col = 0; col < val.columns(); ++col)
00849 {
00850 Value v = val.element (col, row);
00851 if (v.isArray ())
00852 v = func_gcd_helper (v, calc);
00853 res = calc->gcd (res, v);
00854 }
00855 return res;
00856 }
00857
00858
00859 Value func_gcd (valVector args, ValueCalc *calc, FuncExtra *)
00860 {
00861 Value result = 0;
00862 for (unsigned int i = 0; i < args.count(); ++i)
00863 if (args[i].isArray())
00864 result = calc->gcd (result, func_gcd_helper (args[i], calc));
00865 else
00866 result = calc->gcd (result, args[i]);
00867 return result;
00868 }
00869
00870 static Value func_lcm_helper(const Value &val, ValueCalc *calc)
00871 {
00872 Value res = 0;
00873 if (!val.isArray ())
00874 return val;
00875 for (unsigned int row = 0; row < val.rows(); ++row)
00876 for (unsigned int col = 0; col < val.columns(); ++col)
00877 {
00878 Value v = val.element (col, row);
00879 if (v.isArray ())
00880 v = func_lcm_helper (v, calc);
00881 res = calc->lcm (res, v);
00882 }
00883 return res;
00884 }
00885
00886
00887 Value func_lcm (valVector args, ValueCalc *calc, FuncExtra *)
00888 {
00889 Value result = 0;
00890 for (unsigned int i = 0; i < args.count(); ++i)
00891 if (args[i].isArray())
00892 result = calc->lcm (result, func_lcm_helper (args[i], calc));
00893 else
00894 result = calc->lcm (result, args[i]);
00895 return result;
00896 }
00897
00898 Value determinant (ValueCalc *calc, Value matrix)
00899 {
00900
00901
00902
00903
00904
00905
00906 Value res = 0.0;
00907 int n = matrix.columns();
00908 if (n == 1) return matrix.element (0, 0);
00909 if (n == 2) return calc->sub (
00910 calc->mul (matrix.element (1,1), matrix.element (0,0)),
00911 calc->mul (matrix.element (1,0), matrix.element (0,1)));
00912
00913
00914 for (int i = 0; i < n; ++i) {
00915 Value smaller (n-1, n-1);
00916 int col = 0;
00917 for (int c = 0; c < n; ++c)
00918 if (c != i) {
00919
00920 for (int r = 1; r < n; r++)
00921 smaller.setElement (col, r-1, matrix.element (c, r));
00922 col++;
00923 }
00924 Value minor = determinant (calc, smaller);
00925 if (i % 2 == 1) minor = calc->mul (minor, -1);
00926 res = calc->add (res, calc->mul (minor, matrix.element (i, 0)));
00927 }
00928 return res;
00929 }
00930
00931
00932 Value func_mdeterm (valVector args, ValueCalc *calc, FuncExtra *)
00933 {
00934 Value m = args[0];
00935 unsigned r = m.rows ();
00936 unsigned c = m.columns ();
00937 if (r != c)
00938 return Value::errorVALUE();
00939
00940 return determinant (calc, args[0]);
00941 }
00942
00943
00944 Value func_mmult (valVector args, ValueCalc *calc, FuncExtra *)
00945 {
00946 Value m1 = args[0];
00947 Value m2 = args[1];
00948 unsigned r1 = m1.rows ();
00949 unsigned c1 = m1.columns ();
00950 unsigned r2 = m2.rows ();
00951 unsigned c2 = m2.columns ();
00952 if (c1 != r2)
00953 return Value::errorVALUE();
00954
00955
00956 Value res (c2, r1);
00957
00958
00959 for (uint row = 0; row < r1; ++row)
00960 for (uint col = 0; col < c2; ++col) {
00961 Value val = 0.0;
00962 for (uint pos = 0; pos < c1; ++pos)
00963 val = calc->add (val,
00964 calc->mul (m1.element (pos, row), m2.element (col, pos)));
00965 res.setElement (col, row, val);
00966 }
00967 return res;
00968 }
00969
00970
00971
00972
00973
00974 Value func_subtotal (valVector args, ValueCalc *calc, FuncExtra *e)
00975 {
00976 int function = calc->conv()->asInteger (args[0]).asInteger();
00977 Value range = args[1];
00978 int r1 = -1, c1 = -1, r2 = -1, c2 = -1;
00979 if (e) {
00980 r1 = e->ranges[1].row1;
00981 c1 = e->ranges[1].col1;
00982 r2 = e->ranges[1].row2;
00983 c2 = e->ranges[1].col2;
00984 }
00985
00986
00987
00988 Value empty;
00989 if ((r1 > 0) && (c1 > 0) && (r2 > 0) && (c2 > 0)) {
00990 for (int r = r1; r <= r2; ++r)
00991 for (int c = c1; c <= c2; ++c) {
00992 Cell *cell = e->sheet->cellAt (c, r);
00993 if (cell->isDefault())
00994 continue;
00995 if (cell->isFormula() && cell->text().find ("SUBTOTAL", 0, false) != -1)
00996
00997 range.setElement (c-c1, r-r1, empty);
00998 }
00999 }
01000
01001
01002 Value res;
01003 Function *f;
01004 valVector a;
01005 switch (function) {
01006 case 1:
01007 res = calc->avg (range, false);
01008 break;
01009 case 2:
01010 res = calc->count (range, false);
01011 break;
01012 case 3:
01013 res = calc->count (range);
01014 break;
01015 case 4:
01016 res = calc->max (range, false);
01017 break;
01018 case 5:
01019 res = calc->min (range, false);
01020 break;
01021 case 6:
01022 res = calc->product (range, 0.0, false);
01023 break;
01024 case 7:
01025 res = calc->stddev (range, false);
01026 break;
01027 case 8:
01028 res = calc->stddevP (range, false);
01029 break;
01030 case 9:
01031 res = calc->sum (range, false);
01032 break;
01033 case 10:
01034 f = FunctionRepository::self()->function ("VAR");
01035 if (!f) return Value::errorVALUE();
01036 a.reserve (1);
01037 a[0] = range;
01038 res = f->exec (a, calc, 0);
01039 break;
01040 case 11:
01041 f = FunctionRepository::self()->function ("VARP");
01042 if (!f) return Value::errorVALUE();
01043 a.reserve (1);
01044 a[0] = range;
01045 res = f->exec (a, calc, 0);
01046 break;
01047 default:
01048 return Value::errorVALUE();
01049 }
01050 return res;
01051 }
01052
01053
01054
01055
01056
01057
01058
01059
01060
01061
01062
01063
01064
01065
01066
01067
01068
01069
01070
01071
01072
01073
01074
01075
01076
01077
01078
01079
01080
01081
01082
01083
01084
01085
01086
01087
01088
01089
01090
01091
01092
01093
01094
01095
01096
01097
01098
01099
01100
01101
01102
01103
01104
01105
01106
01107
01108
01109
01110
01111
01112
01113
01114
01115
01116
01117
01118
01119
01120
01121
01122
01123
01124
01125
01126
01127
01128
01129
01130
01131
01132
01133
01134
01135
01136
01137
01138
01139
01140
01141
01142