File indexing completed on 2024-05-12 16:35:56
0001 /* This file is part of the KDE project 0002 Copyright 2007 Ariya Hidayat <ariya@kde.org> 0003 Copyright 2007 Sascha Pfau <MrPeacock@gmail.com> 0004 0005 This library is free software; you can redistribute it and/or 0006 modify it under the terms of the GNU Library General Public 0007 License as published by the Free Software Foundation; only 0008 version 2 of the License. 0009 0010 This library is distributed in the hope that it will be useful, 0011 but WITHOUT ANY WARRANTY; without even the implied warranty of 0012 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 0013 Library General Public License for more details. 0014 0015 You should have received a copy of the GNU Library General Public License 0016 along with this library; see the file COPYING.LIB. If not, write to 0017 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, 0018 Boston, MA 02110-1301, USA. 0019 */ 0020 #include "TestMathFunctions.h" 0021 0022 #include "TestKspreadCommon.h" 0023 0024 #include <CellStorage.h> 0025 #include <Formula.h> 0026 #include <Map.h> 0027 #include <Sheet.h> 0028 #include <CalculationSettings.h> 0029 0030 // NOTE: we do not compare the numbers _exactly_ because it is difficult 0031 // to get one "true correct" expected values for the functions due to: 0032 // - different algorithms among spreadsheet programs 0033 // - precision limitation of floating-point number representation 0034 // - accuracy problem due to propagated error in the implementation 0035 #define CHECK_EVAL(x,y) QCOMPARE(TestDouble(x,y,6),y) 0036 #define CHECK_EVAL_SHORT(x,y) QCOMPARE(TestDouble(x,y,10),y) 0037 #define ROUND(x) (roundf(1e10 * x) / 1e10) 0038 0039 Value TestMathFunctions::TestDouble(const QString& formula, const Value& v2, int accuracy) 0040 { 0041 double epsilon = DBL_EPSILON * pow(10.0, (double)(accuracy)); 0042 0043 Formula f(m_map->sheet(0)); 0044 QString expr = formula; 0045 if (expr[0] != '=') 0046 expr.prepend('='); 0047 f.setExpression(expr); 0048 Value result = f.eval(); 0049 0050 bool res = fabs(v2.asFloat() - result.asFloat()) < epsilon; 0051 0052 if (!res) 0053 qDebug() << "check failed -->" << "Epsilon =" << epsilon << "" << (double) v2.asFloat() << " to" << (double)result.asFloat() << " diff =" << (double)(v2.asFloat() - result.asFloat()); 0054 /* else 0055 qDebug()<<"check -->" <<" diff =" << v2.asFloat()-result.asFloat();*/ 0056 if (res) 0057 return v2; 0058 else 0059 return result; 0060 } 0061 0062 // round to get at most 10-digits number 0063 inline static Value RoundNumber(double f) 0064 { 0065 return Value(ROUND(f)); 0066 } 0067 0068 // round to get at most 10-digits number 0069 inline static Value RoundNumber(const Value& v) 0070 { 0071 if (v.isNumber()) { 0072 double d = numToDouble(v.asFloat()); 0073 if (fabs(d) < DBL_EPSILON) 0074 d = 0.0; 0075 return Value(ROUND(d)); 0076 } else 0077 return v; 0078 } 0079 0080 Value TestMathFunctions::evaluate(const QString& formula) 0081 { 0082 Formula f(m_map->sheet(0)); 0083 QString expr = formula; 0084 if (expr[0] != '=') 0085 expr.prepend('='); 0086 f.setExpression(expr); 0087 Value result = f.eval(); 0088 0089 #if 0 0090 // this magically generates the CHECKs 0091 printf(" CHECK_EVAL( \"%s\", %15g) );\n", qPrintable(formula), result.asFloat()); 0092 #endif 0093 0094 return RoundNumber(result); 0095 } 0096 0097 void TestMathFunctions::initTestCase() 0098 { 0099 FunctionModuleRegistry::instance()->loadFunctionModules(); 0100 0101 m_map = new Map(0 /* no Doc */); 0102 m_map->addNewSheet("Sheet1"); 0103 m_map->addNewSheet("Sheet2"); 0104 0105 Sheet* sheet1 = m_map->sheet(0); 0106 CellStorage* storage1 = sheet1->cellStorage(); 0107 0108 // Sheet1!B3:B7 0109 storage1->setValue(2, 3, Value("7")); 0110 storage1->setValue(2, 4, Value(2)); 0111 storage1->setValue(2, 5, Value(3)); 0112 storage1->setValue(2, 6, Value(true)); 0113 storage1->setValue(2, 7, Value("Hello")); 0114 // Sheet1!B9 0115 storage1->setValue(2, 9, Value::errorDIV0()); 0116 0117 Sheet* sheet2 = m_map->sheet(1); 0118 CellStorage* storage2 = sheet2->cellStorage(); 0119 0120 // Sheet2!A1:B13 0121 storage2->setValue(1, 1, Value("test")); 0122 storage2->setValue(2, 1, Value(1)); 0123 storage2->setValue(1, 2, Value("test1")); 0124 storage2->setValue(2, 2, Value(2)); 0125 storage2->setValue(1, 3, Value("test2")); 0126 storage2->setValue(2, 3, Value(3)); 0127 storage2->setValue(1, 4, Value("test1*")); 0128 storage2->setValue(2, 4, Value(4)); 0129 storage2->setValue(1, 5, Value("test1.*")); 0130 storage2->setValue(2, 5, Value(5)); 0131 storage2->setValue(1, 6, Value("TeSt")); 0132 storage2->setValue(2, 6, Value(6)); 0133 storage2->setValue(1, 7, Value("Test1")); 0134 storage2->setValue(2, 7, Value(7)); 0135 storage2->setValue(1, 8, Value("Test2")); 0136 storage2->setValue(2, 8, Value(8)); 0137 storage2->setValue(1, 9, Value(" test")); 0138 storage2->setValue(2, 9, Value(9)); 0139 storage2->setValue(1, 10, Value(" test1")); 0140 storage2->setValue(2, 10, Value(10)); 0141 storage2->setValue(1, 11, Value("*test")); 0142 storage2->setValue(2, 11, Value(11)); 0143 storage2->setValue(1, 12, Value("*test test")); 0144 storage2->setValue(2, 12, Value(12)); 0145 storage2->setValue(1, 13, Value("^test")); 0146 storage2->setValue(2, 13, Value(13)); 0147 } 0148 0149 void TestMathFunctions::cleanupTestCase() 0150 { 0151 delete m_map; 0152 } 0153 0154 /////////////////////////////////////////////////////////////////////////////////////////////// 0155 0156 void TestMathFunctions::testABS() 0157 { 0158 CHECK_EVAL("ABS(0)", Value(0)); 0159 CHECK_EVAL("ABS(-1)", Value(1)); 0160 CHECK_EVAL("ABS(-2)", Value(2)); 0161 CHECK_EVAL("ABS(-3)", Value(3)); 0162 CHECK_EVAL("ABS(-4)", Value(4)); 0163 CHECK_EVAL("ABS(1)", Value(1)); 0164 CHECK_EVAL("ABS(2)", Value(2)); 0165 CHECK_EVAL("ABS(3)", Value(3)); 0166 CHECK_EVAL("ABS(4)", Value(4)); 0167 CHECK_EVAL("ABS(1/0)", Value::errorDIV0()); 0168 } 0169 0170 void TestMathFunctions::testACOS() 0171 { 0172 // ODF-tests 0173 CHECK_EVAL("ACOS(SQRT(2)/2)*4/PI()", Value(1.0)); // arc cosine of SQRT(2)/2 is PI()/4 radians. 0174 CHECK_EVAL("ACOS(TRUE())", Value(0.0)); // TRUE() is 1 if inline. 0175 CHECK_EVAL("ACOS(-1.0)/PI()", Value(1.0)); // The result must be between 0.0 and PI(). 0176 CHECK_EVAL("ACOS(2.0)", Value::errorVALUE()); // The argument must be between -1.0 and 1.0. 0177 0178 // ACosinus needs to be a numeric value between >=-1.0 and <=1.0 0179 CHECK_EVAL("ACOS()", Value::errorVALUE()); 0180 CHECK_EVAL("ACOS(-1.1)", Value::errorVALUE()); 0181 CHECK_EVAL("ACOS(1.1)", Value::errorVALUE()); 0182 0183 CHECK_EVAL("ACOS(1.0)", Value(0)); 0184 CHECK_EVAL_SHORT("2-ACOS(-1.0)", Value(-1.14159265)); 0185 } 0186 0187 void TestMathFunctions::testACOSH() 0188 { 0189 // ODF-tests 0190 CHECK_EVAL("ACOSH(1)", Value(0)); // 0191 CHECK_EVAL("ACOSH(2)", Value(1.316957897)); // 0192 } 0193 0194 void TestMathFunctions::testACOT() 0195 { 0196 // ODF-tests 0197 CHECK_EVAL("ACOT(0)-PI()/2", Value(0)); // 0198 } 0199 0200 void TestMathFunctions::testACOTH() 0201 { 0202 // ODF-tests 0203 CHECK_EVAL("ACOTH(2)", Value(0.5493061443)); // 0204 } 0205 0206 void TestMathFunctions::testASIN() 0207 { 0208 // ODF-tests 0209 CHECK_EVAL("ASIN(SQRT(2)/2)*4/PI()", Value(1.0)); // arc sine of SQRT(2)/2 is PI()/4 radians. 0210 CHECK_EVAL("ASIN(TRUE())*2/PI()", Value(1.0)); // TRUE() is 1 if inline. 0211 CHECK_EVAL("ASIN(-1)*2/PI()", Value(-1.0)); // The result must be between -PI()/2 and PI()/2. 0212 CHECK_EVAL("ASIN(2)", Value::errorVALUE()); // The argument must be between -1.0 and 1.0. 0213 0214 // ASinus needs to be a numeric value between >=-1.0 and <=1.0 0215 CHECK_EVAL("ASIN(1.2)", Value::errorVALUE()); 0216 CHECK_EVAL("ASIN(-99)", Value::errorVALUE()); 0217 0218 CHECK_EVAL_SHORT("1-ASIN(1)", Value(-0.57079633)); 0219 CHECK_EVAL_SHORT("1+ASIN(-1.0)", Value(-0.57079633)); 0220 } 0221 0222 void TestMathFunctions::testASINH() 0223 { 0224 // ODF-tests 0225 CHECK_EVAL("ASINH(0)", Value(0)); // 0226 CHECK_EVAL("ASINH(1)", Value(0.881373587)); // 0227 } 0228 0229 void TestMathFunctions::testATAN() 0230 { 0231 // ODF-tests 0232 CHECK_EVAL("ATAN(1)*4/PI()", Value(1)); // arc tangent of 1 is PI()/4 radians. 0233 CHECK_EVAL_SHORT("ATAN(-1.0e16)", Value(-1.570796)); // TODO expand / Check if ATAN gives reasonably accurate results, 0234 // and that slightly negative values as input produce numbers near -PI/2. 0235 } 0236 0237 void TestMathFunctions::testATAN2() 0238 { 0239 // ODF-tests 0240 CHECK_EVAL("ATAN2(1;1)*4/PI()", Value(1)); // arc tangent of 1.0/1.0 is PI()/4 radians. 0241 CHECK_EVAL("ATAN2(1;-1)*4/PI()", Value(-1)); // Location of sign makes a difference. 0242 CHECK_EVAL("ATAN2(-1;1)*4/PI()", Value(3)); // Location of sign makes a difference. 0243 CHECK_EVAL("ATAN2(-1;-1)*4/PI()", Value(-3)); // Location of sign makes a difference. 0244 CHECK_EVAL("SIGN(ATAN2(-1.0;0.001))", Value(1)); // If y is small, it's still important 0245 CHECK_EVAL("SIGN(ATAN2(-1.0;-0.001))", Value(-1)); // If y is small, it's still important 0246 CHECK_EVAL("ATAN2(-1.0;0)/PI()", Value(1)); // By definition ATAN2(-1,0) should give PI() rather than -PI(). 0247 } 0248 0249 void TestMathFunctions::testATANH() 0250 { 0251 // ODF-tests 0252 CHECK_EVAL_SHORT("ATANH(0)", Value(0)); // TODO expand 0253 CHECK_EVAL_SHORT("ATANH(0.5)", Value(0.549306144)); // TODO expand 0254 } 0255 0256 void TestMathFunctions::testBESSELI() 0257 { 0258 // ODF-tests 0259 CHECK_EVAL_SHORT("BESSELI(2;2)", Value(0.688948)); // TODO expand 0260 CHECK_EVAL_SHORT("BESSELI(0.7;3)", Value(0.007367)); 0261 // alternate function name 0262 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETBESSELI(2;2)", Value(0.688948)); 0263 } 0264 0265 void TestMathFunctions::testBESSELJ() 0266 { 0267 // ODF-tests 0268 CHECK_EVAL_SHORT("BESSELJ(1;0)", Value(0.765198)); // TODO expand 0269 CHECK_EVAL_SHORT("BESSELJ(0.89;3)", Value(0.013974)); 0270 // alternate function name 0271 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETBESSELJ(1;0)", Value(0.765198)); 0272 } 0273 0274 void TestMathFunctions::testBESSELK() 0275 { 0276 // ODF-tests 0277 CHECK_EVAL_SHORT("BESSELK(3;0)", Value(0.03474)); // TODO expand 0278 // alternate function name 0279 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETBESSELK(3;0)", Value(0.03474)); 0280 } 0281 0282 void TestMathFunctions::testBESSELY() 0283 { 0284 // ODF-tests 0285 CHECK_EVAL_SHORT("BESSELY(1;1)", Value(-0.781213)); // TODO expand 0286 CHECK_EVAL_SHORT("BESSELY(4;2)", Value(0.215903595)); 0287 // alternate function name 0288 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETBESSELY(1;1)", Value(-0.781213)); 0289 } 0290 0291 void TestMathFunctions::testCEIL() 0292 { 0293 CHECK_EVAL("CEIL(0)", Value(0)); 0294 0295 CHECK_EVAL("CEIL(0.1)", Value(1)); 0296 CHECK_EVAL("CEIL(0.01)", Value(1)); 0297 CHECK_EVAL("CEIL(0.001)", Value(1)); 0298 CHECK_EVAL("CEIL(0.0001)", Value(1)); 0299 CHECK_EVAL("CEIL(0.00001)", Value(1)); 0300 CHECK_EVAL("CEIL(0.000001)", Value(1)); 0301 CHECK_EVAL("CEIL(0.0000001)", Value(1)); 0302 0303 CHECK_EVAL("CEIL(1.1)", Value(2)); 0304 CHECK_EVAL("CEIL(1.01)", Value(2)); 0305 CHECK_EVAL("CEIL(1.001)", Value(2)); 0306 CHECK_EVAL("CEIL(1.0001)", Value(2)); 0307 CHECK_EVAL("CEIL(1.00001)", Value(2)); 0308 CHECK_EVAL("CEIL(1.000001)", Value(2)); 0309 CHECK_EVAL("CEIL(1.0000001)", Value(2)); 0310 0311 CHECK_EVAL("CEIL(-0.1)", Value(0)); 0312 CHECK_EVAL("CEIL(-0.01)", Value(0)); 0313 CHECK_EVAL("CEIL(-0.001)", Value(0)); 0314 CHECK_EVAL("CEIL(-0.0001)", Value(0)); 0315 CHECK_EVAL("CEIL(-0.00001)", Value(0)); 0316 CHECK_EVAL("CEIL(-0.000001)", Value(0)); 0317 CHECK_EVAL("CEIL(-0.0000001)", Value(0)); 0318 0319 0320 CHECK_EVAL("CEIL(-1.1)", Value(-1)); 0321 CHECK_EVAL("CEIL(-1.01)", Value(-1)); 0322 CHECK_EVAL("CEIL(-1.001)", Value(-1)); 0323 CHECK_EVAL("CEIL(-1.0001)", Value(-1)); 0324 CHECK_EVAL("CEIL(-1.00001)", Value(-1)); 0325 CHECK_EVAL("CEIL(-1.000001)", Value(-1)); 0326 CHECK_EVAL("CEIL(-1.0000001)", Value(-1)); 0327 } 0328 0329 void TestMathFunctions::testCEILING() 0330 { 0331 CHECK_EVAL("CEILING(0 ; 0.1)", Value(0)); 0332 CHECK_EVAL("CEILING(0 ; 0.2)", Value(0)); 0333 CHECK_EVAL("CEILING(0 ; 1.0)", Value(0)); 0334 CHECK_EVAL("CEILING(0 ;10.0)", Value(0)); 0335 0336 CHECK_EVAL("CEILING(0.1; 0.2)", Value(0.2)); 0337 CHECK_EVAL("CEILING(0.1; 0.4)", Value(0.4)); 0338 CHECK_EVAL("CEILING(1.1; 0.2)", Value(1.2)); 0339 0340 // because can't divide by 0 0341 CHECK_EVAL("CEILING(1; 0)", Value::errorDIV0()); 0342 CHECK_EVAL("CEILING(2; 0)", Value::errorDIV0()); 0343 0344 // but this one should be just fine ! 0345 CHECK_EVAL("CEILING(0; 0)", Value(0)); 0346 0347 // different sign does not make sense 0348 CHECK_EVAL("CEILING(-1; 2)", Value::errorNUM()); 0349 CHECK_EVAL("CEILING(1; -2)", Value::errorNUM()); 0350 0351 // mode param 0352 CHECK_EVAL("CEILING(-5.4; -4.0; 0)", Value(-4.0)); 0353 CHECK_EVAL("CEILING(-5.4; -4.0; 1)", Value(-8.0)); 0354 } 0355 0356 void TestMathFunctions::testCOMBIN() 0357 { 0358 // ODF-tests 0359 CHECK_EVAL("COMBIN(5;3)", Value(10)); // 0360 CHECK_EVAL("COMBIN(6;3)", Value(20)); // 0361 CHECK_EVAL("COMBIN(42;3)", Value(11480)); // 0362 CHECK_EVAL("COMBIN(-1;3)", Value::errorNUM()); // N must be >= 0 0363 CHECK_EVAL("COMBIN(4;-3)", Value::errorNUM()); // M must be >= 0 0364 } 0365 0366 void TestMathFunctions::testCOMBINA() 0367 { 0368 // ODF-tests 0369 CHECK_EVAL("COMBINA(5;3)", Value(35)); // 0370 CHECK_EVAL("COMBINA(-1;3)", Value::errorNUM()); // N must be >= 0 0371 CHECK_EVAL("COMBINA(4;-3)", Value::errorNUM()); // M must be >= 0 0372 } 0373 0374 void TestMathFunctions::testCONVERT() 0375 { 0376 // ODF-tests 0377 0378 // TODO add missing SI-units and expand up to 10 digits 0379 0380 CHECK_EVAL("CONVERT( 1; \"ft\"; \"in\")", Value(12)); // 1 foot is 12 inches. Conversion between 0381 // units might involve an intermediate SI unit 0382 // in some implementations, and such round-off 0383 // error is considered acceptable. 0384 CHECK_EVAL("CONVERT( 1; \"in\"; \"cm\")", Value(2.54)); // 1 inch is 2.54 cm. The result is exact, because 0385 // this needs to be represented as accurately as the 0386 // underlying numerical model permits 0387 CHECK_EVAL("CONVERT( 5; \"m\"; \"mm\")", Value(5000)); // 5 meters is 5000 millimeters 0388 CHECK_EVAL("CONVERT( 100; \"C\"; \"F\")", Value(212)); // 212 degrees F is 100 degrees C. Note that this 0389 // is not simply a multiplicative relationship. 0390 // Since internally this is (100/5*9+32), where 0391 // 100/5 is exactly 20, this result needs to be 0392 // exact even on floating-point implementations 0393 CHECK_EVAL("CONVERT( 2; \"Ym\"; \"Zm\")", Value(2000)); // Must support Y and Z prefixes. (wrong ODF-specs 100) 0394 CHECK_EVAL("CONVERT( 20; \"F\"; \"m\")", Value::errorNA()); // Different groups produce an error. 0395 CHECK_EVAL_SHORT("CONVERT(1000;\"qt\";\"l\")", Value(946.5588641)); // Quart is U.S. customary, liquid measure 0396 CHECK_EVAL_SHORT("CONVERT(1000;\"tbs\";\"l\")", Value(14.78998225)); // Tablespoon uses U.S. customary historic definition 0397 // - note that there are many other definitions 0398 CHECK_EVAL("CONVERT(1000; \"tsp\"; \"l\")", Value(4.929994084)); // Teaspoon uses U.S. customary historic definition 0399 // - note that there are many other definitions 0400 CHECK_EVAL("CONVERT( 1; \"das\"; \"sec\")", Value(10)); // Does it support both "s" and "sec" for second? 0401 // Does it support "da" as the SI standard deka prefix? 0402 CHECK_EVAL("CONVERT( 1; \"ar\"; \"m^2\")", Value(100)); // A hectare (ar) is 100 square meters. 0403 // CHECK_EVAL( "CONVERT( 1; \"cal\"; \"J\")", Value( 4.1868 ) ); // "cal" is an International Table (IT) calorie, 4.1868 J. 0404 CHECK_EVAL("CONVERT( 1; \"lbf\"; \"N\")", Value(4.448222)); // Converting pound-force to Newtons 0405 CHECK_EVAL("CONVERT( 1; \"HP\"; \"W\")", Value(745.701)); // Horsepower to Watts 0406 CHECK_EVAL("CONVERT( 1; \"Mibyte\"; \"bit\")", Value(8388608)); // Converts bytes to bits, and tests binary prefixes 0407 CHECK_EVAL("CONVERT( 1; \"Gibyte\"; \"Mibyte\")", Value(1024)); // Converts bytes to bits, and tests binary prefixes 0408 CHECK_EVAL("CONVERT( 1; \"T\"; \"ga\")", Value(10000)); // Tesla to Gauss 0409 // CHECK_EVAL( "CONVERT( 1; \"lbm\"; \"g\")", Value( 453.59237 ) ); // International pound mass (avoirdupois) to grams. 0410 // (This is actually exact.) 0411 CHECK_EVAL("CONVERT( 1; \"uk_ton\"; \"lbm\")", Value(2240)); // Imperial ton, aka "long ton", "deadweight ton", 0412 // or "weight ton", is 2240 lbm. 0413 // CHECK_EVAL( "CONVERT( 1; \"psi\"; \"Pa\")", Value( 6894.76 ) ); // Pounds per square inch to Pascals. 0414 CHECK_EVAL("CONVERT( 60; \"mph\"; \"km/h\")", Value(96.56064)); // Miles per hour to kilometers per hour. 0415 CHECK_EVAL("CONVERT( 1; \"day\"; \"s\")", Value(86400)); // Day to seconds. Note: This test uses the 0416 // international standard abbreviation for second (s), 0417 // not the abbreviation traditionally used in spreadsheets 0418 // (sec); both "s" and "sec" must be supported. 0419 // CHECK_EVAL_SHORT( "CONVERT( 1; \"qt\"; \"L\")", Value( 0.9463529460 ) ); // Quart (U.S. customary liquid measure) to liter. 0420 // This is 0.946352946 liters, 0421 } 0422 0423 void TestMathFunctions::testCOT() 0424 { 0425 // ODF-tests 0426 CHECK_EVAL("COT(PI()/4.0)", Value(1)); // cotangent of PI()/4.0 radians. 0427 CHECK_EVAL("COT(PI()/2.0)", Value(0)); // cotangent of PI()/2 radians. Not the same as TAN. 0428 CHECK_EVAL("COT(0)", Value::errorDIV0()); // cotangent of PI()/4.0 radians. 0429 } 0430 0431 void TestMathFunctions::testCOTH() 0432 { 0433 // ODF-tests 0434 CHECK_EVAL("COTH(1)", Value(1.3130352855)); // 0435 CHECK_EVAL("COTH(EXP(1))", Value(1.0087469296)); // 0436 } 0437 0438 void TestMathFunctions::testDEGREES() 0439 { 0440 // ODF-tests 0441 CHECK_EVAL("DEGREES(PI())", Value(180)); // PI() radians is 180 degrees. 0442 } 0443 0444 void TestMathFunctions::testDELTA() 0445 { 0446 // ODF-tests 0447 CHECK_EVAL("DELTA(2;3)", Value(0)); // Different numbers are not equal 0448 CHECK_EVAL("DELTA(2;2)", Value(1)); // Same numbers are equal 0449 CHECK_EVAL("DELTA(0)" , Value(1)); // 0 equal to default 0 0450 } 0451 0452 void TestMathFunctions::testEVEN() 0453 { 0454 // ODF-tests 0455 CHECK_EVAL("EVEN(6)", Value(6)); // Positive even integers remain unchanged. 0456 CHECK_EVAL("EVEN(-4)", Value(-4)); // Negative even integers remain unchanged. 0457 CHECK_EVAL("EVEN(1)", Value(2)); // Non-even positive integers round up. 0458 CHECK_EVAL("EVEN(0.3)", Value(2)); // Positive floating values round up. 0459 CHECK_EVAL("EVEN(-1)", Value(-2)); // Non-even negative integers round down. 0460 CHECK_EVAL("EVEN(-0.3)", Value(-2)); // Negative floating values round down. 0461 CHECK_EVAL("EVEN(0)", Value(0)); // Since zero is even, EVEN(0) returns zero. 0462 } 0463 0464 void TestMathFunctions::testEXP() 0465 { 0466 // ODF-tests 0467 CHECK_EVAL("EXP(0)", Value(1)); // Anything raised to the 0 power is 1. 0468 CHECK_EVAL("EXP(LN(2))", Value(2)); // The EXP function is the inverse of the LN function. 0469 CHECK_EVAL("EXP(1)", Value(2.71828182845904523536)); // The value of the natural logarithm e. 0470 } 0471 0472 void TestMathFunctions::testFACT() 0473 { 0474 CHECK_EVAL("FACT(0)", Value(1)); 0475 CHECK_EVAL("FACT(1)", Value(1)); 0476 CHECK_EVAL("FACT(2)", Value(2)); 0477 CHECK_EVAL("FACT(3)", Value(6)); 0478 CHECK_EVAL("FACT(-1)", Value::errorNUM()); 0479 CHECK_EVAL("FACT(\"xyzzy\")", Value::errorNUM()); 0480 } 0481 0482 void TestMathFunctions::testFACTDOUBLE() 0483 { 0484 CHECK_EVAL("FACTDOUBLE(0)", Value(1)); 0485 CHECK_EVAL("FACTDOUBLE(1)", Value(1)); 0486 CHECK_EVAL("FACTDOUBLE(7)", Value(105)); 0487 CHECK_EVAL("FACTDOUBLE(6)", Value(48)); 0488 CHECK_EVAL("FACTDOUBLE(-1)", Value::errorNUM()); 0489 CHECK_EVAL("FACTDOUBLE(\"xyzzy\")", Value::errorNUM()); 0490 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETFACTDOUBLE(7)", Value(105)); // alternate function name 0491 } 0492 0493 void TestMathFunctions::testFIB() 0494 { 0495 CHECK_EVAL("FIB(1)", Value(1)); 0496 CHECK_EVAL("FIB(2)", Value(1)); 0497 CHECK_EVAL("FIB(3)", Value(2)); 0498 CHECK_EVAL("FIB(4)", Value(3)); 0499 CHECK_EVAL("FIB(5)", Value(5)); 0500 CHECK_EVAL("FIB(6)", Value(8)); 0501 CHECK_EVAL("FIB(7)", Value(13)); 0502 CHECK_EVAL("FIB(8)", Value(21)); 0503 CHECK_EVAL("FIB(9)", Value(34)); 0504 CHECK_EVAL("FIB(10)", Value(55)); 0505 0506 // large number 0507 CHECK_EVAL("FIB(100)/1E+20", Value(3.54224848179263)); 0508 CHECK_EVAL("FIB(200)/1E+41", Value(2.80571172992512)); 0509 CHECK_EVAL("FIB(300)/1E+62", Value(2.22232244629423)); 0510 CHECK_EVAL("FIB(400)/1E+83", Value(1.76023680645016)); 0511 CHECK_EVAL("FIB(500)/1E+104", Value(1.394232245617)); 0512 CHECK_EVAL("FIB(600)/1E+125", Value(1.10433070572954)); 0513 0514 // invalid 0515 CHECK_EVAL("FIB(0)", Value::errorNUM()); 0516 CHECK_EVAL("FIB(-1)", Value::errorNUM()); 0517 CHECK_EVAL("FIB(\"text\")", Value::errorVALUE()); 0518 } 0519 0520 void TestMathFunctions::testFLOOR() 0521 { 0522 // ODF-Tests 0523 CHECK_EVAL("=FLOOR(2; 1)", Value(2)); 0524 CHECK_EVAL("=FLOOR(2.5; 1)", Value(2)); 0525 CHECK_EVAL("=FLOOR(5; 2)", Value(4)); 0526 CHECK_EVAL("=FLOOR(5; 2.2)", Value(4.4)); 0527 CHECK_EVAL("=FLOOR(-2.5;1)", Value::errorVALUE()); 0528 CHECK_EVAL("=FLOOR(-2.5; -1)", Value(-3)); 0529 CHECK_EVAL("=FLOOR(-2.5; -1;1)", Value(-2)); 0530 CHECK_EVAL("=FLOOR(-2.5;0)", Value(0)); 0531 CHECK_EVAL("=FLOOR(0;-1)", Value(0)); 0532 CHECK_EVAL("=FLOOR(-1.1)", Value(-2)); 0533 } 0534 0535 void TestMathFunctions::testGAMMA() 0536 { 0537 // ODF-Tests 0538 CHECK_EVAL("GAMMA(1.00)", Value(1.0000000000)); 0539 CHECK_EVAL("GAMMA(1.10)", Value(0.9513507700)); 0540 CHECK_EVAL("GAMMA(1.50)", Value(0.8862269255)); 0541 } 0542 0543 void TestMathFunctions::testGAMMALN() 0544 { 0545 // ODF-Tests 0546 CHECK_EVAL("GAMMALN(1.00)", Value(0)); 0547 CHECK_EVAL("GAMMALN(2.00)", Value(0)); 0548 CHECK_EVAL("GAMMALN(3.00)", Value(0.6931471806)); 0549 CHECK_EVAL("GAMMALN(1.50)", Value(-0.1207822376)); 0550 } 0551 0552 void TestMathFunctions::testGCD() 0553 { 0554 CHECK_EVAL("GCD(5;15;25)", Value(5)); 0555 CHECK_EVAL("GCD(2;3)", Value(1)); 0556 CHECK_EVAL("GCD(18;24)", Value(6)); 0557 CHECK_EVAL("GCD(18.1;24.1)", Value(6)); 0558 CHECK_EVAL("GCD(1.1;2.2)", Value(1)); 0559 CHECK_EVAL("GCD(18.9;24.9)", Value(6)); 0560 CHECK_EVAL("GCD(7)", Value(7)); 0561 CHECK_EVAL("GCD(5;0)", Value(5)); 0562 CHECK_EVAL("GCD(0;0)", Value(0)); 0563 CHECK_EVAL("GCD(-2;3)", Value::errorNUM()); 0564 CHECK_EVAL("GCD(2;-4)", Value::errorNUM()); 0565 CHECK_EVAL("GCD(-2;-4)", Value::errorNUM()); 0566 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETGCD(18;24)", Value(6)); // alternate function name 0567 } 0568 0569 void TestMathFunctions::testGESTEP() 0570 { 0571 // ODF-tests 0572 CHECK_EVAL("GESTEP(2;1)", Value(1)); // 0573 CHECK_EVAL("GESTEP(-1;-2)", Value(1)); // Negative arguments are valid 0574 CHECK_EVAL("GESTEP(1)", Value(1)); // Second parameter assumed 0 if omitted 0575 CHECK_EVAL("GESTEP(-2;1)", Value(0)); // 0576 CHECK_EVAL("GESTEP(3;3)", Value(1)); // Number identical to step value. 0577 CHECK_EVAL("GESTEP(1.3;1.2)", Value(1)); // Floating point values where X is greater than Step. 0578 CHECK_EVAL("GESTEP(-2;\"xxx\")", Value::errorNUM()); // 0579 CHECK_EVAL("GESTEP(\"xxx\";-2)", Value::errorNUM()); // 0580 } 0581 0582 void TestMathFunctions::testINT() 0583 { 0584 // ODF-tests 0585 CHECK_EVAL("=INT(2)", Value(2)); 0586 CHECK_EVAL("=INT(-3)", Value(-3)); 0587 CHECK_EVAL("=INT(1.2)", Value(1)); 0588 CHECK_EVAL("=INT(1.7)", Value(1)); 0589 CHECK_EVAL("=INT(-1.2)", Value(-2)); 0590 CHECK_EVAL("=INT((1/3)*3)", Value(1)); 0591 } 0592 0593 void TestMathFunctions::testLCM() 0594 { 0595 CHECK_EVAL("LCM(5;15;25)", Value(75)); 0596 CHECK_EVAL("LCM(2;3)", Value(6)); 0597 CHECK_EVAL("LCM(18;12)", Value(36)); 0598 CHECK_EVAL("LCM(12;18)", Value(36)); 0599 CHECK_EVAL("LCM(12.1;18.1)", Value(36)); 0600 CHECK_EVAL("LCM(18.1;12.1)", Value(36)); 0601 CHECK_EVAL("LCM(18.9;12.9)", Value(36)); 0602 CHECK_EVAL("LCM(7)", Value(7)); 0603 CHECK_EVAL("LCM(5;0)", Value(0)); 0604 CHECK_EVAL("LCM(-2;4)", Value::errorNUM()); 0605 CHECK_EVAL("LCM(2;-4)", Value::errorNUM()); 0606 CHECK_EVAL("LCM(-2;-4)", Value::errorNUM()); 0607 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETLCM(18;12)", Value(36)); // alternate function name 0608 } 0609 0610 void TestMathFunctions::testLN() 0611 { 0612 // ODF-tests 0613 CHECK_EVAL("LN(1)", Value(0)); // The logarithm of 1 (in any base) is 0. 0614 CHECK_EVAL("LN(EXP(1))", Value(1)); // The natural logarithm of e is 1. 0615 CHECK_EVAL_SHORT("LN(20)", Value(2.995732274)); // TODO expand / Trivial test 0616 CHECK_EVAL_SHORT("LN(0.2)", Value(-1.609437912)); // TODO expand / This tests a value between 0 and 0.5. 0617 // Values in this domain are valid, but implementations that compute LN(x) 0618 // by blindly summing the series (1/n)((x-1)/x)^n won't get this value 0619 // correct, because that series requires x > 0.5. 0620 CHECK_EVAL("LN(0)", Value::errorNUM()); // The argument must be greater than zero. 0621 CHECK_EVAL("LN(\"s\")", Value::errorNUM()); // The argument must be a number. 0622 } 0623 0624 void TestMathFunctions::testLOG() 0625 { 0626 CHECK_EVAL("LOG(1;10)", Value(0)); 0627 CHECK_EVAL("LOG(1;EXP(1))", Value(0)); 0628 CHECK_EVAL("LOG(10;10)", Value(1)); 0629 CHECK_EVAL("LOG(EXP(1);EXP(1))", Value(1)); 0630 CHECK_EVAL("LOG(10)", Value(1)); 0631 CHECK_EVAL("LOG(8*8*8;8)", Value(3)); 0632 CHECK_EVAL("LOG(0;10)", Value::errorNUM()); 0633 CHECK_EVAL("LOG(\"foo\";10)", Value::errorNUM()); 0634 CHECK_EVAL("LOG(2;\"foo\")", Value::errorNUM()); 0635 CHECK_EVAL("LOG(NA();10)", Value::errorNA()); 0636 CHECK_EVAL("LOG(10;NA())", Value::errorNA()); 0637 CHECK_EVAL("LOG(NA();NA())", Value::errorNA()); 0638 } 0639 0640 void TestMathFunctions::testLOG10() 0641 { 0642 CHECK_EVAL("LOG10(1)", Value(0)); 0643 CHECK_EVAL("LOG10(10)", Value(1)); 0644 CHECK_EVAL("LOG10(100)", Value(2)); 0645 CHECK_EVAL("LOG10(0)", Value::errorNUM()); 0646 CHECK_EVAL("LOG10(\"H\")", Value::errorNUM()); 0647 CHECK_EVAL("LOG10(-2)", Value::errorNUM()); 0648 } 0649 0650 void TestMathFunctions::testMDETERM() 0651 { 0652 CHECK_EVAL("MDETERM({2;4|3;5})", Value(-2)); 0653 CHECK_EVAL("MDETERM({2;4})", Value::errorVALUE()); 0654 CHECK_EVAL("MDETERM({2;4|3;6})", Value(0)); 0655 CHECK_EVAL("MDETERM(2)", Value(2)); 0656 } 0657 0658 void TestMathFunctions::testMINVERSE() 0659 { 0660 Value value(Value::Array); 0661 value.setElement(0, 0, Value(-2.5)); 0662 value.setElement(1, 0, Value(2.0)); 0663 value.setElement(0, 1, Value(1.5)); 0664 value.setElement(1, 1, Value(-1.0)); 0665 CHECK_EVAL("MINVERSE({2;4|3;5})", value); // simply invertible 0666 value.setElement(0, 0, Value(5.0)); 0667 value.setElement(1, 0, Value(1.0)); 0668 value.setElement(2, 0, Value(-2.0)); 0669 value.setElement(0, 1, Value(-1.0)); 0670 value.setElement(1, 1, Value(-1.0)); 0671 value.setElement(2, 1, Value(1.0)); 0672 value.setElement(0, 1, Value(-2.0)); 0673 value.setElement(1, 1, Value(1.0)); 0674 value.setElement(2, 1, Value(0.0)); 0675 CHECK_EVAL("MINVERSE({1;2;1|2;4;3|3;7;4}", value); // fails without pivoting 0676 CHECK_EVAL("MINVERSE({2;4})", Value::errorVALUE()); // non-square matrix 0677 CHECK_EVAL("MINVERSE({2;4|3;6})", Value::errorDIV0()); // singular matrix 0678 CHECK_EVAL("MINVERSE(2)", evaluate("{0.5}")); // one elementary matrix 0679 } 0680 0681 void TestMathFunctions::testMMULT() 0682 { 0683 CHECK_EVAL("MMULT({2;4|3;5};{2;4|3;5})", evaluate("{16.0;28.0|21.0;37.0}")); 0684 } 0685 0686 void TestMathFunctions::testMOD() 0687 { 0688 CHECK_EVAL("MOD(10;3)", Value(1)); // 10/3 has remainder 1. 0689 CHECK_EVAL("MOD(2;8)", Value(2)); // 2/8 is 0 remainder 2. 0690 CHECK_EVAL("MOD(5.5;2.5)", Value(0.5)); // The numbers need not be integers. 0691 CHECK_EVAL("MOD(-2;3)", Value(1)); // The location of the sign matters. 0692 CHECK_EVAL("MOD(2;-3)", Value(-1)); // The location of the sign matters. 0693 CHECK_EVAL("MOD(-2;-3)", Value(-2)); // The location of the sign matters. 0694 CHECK_EVAL("MOD(10;3)", Value(1)); // 10/3 has remainder 1. 0695 CHECK_EVAL("MOD(10;0)", Value::errorDIV0()); // Division by zero is not allowed 0696 } 0697 0698 void TestMathFunctions::testMROUND() 0699 { 0700 // ODF-tests 0701 CHECK_EVAL("=MROUND(1564;100)", Value(1600)); 0702 CHECK_EVAL("=MROUND(1520;100)", Value(1500)); 0703 CHECK_EVAL("=MROUND(1550;100)", Value(1600)); 0704 CHECK_EVAL("=MROUND(41.89;8)", Value(40)); 0705 // alternate function name 0706 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETMROUND(1520;100)", Value(1500)); 0707 } 0708 0709 void TestMathFunctions::testMULTINOMIAL() 0710 { 0711 // ODF-tests 0712 CHECK_EVAL("=MULTINOMIAL(3;4;5)", Value(27720)); 0713 // alternate function name 0714 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETMULTINOMIAL(3;4;5)", Value(27720)); 0715 } 0716 0717 void TestMathFunctions::testMUNIT() 0718 { 0719 CHECK_EVAL("MUNIT(2)", evaluate("{1;0|0;1}")); 0720 CHECK_EVAL("MUNIT(3)", evaluate("{1;0;0|0;1;0|0;0;1}")); 0721 } 0722 0723 void TestMathFunctions::testODD() 0724 { 0725 CHECK_EVAL("ODD(5)", Value(5)); 0726 CHECK_EVAL("ODD(-5)", Value(-5)); 0727 CHECK_EVAL("ODD(2)", Value(3)); 0728 CHECK_EVAL("ODD(0.3)", Value(1)); 0729 CHECK_EVAL("ODD(-2)", Value(-3)); 0730 CHECK_EVAL("ODD(-0.3)", Value(-1)); 0731 CHECK_EVAL("ODD(0)", Value(1)); 0732 } 0733 0734 void TestMathFunctions::testPOWER() 0735 { 0736 CHECK_EVAL("POWER(10;0)", Value(1)); // Anything raised to the 0 power is 1 0737 CHECK_EVAL("POWER(2;8)" , Value(256)); // 2^8 is 256 0738 } 0739 0740 void TestMathFunctions::testPRODUCT() 0741 { 0742 CHECK_EVAL("PRODUCT(2;3;4)", Value(24)); // Anything raised to the 0 power is 1 0743 CHECK_EVAL("PRODUCT(TRUE();2;3)" , Value(6)); // TRUE() is 1 if inline 0744 CHECK_EVAL("PRODUCT()", Value(0)); // Product with no parameters returns 0 0745 //TODO 0746 // check inline-values e.g. product(2;3;"2") 0747 } 0748 0749 void TestMathFunctions::testQUOTIENT() 0750 { 0751 CHECK_EVAL("QUOTIENT(10;5)", Value(2)); // 0752 CHECK_EVAL("QUOTIENT(14;5)" , Value(2)); // 0753 CHECK_EVAL("QUOTIENT(-204;-23)", Value(8)); // 0754 CHECK_EVAL("QUOTIENT(-45;8)", Value(-5)); // 0755 CHECK_EVAL("QUOTIENT(24;-5)" , Value(-4)); // 0756 CHECK_EVAL("QUOTIENT(21;-5)", Value(-4)); // 0757 CHECK_EVAL("QUOTIENT(-14;5)", Value(-2)); // 0758 CHECK_EVAL("QUOTIENT(5;0)" , Value::errorDIV0()); // 0759 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETQUOTIENT(14;5)", Value(2)); // alternate function name 0760 } 0761 0762 void TestMathFunctions::testRADIANS() 0763 { 0764 CHECK_EVAL("RADIANS(180)/PI()", Value(1)); // 180 degrees is PI() radians. 0765 } 0766 0767 void TestMathFunctions::testRAND() 0768 { 0769 CHECK_EVAL("RAND()>=0", Value(true)); // The random number must be between 0 and 1. 0770 CHECK_EVAL("RAND()<=1", Value(true)); // The random number must be between 0 and 1. 0771 } 0772 0773 void TestMathFunctions::testRANDBETWEEN() 0774 { 0775 CHECK_EVAL("RANDBETWEEN(8;8)", Value(8)); // If A=B, return A. 0776 CHECK_EVAL("RANDBETWEEN(5;15)>=5", Value(true)); // Must return value in range 0777 CHECK_EVAL("RANDBETWEEN(5;15)<=15", Value(true)); // Must return value in range 0778 CHECK_EVAL("RANDBETWEEN(15;5)>=5", Value(true)); // Must return value in range 0779 CHECK_EVAL("RANDBETWEEN(15;5)<=15", Value(true)); // Must return value in range 0780 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETRANDBETWEEN(8;8)", Value(8)); // alternate function name 0781 } 0782 0783 void TestMathFunctions::testROUND() 0784 { 0785 // ODF-tests 0786 CHECK_EVAL("=ROUND(10.1;0)", Value(10)); 0787 CHECK_EVAL("=ROUND(9.8;0)", Value(10)); 0788 CHECK_EVAL("=ROUND(0.5;0)", Value(1)); 0789 CHECK_EVAL("=ROUND(1/3;0) ", Value(0)); 0790 CHECK_EVAL("=ROUND(1/3;1)", Value(0.3)); 0791 CHECK_EVAL("=ROUND(1/3;2)", Value(0.33)); 0792 CHECK_EVAL("=ROUND(1/3;2.9)", Value(0.33)); 0793 CHECK_EVAL("=ROUND(5555;-1)", Value(5560)); 0794 CHECK_EVAL("=ROUND(-1.1; 0)", Value(-1)); 0795 CHECK_EVAL("=ROUND(-1.5; 0)", Value(-2)); 0796 CHECK_EVAL("=ROUND(-1.5)", Value(-2)); 0797 CHECK_EVAL("=ROUND(1.1)", Value(1)); 0798 CHECK_EVAL("=ROUND(9.8)", Value(10)); 0799 } 0800 0801 void TestMathFunctions::testROUNDDOWN() 0802 { 0803 // ODF-tests 0804 CHECK_EVAL("=ROUNDDOWN(1.45673;2)", Value(1.45)); 0805 CHECK_EVAL("=ROUNDDOWN(1;0)", Value(1)); 0806 CHECK_EVAL("=ROUNDDOWN(1)", Value(1)); 0807 CHECK_EVAL("=ROUNDDOWN(9;-1)", Value(0)); 0808 CHECK_EVAL("=ROUNDDOWN(-9;-1)", Value(0)); 0809 CHECK_EVAL("=ROUNDDOWN(9;0)", Value(9)); 0810 CHECK_EVAL("=ROUNDDOWN(-1.1)", Value(-1)); 0811 CHECK_EVAL("=ROUNDDOWN(-1.9)", Value(-1)); 0812 } 0813 0814 void TestMathFunctions::testROUNDUP() 0815 { 0816 // ODF-tests 0817 CHECK_EVAL("=ROUNDUP(1.45673;2)", Value(1.46)); 0818 CHECK_EVAL("=ROUNDUP(1.1;0)", Value(2)); 0819 CHECK_EVAL("=ROUNDUP(1.9;0)", Value(2)); 0820 CHECK_EVAL("=ROUNDUP(1)", Value(1)); 0821 CHECK_EVAL("=ROUNDUP(9;-1)", Value(10)); 0822 CHECK_EVAL("=ROUNDUP(-9;-1)", Value(-10)); 0823 CHECK_EVAL("=ROUNDUP(9;0)", Value(9)); 0824 CHECK_EVAL("=ROUNDUP(-1.1)", Value(-2)); 0825 CHECK_EVAL("=ROUNDUP(-1.9)", Value(-2)); 0826 } 0827 0828 void TestMathFunctions::testSERIESSUM() 0829 { 0830 CHECK_EVAL("SERIESSUM(2;0;2;{1;2})", Value(9)); // 0831 CHECK_EVAL("SERIESSUM(2;0;2;{1;2;3;4})", Value(313)); // 0832 CHECK_EVAL("SERIESSUM(2;0;2;{1;2;3;4;5;6;7})", Value(36409)); // 0833 CHECK_EVAL("SERIESSUM(2;2;2;{1;6;5;4;3;2;7})", Value(127396)); // 0834 CHECK_EVAL("SERIESSUM(3;0;2;{1;2;3;4})", Value(3178)); // 0835 CHECK_EVAL("SERIESSUM(\"error\";0;2;{1;2})", Value::errorNUM()); // Text is not allowed 0836 } 0837 0838 void TestMathFunctions::testSIGN() 0839 { 0840 CHECK_EVAL("SIGN(-4)", Value(-1)); // N < 0 returns -1 0841 CHECK_EVAL("SIGN(4)", Value(1)); // N > 0 returns +1 0842 CHECK_EVAL("SIGN(0)", Value(0)); // N == 0 returns 0 0843 } 0844 0845 void TestMathFunctions::testSQRT() 0846 { 0847 CHECK_EVAL("SQRT(4)", Value(2)); // The square root of 4 is 2. 0848 CHECK_EVAL("SQRT(-4)", Value::errorNUM()); // N > 0 returns +1 0849 } 0850 0851 void TestMathFunctions::testSQRTPI() 0852 { 0853 CHECK_EVAL_SHORT("SQRTPI(1)", Value(1.77245385)); // TODO more digits / The square root of PI 0854 CHECK_EVAL("SQRTPI(2)", Value(2.5066282746)); // The square root of 2PI 0855 CHECK_EVAL("SQRTPI(-4)", Value::errorNUM()); // The argument must be non-negative 0856 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETSQRTPI(2)", Value(2.5066282746)); // alternate function name 0857 } 0858 0859 void TestMathFunctions::testSUBTOTAL() 0860 { 0861 CHECK_EVAL("SUBTOTAL(1;7)", Value(7)); // Average. 0862 CHECK_EVAL("SUBTOTAL(2;8)", Value(1)); // Count. 0863 CHECK_EVAL("SUBTOTAL(3;11)", Value(1)); // Count. 0864 CHECK_EVAL("SUBTOTAL(11;33)", Value(0)); 0865 CHECK_EVAL("SUBTOTAL(12;33)", Value::errorVALUE()); 0866 CHECK_EVAL("SUBTOTAL(102;8)", Value(1)); // Count. 0867 CHECK_EVAL("SUBTOTAL(111;33)", Value(0)); // Average. 0868 CHECK_EVAL("SUBTOTAL(1111;33)", Value(0)); // Average. 0869 } 0870 0871 void TestMathFunctions::testSUMA() 0872 { 0873 CHECK_EVAL("SUMA(1;2;3)", Value(6)); // Simple sum. 0874 CHECK_EVAL("SUMA(TRUE();2;3)", Value(6)); // TRUE() is 1. 0875 } 0876 0877 void TestMathFunctions::testSUMIF() 0878 { 0879 // B3 = 7 0880 // B4 = 2 0881 // B5 = 3 0882 CHECK_EVAL("SUMIF(B4:B5;\">2.5\")", Value(3)); // B4 is 2 and B5 is 3, so only B5 has a value greater than 2.5. 0883 CHECK_EVAL("SUMIF(B3:B5;B4)", Value(2)); // Test if a cell equals the value in B4. 0884 CHECK_EVAL("SUMIF("";B4)", Value::errorNUM()); // Constant values are not allowed for the range. 0885 CHECK_EVAL("SUMIF(B3:B4;\"7\";B4:B5)", Value(2)); // B3 is the string "7", but its match is mapped to B4 for the summation. 0886 CHECK_EVAL("SUMIF(B3:B10;1+1)", Value(2)); // The criteria can be an expression. 0887 CHECK_EVAL("SUMIF(B3:B4;\"7\")", Value(0)); // TODO B3 is the string "7", but only numbers are summed. 0888 } 0889 0890 void TestMathFunctions::testSUMIF_STRING() 0891 { 0892 m_map->calculationSettings()->setUseWildcards(false); 0893 m_map->calculationSettings()->setUseRegularExpressions(false); 0894 0895 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test\";Sheet2!B1:B32767)", Value(7)); 0896 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test1\";Sheet2!B1:B32767)", Value(9)); 0897 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test*\";Sheet2!B1:B32767)", Value(0)); 0898 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test?\";Sheet2!B1:B32767)", Value(0)); 0899 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test.*\";Sheet2!B1:B32767)", Value(0)); 0900 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test.+\";Sheet2!B1:B32767)", Value(0)); 0901 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\".*est.*1.*\";Sheet2!B1:B32767)", Value(0)); 0902 } 0903 0904 void TestMathFunctions::testSUMIF_WILDCARDS() 0905 { 0906 m_map->calculationSettings()->setUseWildcards(true); 0907 m_map->calculationSettings()->setUseRegularExpressions(false); 0908 0909 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test\";Sheet2!B1:B32767)", Value(7)); 0910 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test*\";Sheet2!B1:B32767)", Value(36)); 0911 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test?\";Sheet2!B1:B32767)", Value(20)); 0912 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test.*\";Sheet2!B1:B32767)", Value(0)); 0913 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test.+\";Sheet2!B1:B32767)", Value(0)); 0914 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\".*est.*1.*\";Sheet2!B1:B32767)", Value(0)); 0915 } 0916 0917 void TestMathFunctions::testSUMIF_REGULAREXPRESSIONS() 0918 { 0919 m_map->calculationSettings()->setUseWildcards(false); 0920 m_map->calculationSettings()->setUseRegularExpressions(true); 0921 0922 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test\";Sheet2!B1:B32767)", Value(7)); 0923 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test*\";Sheet2!B1:B32767)", Value(7)); 0924 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test?\";Sheet2!B1:B32767)", Value(7)); 0925 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test.*\";Sheet2!B1:B32767)", Value(36)); 0926 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test.+\";Sheet2!B1:B32767)", Value(29)); 0927 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\".*est.*1.*\";Sheet2!B1:B32767)", Value(28)); 0928 0929 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\".*\";A1:A32767)", Value(0)); 0930 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\".*\";B1:B32767)", Value(5)); 0931 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\".*\";Sheet2!A1:A32767)", Value(0)); 0932 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\".*\";Sheet2!B1:B32767)", Value(91)); 0933 CHECK_EVAL("=SUMIF(A1:A32767;\".*\";A1:A32767)", Value(0)); 0934 CHECK_EVAL("=SUMIF(B1:B32767;\".+\";B1:B32767)", Value(5)); 0935 } 0936 0937 void TestMathFunctions::testSUMSQ() 0938 { 0939 CHECK_EVAL("SUMSQ(1;2;3)", Value(14)); // Simple sum. 0940 CHECK_EVAL("SUMSQ(TRUE();2;3)", Value(14)); // TRUE() is 1. 0941 CHECK_EVAL("SUMSQ(B4:B5)", Value(13)); // 2*2+3*3 is 13. 0942 } 0943 0944 void TestMathFunctions::testTRUNC() 0945 { 0946 // ODF-tests 0947 CHECK_EVAL("=TRUNC(10.1)", Value(10)); 0948 CHECK_EVAL("=TRUNC(0.5)", Value(0)); 0949 CHECK_EVAL("=TRUNC(1/3;0)", Value(0)); 0950 CHECK_EVAL("=TRUNC(1/3;1)", Value(0.3)); 0951 CHECK_EVAL("=TRUNC(1/3;2)", Value(0.33)); 0952 CHECK_EVAL("=TRUNC(1/3;2.9)", Value(0.33)); 0953 CHECK_EVAL("=TRUNC(5555;-1)", Value(5550)); 0954 CHECK_EVAL("=TRUNC(-1.1)", Value(-1)); 0955 CHECK_EVAL("=TRUNC(-1.5)", Value(-1)); 0956 } 0957 0958 QTEST_MAIN(TestMathFunctions)