kspread_functions_math.cpp

/* This file is part of the KDE project
   Copyright (C) 1998-2002 The KSpread Team
                           www.koffice.org/kspread
   Copyright (C) 2005 Tomas Mecir <mecirt@gmail.com>
 
   This library is free software; you can redistribute it and/or
   modify it under the terms of the GNU Library General Public
   License as published by the Free Software Foundation; either
   version 2 of the License.
 
   This library is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
   Library General Public License for more details.
 
   You should have received a copy of the GNU Library General Public License
   along with this library; see the file COPYING.LIB.  If not, write to
   the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
 * Boston, MA 02110-1301, USA.
*/
 
// built-in math functions
 
#include <kdebug.h>
#include <tdelocale.h>
 
#include "functions.h"
#include "valuecalc.h"
#include "valueconverter.h"
 
// these two are needed for SUBTOTAL:
#include "kspread_cell.h"
#include "kspread_sheet.h"
 
// needed for RANDBINOM and so
#include <math.h>
 
using namespace KSpread;
 
// RANDBINOM and RANDNEGBINOM won't support arbitrary precision
 
// prototypes
Value func_abs (valVector args, ValueCalc *calc, FuncExtra *);
Value func_ceil (valVector args, ValueCalc *calc, FuncExtra *);
Value func_ceiling (valVector args, ValueCalc *calc, FuncExtra *);
Value func_count (valVector args, ValueCalc *calc, FuncExtra *);
Value func_counta (valVector args, ValueCalc *calc, FuncExtra *);
Value func_countblank (valVector args, ValueCalc *calc, FuncExtra *);
Value func_countif (valVector args, ValueCalc *calc, FuncExtra *);
Value func_cur (valVector args, ValueCalc *calc, FuncExtra *);
Value func_div (valVector args, ValueCalc *calc, FuncExtra *);
Value func_eps (valVector args, ValueCalc *calc, FuncExtra *);
Value func_even (valVector args, ValueCalc *calc, FuncExtra *);
Value func_exp (valVector args, ValueCalc *calc, FuncExtra *);
Value func_fact (valVector args, ValueCalc *calc, FuncExtra *);
Value func_factdouble (valVector args, ValueCalc *calc, FuncExtra *);
Value func_fib (valVector args, ValueCalc *calc, FuncExtra *);
Value func_floor (valVector args, ValueCalc *calc, FuncExtra *);
Value func_gcd (valVector args, ValueCalc *calc, FuncExtra *);
Value func_int (valVector args, ValueCalc *calc, FuncExtra *);
Value func_inv (valVector args, ValueCalc *calc, FuncExtra *);
Value func_kproduct (valVector args, ValueCalc *calc, FuncExtra *);
Value func_lcm (valVector args, ValueCalc *calc, FuncExtra *);
Value func_ln (valVector args, ValueCalc *calc, FuncExtra *);
Value func_log2 (valVector args, ValueCalc *calc, FuncExtra *);
Value func_log10 (valVector args, ValueCalc *calc, FuncExtra *);
Value func_logn (valVector args, ValueCalc *calc, FuncExtra *);
Value func_max (valVector args, ValueCalc *calc, FuncExtra *);
Value func_maxa (valVector args, ValueCalc *calc, FuncExtra *);
Value func_mdeterm (valVector args, ValueCalc *calc, FuncExtra *);
Value func_min (valVector args, ValueCalc *calc, FuncExtra *);
Value func_mina (valVector args, ValueCalc *calc, FuncExtra *);
Value func_mmult (valVector args, ValueCalc *calc, FuncExtra *);
Value func_mod (valVector args, ValueCalc *calc, FuncExtra *);
Value func_mround (valVector args, ValueCalc *calc, FuncExtra *);
Value func_mult (valVector args, ValueCalc *calc, FuncExtra *);
Value func_multinomial (valVector args, ValueCalc *calc, FuncExtra *);
Value func_odd (valVector args, ValueCalc *calc, FuncExtra *);
Value func_pow (valVector args, ValueCalc *calc, FuncExtra *);
Value func_quotient (valVector args, ValueCalc *calc, FuncExtra *);
Value func_product (valVector args, ValueCalc *calc, FuncExtra *);
Value func_rand (valVector args, ValueCalc *calc, FuncExtra *);
Value func_randbetween (valVector args, ValueCalc *calc, FuncExtra *);
Value func_randbernoulli (valVector args, ValueCalc *calc, FuncExtra *);
Value func_randbinom (valVector args, ValueCalc *calc, FuncExtra *);
Value func_randexp (valVector args, ValueCalc *calc, FuncExtra *);
Value func_randnegbinom (valVector args, ValueCalc *calc, FuncExtra *);
Value func_randnorm (valVector args, ValueCalc *calc, FuncExtra *);
Value func_randpoisson (valVector args, ValueCalc *calc, FuncExtra *);
Value func_rootn (valVector args, ValueCalc *calc, FuncExtra *);
Value func_round (valVector args, ValueCalc *calc, FuncExtra *);
Value func_rounddown (valVector args, ValueCalc *calc, FuncExtra *);
Value func_roundup (valVector args, ValueCalc *calc, FuncExtra *);
Value func_sign (valVector args, ValueCalc *calc, FuncExtra *);
Value func_sqrt (valVector args, ValueCalc *calc, FuncExtra *);
Value func_sqrtpi (valVector args, ValueCalc *calc, FuncExtra *);
Value func_subtotal (valVector args, ValueCalc *calc, FuncExtra *);
Value func_sum (valVector args, ValueCalc *calc, FuncExtra *);
Value func_suma (valVector args, ValueCalc *calc, FuncExtra *);
Value func_sumif (valVector args, ValueCalc *calc, FuncExtra *);
Value func_sumsq (valVector args, ValueCalc *calc, FuncExtra *);
Value func_trunc (valVector args, ValueCalc *calc, FuncExtra *);
 
 
// Value func_multipleOP (valVector args, ValueCalc *calc, FuncExtra *);
 
// registers all math functions
void RegisterMathFunctions()
{
  FunctionRepository* repo = FunctionRepository::self();
  Function *f;
 
/*
  f = new Function ("MULTIPLEOPERATIONS", func_multipleOP);
  repo->add (f);
*/
 
  // functions that don't take array parameters
  f = new Function ("ABS",           func_abs);
  repo->add (f);
  f = new Function ("CEIL",          func_ceil);
  repo->add (f);
  f = new Function ("CEILING",       func_ceiling);
  f->setParamCount (1, 2);
  repo->add (f);
  f = new Function ("CUR",           func_cur);
  repo->add (f);
  f = new Function ("EPS",           func_eps);
  f->setParamCount (0);
  repo->add (f);
  f = new Function ("EVEN",          func_even);
  repo->add (f);
  f = new Function ("EXP",           func_exp);
  repo->add (f);
  f = new Function ("FACT",          func_fact);
  repo->add (f);
  f = new Function ("FACTDOUBLE",    func_factdouble);
  repo->add (f);
  f = new Function ("FIB",           func_fib); // KSpread-specific, like Quattro-Pro's FIB
  repo->add (f);
  f = new Function ("FLOOR",         func_floor);
  repo->add (f);
  f = new Function ("INT",           func_int);
  repo->add (f);
  f = new Function ("INV",           func_inv);
  repo->add (f);
  f = new Function ("LN",            func_ln);
  repo->add (f);
  f = new Function ("LOG",           func_log10);
  repo->add (f);
  f = new Function ("LOG2",          func_log2);
  repo->add (f);
  f = new Function ("LOG10",         func_log10);   // same as LOG
  repo->add (f);
  f = new Function ("LOGN",          func_logn);
  f->setParamCount (2);
  repo->add (f);
  f = new Function ("MOD",           func_mod);
  f->setParamCount (2);
  repo->add (f);
  f = new Function ("MROUND",        func_mround);
  f->setParamCount (2);
  repo->add (f);
  f = new Function ("MULTINOMIAL",   func_multinomial);
  f->setParamCount (1, -1);
  repo->add (f);
  f = new Function ("ODD",           func_odd);
  repo->add (f);
  f = new Function ("POW",         func_pow);
  f->setParamCount (2);
  repo->add (f);
  f = new Function ("POWER",         func_pow);
  f->setParamCount (2);
  repo->add (f);
  f = new Function ("QUOTIENT",      func_quotient);
  f->setParamCount (2);
  repo->add (f);
  f = new Function ("RAND",          func_rand);
  f->setParamCount (0);
  repo->add (f);
  f = new Function ("RANDBERNOULLI", func_randbernoulli);
  repo->add (f);
  f = new Function ("RANDBETWEEN",   func_randbetween);
  f->setParamCount (2);
  repo->add (f);
  f = new Function ("RANDBINOM",     func_randbinom);
  f->setParamCount (2);
  repo->add (f);
  f = new Function ("RANDEXP",       func_randexp);
  repo->add (f);
  f = new Function ("RANDNEGBINOM",  func_randnegbinom);
  f->setParamCount (2);
  repo->add (f);
  f = new Function ("RANDNORM",      func_randnorm);
  f->setParamCount (2);
  repo->add (f);
  f = new Function ("RANDPOISSON",   func_randpoisson);
  repo->add (f);
  f = new Function ("ROOTN",         func_rootn);
  f->setParamCount (2);
  repo->add (f);
  f = new Function ("ROUND",         func_round);
  f->setParamCount (1, 2);
  repo->add (f);
  f = new Function ("ROUNDDOWN",     func_rounddown);
  f->setParamCount (1, 2);
  repo->add (f);
  f = new Function ("ROUNDUP",       func_roundup);
  f->setParamCount (1, 2);
  repo->add (f);
  f = new Function ("SIGN",          func_sign);
  repo->add (f);
  f = new Function ("SQRT",          func_sqrt);
  repo->add (f);
  f = new Function ("SQRTPI",        func_sqrtpi);
  repo->add (f);
  f = new Function ("TRUNC",         func_trunc);
  f->setParamCount (1, 2);
  repo->add (f);
 
  // functions that operate over arrays
  f = new Function ("COUNT",         func_count);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("COUNTA",        func_counta);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("COUNTBLANK",    func_countblank);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("COUNTIF",       func_countif);
  f->setParamCount (2);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("DIV",           func_div);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("G_PRODUCT",     func_kproduct); // Gnumeric compatibility
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("GCD",           func_gcd);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("KPRODUCT",      func_kproduct);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("LCM",           func_lcm);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("MAX",           func_max);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("MAXA",          func_maxa);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("MDETERM",          func_mdeterm);
  f->setParamCount (1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("MIN",           func_min);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("MINA",          func_mina);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("MMULT",          func_mmult);
  f->setParamCount (2);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("MULTIPLY",      func_product);  // same as PRODUCT
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("PRODUCT",       func_product);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("SUM",           func_sum);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("SUMA",          func_suma);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("SUBTOTAL",      func_subtotal);
  f->setParamCount (2);
  f->setAcceptArray ();
  f->setNeedsExtra (true);
  repo->add (f);
  f = new Function ("SUMIF",         func_sumif);
  f->setParamCount (2, 3);
  f->setAcceptArray ();
  repo->add (f);
  f = new Function ("SUMSQ",         func_sumsq);
  f->setParamCount (1, -1);
  f->setAcceptArray ();
  repo->add (f);
}
 
// Function: SQRT
Value func_sqrt (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->sqrt (args[0]);
}
 
// Function: SQRTPI
Value func_sqrtpi (valVector args, ValueCalc *calc, FuncExtra *)
{
  // sqrt (val * PI)
  return calc->sqrt (calc->mul (args[0], calc->pi()));
}
 
// Function: ROOTN
Value func_rootn (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->pow (args[0], calc->div (1, args[1]));
}
 
// Function: CUR
Value func_cur (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->pow (args[0], 1.0/3.0);
}
 
// Function: ABS
Value func_abs (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->abs (args[0]);
}
 
// Function: exp
Value func_exp (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->exp (args[0]);
}
 
// Function: ceil
Value func_ceil (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->roundUp (args[0], 0);
}
 
// Function: ceiling
Value func_ceiling (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value number = args[0];
  Value res;
  if (args.count() == 2)
    res = args[1];
  else
    res = calc->gequal (number, 0.0) ? 1.0 : -1.0;
 
  if (calc->isZero(res))
    return Value::errorDIV0();
 
  Value d = calc->div (number, res);
  if (calc->greater (0, d))
    return Value::errorVALUE();
 
  Value rud = calc->roundDown (d);
  if (calc->approxEqual (rud, d))
    d = calc->mul (rud, res);
  else
    d = calc->mul (calc->roundUp (d), res);
 
  return d;
}
 
// Function: floor
Value func_floor (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->roundDown (args[0], 0);
}
 
// Function: ln
Value func_ln (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->ln (args[0]);
}
 
// Function: LOGn
Value func_logn (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->log (args[0], args[1]);
}
 
// Function: LOG2
Value func_log2 (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->log (args[0], 2.0);
}
 
// Function: LOG10
Value func_log10 (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->log (args[0]);
}
 
// Function: sum
Value func_sum (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->sum (args, false);
}
 
// Function: suma
Value func_suma (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->sum (args, true);
}
 
Value func_sumif (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value checkRange = args[0];
  TQString condition = calc->conv()->asString (args[1]).asString();
  Value sumRange = checkRange;
  if (args.count() == 3)
    sumRange = args[2];
 
  Condition cond;
  calc->getCond (cond, condition);
 
  return calc->sumIf (sumRange, checkRange, cond);
}
 
// Function: product
Value func_product (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->product (args, 0.0);
}
 
// Function: kproduct
Value func_kproduct (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->product (args, 1.0);
}
 
// Function: DIV
Value func_div (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value val = args[0];
  for (unsigned int i = 1; i < args.count(); ++i)
  {
    val = calc->div (val, args[i]);
    if (val.isError())
      return val;
  }
  return val;
}
 
// Function: SUMSQ
Value func_sumsq (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value res;
  calc->arrayWalk (args, res, calc->awFunc ("sumsq"), 0);
  return res;
}
 
// Function: MAX
Value func_max (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value m = calc->max (args, false);
  return m.isEmpty() ? Value(0.0) : m;
}
 
// Function: MAXA
Value func_maxa (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value m = calc->max (args);
  return m.isEmpty() ? Value(0.0) : m;
}
 
// Function: MIN
Value func_min (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value m = calc->min (args, false);
  return m.isEmpty() ? Value(0.0) : m;
}
 
// Function: MINA
Value func_mina (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value m = calc->min (args);
  return m.isEmpty() ? Value(0.0) : m;
}
 
// Function: INT
Value func_int (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->conv()->asInteger (args[0]);
}
 
// Function: QUOTIENT
Value func_quotient (valVector args, ValueCalc *calc, FuncExtra *)
{
  if (calc->isZero (args[1]))
    return Value::errorDIV0();
  return calc->conv()->asInteger (calc->div (args[0], args[1]));
}
 
 
// Function: eps
Value func_eps (valVector, ValueCalc *calc, FuncExtra *)
{
  return calc->eps ();
}
 
Value func_randexp (valVector args, ValueCalc *calc, FuncExtra *)
{
  // -1 * d * log (random)
  return calc->mul (calc->mul (args[0], -1), calc->random());
}
 
Value func_randbinom (valVector args, ValueCalc *calc, FuncExtra *)
{
  // this function will not support arbitrary precision
 
  double d  = calc->conv()->asFloat (args[0]).asFloat();
  int    tr = calc->conv()->asInteger (args[1]).asInteger();
 
  if ( d < 0 || d > 1 )
    return Value::errorVALUE();
 
  if ( tr < 0 )
    return Value::errorVALUE();
 
  // taken from gnumeric
  double x = pow(1 - d, tr);
  double r = (double) rand() / ( RAND_MAX + 1.0 );
  double t = x;
  int i = 0;
 
  while (r > t)
  {
    x *= (((tr - i) * d) / ((1 + i) * (1 - d)));
    i++;
    t += x;
  }
 
  return Value (i);
}
 
Value func_randnegbinom (valVector args, ValueCalc *calc, FuncExtra *)
{
  // this function will not support arbitrary precision
 
  double d  = calc->conv()->asFloat (args[0]).asFloat();
  int    f = calc->conv()->asInteger (args[1]).asInteger();
 
  if ( d < 0 || d > 1 )
    return Value::errorVALUE();
 
  if ( f < 0 )
    return Value::errorVALUE();
 
 
  // taken from Gnumeric
  double x = pow(d, f);
  double r = (double) rand() / ( RAND_MAX + 1.0 );
  double t = x;
  int i = 0;
 
  while (r > t)
  {
    x *= ( ( ( f + i ) * ( 1 - d ) ) / (1 + i) ) ;
    i++;
    t += x;
  }
 
  return Value (i);
}
 
Value func_randbernoulli (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value rnd = calc->random ();
  return Value (calc->greater (rnd, args[0]) ? 1.0 : 0.0);
}
 
Value func_randnorm (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value mu = args[0];
  Value sigma = args[1];
 
  //using polar form of the Box-Muller transformation
  //refer to http://www.taygeta.com/random/gaussian.html for more info
 
  Value x1, x2, w;
  do {
    // x1,x2 = 2 * random() - 1
    x1 = calc->random (2.0);
    x2 = calc->random (2.0);
    x1 = calc->sub (x1, 1);
    x1 = calc->sub (x2, 1);
    w = calc->add (calc->sqr(x1), calc->sqr (x2));
  } while (calc->gequal (w, 1.0));   // w >= 1.0
 
  //sqrt ((-2.0 * log (w)) / w) :
  w = calc->sqrt (calc->div (calc->mul (-2.0, calc->ln (w)), w));
  Value res = calc->mul (x1, w);
 
  res = calc->add (calc->mul (res, sigma), mu);  // res*sigma + mu
  return res;
}
 
Value func_randpoisson (valVector args, ValueCalc *calc, FuncExtra *)
{
  if (calc->lower (args[0], 0))
    return Value::errorVALUE();
 
  // taken from Gnumeric...
  Value x = calc->exp (calc->mul (-1, args[0]));   // e^(-A)
  Value r = calc->random ();
  Value t = x;
  int i = 0;
 
  while (calc->greater (r, t)) {   // r > t
    x = calc->mul (x, calc->div (args[0], i + 1));  // x *= (A/(i+1))
    t = calc->add (t, x);    //t += x
    i++;
  }
 
  return Value (i);
}
 
// Function: rand
Value func_rand (valVector, ValueCalc *calc, FuncExtra *)
{
  return calc->random ();
}
 
// Function: RANDBETWEEN
Value func_randbetween (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value v1 = args[0];
  Value v2 = args[1];
  if (calc->greater (v2, v1)) {
    v1 = args[1];
    v2 = args[0];
  }
  return calc->add (v1, calc->random (calc->sub (v2, v1)));
}
 
// Function: POW
Value func_pow (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->pow (args[0], args[1]);
}
 
// Function: MOD
Value func_mod (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->mod (args[0], args[1]);
}
 
// Function: fact
Value func_fact (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->fact (args[0]);
}
 
// Function: FACTDOUBLE
Value func_factdouble (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->factDouble (args[0]);
}
 
// Function: MULTINOMIAL
Value func_multinomial (valVector args, ValueCalc *calc, FuncExtra *)
{
  // (a+b+c)! / a!b!c!  (any number of params possible)
  Value num = 0, den = 1;
  for (unsigned int i = 0; i < args.count(); ++i) {
    num = calc->add (num, args[i]);
    den = calc->mul (den, calc->fact (args[i]));
  }
  num = calc->fact (num);
  return calc->div (num, den);
}
 
// Function: sign
Value func_sign (valVector args, ValueCalc *calc, FuncExtra *)
{
  return Value (calc->sign (args[0]));
}
 
// Function: INV
Value func_inv (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->mul (args[0], -1);
}
 
Value func_mround (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value d = args[0];
  Value m = args[1];
 
  // signs must be the same
  if ((calc->greater (d, 0) && calc->lower (m, 0))
      || (calc->lower (d, 0) && calc->greater (m, 0)))
    return Value::errorVALUE();
 
  int sign = 1;
 
  if (calc->lower (d, 0))
  {
    sign = -1;
    d = calc->mul (d, -1);
    m = calc->mul (m, -1);
  }
 
  // from gnumeric:
  Value mod = calc->mod (d, m);
  Value div = calc->sub (d, mod);
 
  Value result = div;
  if (calc->greater (mod, calc->div (m, 2)))  // mod > m/2
    result = calc->add (result, m);     // result += m
  result = calc->mul (result, sign);    // add the sign
 
  return result;
}
 
// Function: ROUNDDOWN
Value func_rounddown (valVector args, ValueCalc *calc, FuncExtra *)
{
  if (args.count() == 2)
    return calc->roundDown (args[0], args[1]);
  return calc->roundDown (args[0], 0);
}
 
// Function: ROUNDUP
Value func_roundup (valVector args, ValueCalc *calc, FuncExtra *)
{
  if (args.count() == 2)
    return calc->roundUp (args[0], args[1]);
  return calc->roundUp (args[0], 0);
}
 
// Function: ROUND
Value func_round (valVector args, ValueCalc *calc, FuncExtra *)
{
  if (args.count() == 2)
    return calc->round (args[0], args[1]);
  return calc->round (args[0], 0);
}
 
// Function: EVEN
Value func_even (valVector args, ValueCalc *calc, FuncExtra *)
{
  const Value value = calc->roundUp (args[0], 0);
  return calc->isZero( calc->mod(value, 2) ) ? value : calc->add(value, 1);
}
 
// Function: ODD
Value func_odd (valVector args, ValueCalc *calc, FuncExtra *)
{
  const Value value = calc->roundUp (args[0], 0);
  return calc->isZero( calc->mod(value, 2) ) ? calc->add(value, 1) : value;
}
 
Value func_trunc (valVector args, ValueCalc *calc, FuncExtra *)
{
  if (args.count() == 1)
    return calc->roundDown (args[0]);
  return calc->roundDown (args[0], args[1]);
}
 
// Function: COUNT
Value func_count (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->count (args, false);
}
 
// Function: COUNTA
Value func_counta (valVector args, ValueCalc *calc, FuncExtra *)
{
  return calc->count (args);
}
 
// Function: COUNTBLANK
Value func_countblank (valVector args, ValueCalc *, FuncExtra *)
{
  int cnt = 0;
  for (unsigned int i = 0; i < args.count(); ++i)
    if (args[i].isArray()) {
      int rows = args[i].rows();
      int cols = args[i].columns();
      for (int r = 0; r < rows; ++r)
        for (int c = 0; c < cols; ++c)
          if (args[i].element (c, r).isEmpty())
            cnt++;
    } else
      if (args[i].isEmpty())
        cnt++;
  return Value (cnt);
}
 
// Function: COUNTIF
Value func_countif (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value range = args[0];
  TQString condition = calc->conv()->asString (args[1]).asString();
 
  Condition cond;
  calc->getCond (cond, condition);
 
  return calc->countIf (range, cond);
}
 
// Function: FIB
Value func_fib (valVector args, ValueCalc *calc, FuncExtra *)
{
/*
Lucas' formula for the nth Fibonacci number F(n) is given by
 
         ((1+sqrt(5))/2)^n - ((1-sqrt(5))/2)^n
  F(n) = ------------------------------------- .
                         sqrt(5)
 
*/
  Value n = args[0];
  Value s = calc->sqrt (5.0);
  // u1 = ((1+sqrt(5))/2)^n
  Value u1 = calc->pow (calc->div (calc->add (1, s), 2), n);
  // u2 = ((1-sqrt(5))/2)^n
  Value u2 = calc->pow (calc->div (calc->sub (1, s), 2), n);
 
  Value result = calc->div (calc->sub (u1, u2), s);
  return result;
}
 
static Value func_gcd_helper(const Value &val, ValueCalc *calc)
{
  Value res = 0;
  if (!val.isArray ())
    return val;
  for (unsigned int row = 0; row < val.rows(); ++row)
    for (unsigned int col = 0; col < val.columns(); ++col)
    {
      Value v = val.element (col, row);
      if (v.isArray ())
        v = func_gcd_helper (v, calc);
      res = calc->gcd (res, v);
    }
  return res;
}
 
// Function: GCD
Value func_gcd (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value result = 0;
  for (unsigned int i = 0; i < args.count(); ++i)
    if (args[i].isArray())
      result = calc->gcd (result, func_gcd_helper (args[i], calc));
    else
      result = calc->gcd (result, args[i]);
  return result;
}
 
static Value func_lcm_helper(const Value &val, ValueCalc *calc)
{
  Value res = 0;
  if (!val.isArray ())
    return val;
  for (unsigned int row = 0; row < val.rows(); ++row)
    for (unsigned int col = 0; col < val.columns(); ++col)
    {
      Value v = val.element (col, row);
      if (v.isArray ())
        v = func_lcm_helper (v, calc);
      res = calc->lcm (res, v);
    }
  return res;
}
 
// Function: lcm
Value func_lcm (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value result = 0;
  for (unsigned int i = 0; i < args.count(); ++i)
    if (args[i].isArray())
      result = calc->lcm (result, func_lcm_helper (args[i], calc));
    else
      result = calc->lcm (result, args[i]);
  return result;
}
 
Value determinant (ValueCalc *calc, Value matrix)
{
  // this is a --- SLOOOW --- recursive function
  // using this for something bigger than 10x10 or so = suicide :P
  // but I'm too lazy to adjust gnumeric's code - remains as a TODO then
  // as a note, gnumeric uses LUP decomposition to compute this
 
  // take first row, generate smaller matrices, recursion, multiply
  Value res = 0.0;
  int n = matrix.columns();
  if (n == 1) return matrix.element (0, 0);
  if (n == 2) return calc->sub (
      calc->mul (matrix.element (1,1), matrix.element (0,0)),
      calc->mul (matrix.element (1,0), matrix.element (0,1)));
 
  // n >= 3
  for (int i = 0; i < n; ++i) {
    Value smaller (n-1, n-1);
    int col = 0;
    for (int c = 0; c < n; ++c)
      if (c != i) {
        // copy column c to column col in new matrix
        for (int r = 1; r < n; r++)
          smaller.setElement (col, r-1, matrix.element (c, r));
        col++;
      }
    Value minor = determinant (calc, smaller);
    if (i % 2 == 1) minor = calc->mul (minor, -1);
    res = calc->add (res, calc->mul (minor, matrix.element (i, 0)));
  }
  return res;
}
 
// Function: mdeterm
Value func_mdeterm (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value m = args[0];
  unsigned r = m.rows ();
  unsigned c = m.columns ();
  if (r != c)   // must be a square matrix
    return Value::errorVALUE();
 
  return determinant (calc, args[0]);
}
 
// Function: mmult
Value func_mmult (valVector args, ValueCalc *calc, FuncExtra *)
{
  Value m1 = args[0];
  Value m2 = args[1];
  unsigned r1 = m1.rows ();
  unsigned c1 = m1.columns ();
  unsigned r2 = m2.rows ();
  unsigned c2 = m2.columns ();
  if (c1 != r2)  // row/column counts must match
    return Value::errorVALUE();
 
  // create the resulting matrix
  Value res (c2, r1);
 
  // perform the multiplication - O(n^3) algorithm
  for (uint row = 0; row < r1; ++row)
    for (uint col = 0; col < c2; ++col) {
      Value val = 0.0;
      for (uint pos = 0; pos < c1; ++pos)
        val = calc->add (val,
            calc->mul (m1.element (pos, row), m2.element (col, pos)));
      res.setElement (col, row, val);
    }
  return res;
}
 
// Function: SUBTOTAL
// This function requires access to the Sheet and so on, because
// it needs to check whether cells contain the SUBTOTAL formula or not ...
// Cells containing a SUBTOTAL formula must be ignored.
Value func_subtotal (valVector args, ValueCalc *calc, FuncExtra *e)
{
  int function = calc->conv()->asInteger (args[0]).asInteger();
  Value range = args[1];
  int r1 = -1, c1 = -1, r2 = -1, c2 = -1;
  if (e) {
    r1 = e->ranges[1].row1;
    c1 = e->ranges[1].col1;
    r2 = e->ranges[1].row2;
    c2 = e->ranges[1].col2;
  }
 
  // if we have a range, run through it, and put an empty value to the place
  // of all occurences of the SUBTOTAL function
  Value empty;
  if ((r1 > 0) && (c1 > 0) && (r2 > 0) && (c2 > 0)) {
    for (int r = r1; r <= r2; ++r)
      for (int c = c1; c <= c2; ++c) {
        Cell *cell = e->sheet->cellAt (c, r);
        if (cell->isDefault())
          continue;
        if (cell->isFormula() && cell->text().find ("SUBTOTAL", 0, false) != -1)
          // cell contains the word SUBTOTAL - replace value with empty
          range.setElement (c-c1, r-r1, empty);
      }
  }
 
  // Good. Now we can execute the necessary function on the range.
  Value res;
  Function *f;
  valVector a;
  switch (function) {
   case 1: // Average
    res = calc->avg (range, false);
    break;
   case 2: // Count
    res = calc->count (range, false);
    break;
   case 3: // CountA
    res = calc->count (range);
    break;
   case 4: // MAX
    res = calc->max (range, false);
    break;
   case 5: // Min
    res = calc->min (range, false);
    break;
   case 6: // Product
    res = calc->product (range, 0.0, false);
    break;
   case 7: // StDev
    res = calc->stddev (range, false);
    break;
   case 8: // StDevP
    res = calc->stddevP (range, false);
    break;
   case 9: // Sum
    res = calc->sum (range, false);
    break;
   case 10: // Var
    f = FunctionRepository::self()->function ("VAR");
    if (!f) return Value::errorVALUE();
    a.reserve (1);
    a[0] = range;
    res = f->exec (a, calc, 0);
    break;
   case 11: // VarP
    f = FunctionRepository::self()->function ("VARP");
    if (!f) return Value::errorVALUE();
    a.reserve (1);
    a[0] = range;
    res = f->exec (a, calc, 0);
    break;
   default:
    return Value::errorVALUE();
  }
  return res;
}
 
/*
Commented out.
Absolutely no idea what this thing is supposed to do.
To anyone who would enable this code: it still uses koscript calls - you need
to convert it to the new style prior to uncommenting.
 
// Function: MULTIPLEOPERATIONS
Value func_multipleOP (valVector args, ValueCalc *calc, FuncExtra *)
{
  if (gCell)
  {
    context.setValue( new KSValue( ((Interpreter *) context.interpreter() )->cell()->value().asFloat() ) );
    return true;
  }
 
  gCell = ((Interpreter *) context.interpreter() )->cell();
 
  TQValueList<KSValue::Ptr>& args = context.value()->listValue();
  TQValueList<KSValue::Ptr>& extra = context.extraData()->listValue();
 
  if ( !KSUtil::checkArgumentsCount( context, 5, "MULTIPLEOPERATIONS", true ) )
  {
    gCell = 0;
    return false;
  }
 
  // 0: cell must contain formula with double/int result
  // 0, 1, 2, 3, 4: must contain integer/double
  for (int i = 0; i < 5; ++i)
  {
    if ( !KSUtil::checkType( context, args[i], KSValue::DoubleType, true ) )
    {
      gCell = 0;
      return false;
    }
  }
 
  //  ((Interpreter *) context.interpreter() )->document()->emitBeginOperation();
 
  double oldCol = args[1]->doubleValue();
  double oldRow = args[3]->doubleValue();
  kdDebug() << "Old values: Col: " << oldCol << ", Row: " << oldRow << endl;
 
  Cell * cell;
  Sheet * sheet = ((Interpreter *) context.interpreter() )->sheet();
 
  Point point( extra[1]->stringValue() );
  Point point2( extra[3]->stringValue() );
  Point point3( extra[0]->stringValue() );
 
  if ( ( args[1]->doubleValue() != args[2]->doubleValue() )
       || ( args[3]->doubleValue() != args[4]->doubleValue() ) )
  {
    cell = sheet->cellAt( point.pos.x(), point.pos.y() );
    cell->setValue( args[2]->doubleValue() );
    kdDebug() << "Setting value " << args[2]->doubleValue() << " on cell " << point.pos.x()
              << ", " << point.pos.y() << endl;
 
    cell = sheet->cellAt( point2.pos.x(), point.pos.y() );
    cell->setValue( args[4]->doubleValue() );
    kdDebug() << "Setting value " << args[4]->doubleValue() << " on cell " << point2.pos.x()
              << ", " << point2.pos.y() << endl;
  }
 
  Cell * cell1 = sheet->cellAt( point3.pos.x(), point3.pos.y() );
  cell1->calc( false );
 
  double d = cell1->value().asFloat();
  kdDebug() << "Cell: " << point3.pos.x() << "; " << point3.pos.y() << " with value "
            << d << endl;
 
  kdDebug() << "Resetting old values" << endl;
 
  cell = sheet->cellAt( point.pos.x(), point.pos.y() );
  cell->setValue( oldCol );
 
  cell = sheet->cellAt( point2.pos.x(), point2.pos.y() );
  cell->setValue( oldRow );
 
  cell1->calc( false );
 
  // ((Interpreter *) context.interpreter() )->document()->emitEndOperation();
 
  context.setValue( new KSValue( (double) d ) );
 
  gCell = 0;
  return true;
}
 
*/