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 (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
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 const Value value = calc->roundUp (args[0], 0);
00763 return calc->isZero( calc->mod(value, 2) ) ? value : calc->add(value, 1);
00764 }
00765
00766
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
00781 Value func_count (valVector args, ValueCalc *calc, FuncExtra *)
00782 {
00783 return calc->count (args, false);
00784 }
00785
00786
00787 Value func_counta (valVector args, ValueCalc *calc, FuncExtra *)
00788 {
00789 return calc->count (args);
00790 }
00791
00792
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
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
00823 Value func_fib (valVector args, ValueCalc *calc, FuncExtra *)
00824 {
00825
00826
00827
00828
00829
00830
00831
00832
00833 Value n = args[0];
00834 Value s = calc->sqrt (5.0);
00835
00836 Value u1 = calc->pow (calc->div (calc->add (1, s), 2), n);
00837
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
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
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
00903
00904
00905
00906
00907
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
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
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
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)
00940 return Value::errorVALUE();
00941
00942 return determinant (calc, args[0]);
00943 }
00944
00945
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)
00955 return Value::errorVALUE();
00956
00957
00958 Value res (c2, r1);
00959
00960
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
00973
00974
00975
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
00989
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
00999 range.setElement (c-c1, r-r1, empty);
01000 }
01001 }
01002
01003
01004 Value res;
01005 Function *f;
01006 valVector a;
01007 switch (function) {
01008 case 1:
01009 res = calc->avg (range, false);
01010 break;
01011 case 2:
01012 res = calc->count (range, false);
01013 break;
01014 case 3:
01015 res = calc->count (range);
01016 break;
01017 case 4:
01018 res = calc->max (range, false);
01019 break;
01020 case 5:
01021 res = calc->min (range, false);
01022 break;
01023 case 6:
01024 res = calc->product (range, 0.0, false);
01025 break;
01026 case 7:
01027 res = calc->stddev (range, false);
01028 break;
01029 case 8:
01030 res = calc->stddevP (range, false);
01031 break;
01032 case 9:
01033 res = calc->sum (range, false);
01034 break;
01035 case 10:
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:
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
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
01143
01144