File indexing completed on 2024-05-12 16:35:59
0001 /* This file is part of the KDE project 0002 Copyright 2007 Sascha Pfau <MrPeacock@gmail.com> 0003 Copyright 2007 Stefan Nikolaus <stefan.nikolaus@kdemail.net> 0004 Copyright 2006 Ariya Hidayat <ariya@kde.org> 0005 0006 This library is free software; you can redistribute it and/or 0007 modify it under the terms of the GNU Library General Public 0008 License as published by the Free Software Foundation; only 0009 version 2 of the License. 0010 0011 This library is distributed in the hope that it will be useful, 0012 but WITHOUT ANY WARRANTY; without even the implied warranty of 0013 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 0014 Library General Public License for more details. 0015 0016 You should have received a copy of the GNU Library General Public License 0017 along with this library; see the file COPYING.LIB. If not, write to 0018 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, 0019 Boston, MA 02110-1301, USA. 0020 */ 0021 0022 #include "TestStatisticalFunctions.h" 0023 0024 #include <math.h> 0025 0026 #include <QTest> 0027 0028 #include <CellStorage.h> 0029 #include <Formula.h> 0030 #include <Map.h> 0031 #include <Sheet.h> 0032 0033 #include "TestKspreadCommon.h" 0034 0035 using namespace Calligra::Sheets; 0036 0037 // NOTE: we do not compare the numbers _exactly_ because it is difficult 0038 // to get one "true correct" expected values for the functions due to: 0039 // - different algorithms among spreadsheet programs 0040 // - precision limitation of floating-point number representation 0041 // - accuracy problem due to propagated error in the implementation 0042 #define CHECK_EVAL(x,y) QCOMPARE(TestDouble(x,y,6),y) 0043 #define CHECK_EVAL_SHORT(x,y) QCOMPARE(TestDouble(x,y,10),y) 0044 #define CHECK_ARRAY(x,y) QCOMPARE(TestArray(x,y,10),true) 0045 #define CHECK_ARRAY_NOSIZE(x,y) QCOMPARE(TestArray(x,y,10,false),true) 0046 #define ROUND(x) (roundf(1e15 * x) / 1e15) 0047 0048 bool TestStatisticalFunctions::TestArray(const QString& formula, const QString& _Array, int accuracy, bool checkSize = true) 0049 { 0050 // define epsilon 0051 double epsilon = DBL_EPSILON * pow(10.0, (double)(accuracy)); 0052 0053 Value Array = evaluate(_Array); 0054 // qDebug()<<"Array = "<<Array; 0055 0056 Value result = evaluate(formula); 0057 0058 // test match size 0059 if (checkSize) 0060 if (Array.rows() != result.rows() || Array.columns() != result.columns()) { 0061 qDebug() << "Array size do not match"; 0062 return false; 0063 } 0064 0065 // if checkSize is disabled the count of Array array could be lower than result array 0066 for (int e = 0; e < (int)Array.count(); e++) { 0067 qDebug() << "check element (" << e << ") " << (double)Array.element(e).asFloat() << " " << (double)result.element(e).asFloat(); 0068 bool res = (long double) fabsl(Array.element(e).asFloat() - result.element(e).asFloat()) < epsilon; 0069 if (!res) { 0070 qDebug() << "check failed -->" << "Element(" << e << ") " << (double)Array.element(e).asFloat() << " to" << (double) result.element(e).asFloat() << " diff =" << (double)(Array.element(e).asFloat() - result.element(e).asFloat()); 0071 return false; 0072 } 0073 } 0074 // test passed 0075 return true; 0076 } 0077 0078 Value TestStatisticalFunctions::TestDouble(const QString& formula, const Value& v2, int accuracy) 0079 { 0080 double epsilon = DBL_EPSILON * pow(10.0, (double)(accuracy)); 0081 0082 Formula f(m_map->sheet(0)); // bind to test case data set 0083 QString expr = formula; 0084 if (expr[0] != '=') 0085 expr.prepend('='); 0086 f.setExpression(expr); 0087 Value result = f.eval(); 0088 0089 bool res = fabs(v2.asFloat() - result.asFloat()) < epsilon; 0090 0091 if (!res) 0092 qDebug() << "check failed -->" << "Epsilon =" << epsilon << "" << (double)v2.asFloat() << " to" << (double)result.asFloat() << " diff =" << (double)(v2.asFloat() - result.asFloat()); 0093 // else 0094 // qDebug()<<"check -->" <<" diff =" << v2.asFloat()-result.asFloat(); 0095 if (res) 0096 return v2; 0097 else 0098 return result; 0099 } 0100 0101 // round to get at most 15-digits number 0102 static Value RoundNumber(const Value& v) 0103 { 0104 if (v.isNumber()) { 0105 double d = numToDouble(v.asFloat()); 0106 if (fabs(d) < DBL_EPSILON) 0107 d = 0.0; 0108 return Value(ROUND(d)); 0109 } else 0110 return v; 0111 } 0112 0113 Value TestStatisticalFunctions::evaluate(const QString& formula) 0114 { 0115 Formula f(m_map->sheet(0)); 0116 QString expr = formula; 0117 if (expr[0] != '=') 0118 expr.prepend('='); 0119 f.setExpression(expr); 0120 Value result = f.eval(); 0121 0122 #if 0 0123 // this magically generates the CHECKs 0124 printf(" CHECK_EVAL( \"%s\", %15g) );\n", qPrintable(formula), result.asFloat()); 0125 #endif 0126 0127 return RoundNumber(result); 0128 } 0129 0130 void TestStatisticalFunctions::initTestCase() 0131 { 0132 FunctionModuleRegistry::instance()->loadFunctionModules(); 0133 m_map = new Map(0 /*no Doc*/); 0134 m_map->addNewSheet(); 0135 Sheet* sheet = m_map->sheet(0); 0136 CellStorage* storage = sheet->cellStorage(); 0137 0138 // 0139 // Test case data set 0140 // 0141 0142 0143 // A19:A29 0144 storage->setValue(1, 19, Value(1)); 0145 storage->setValue(1, 20, Value(2)); 0146 storage->setValue(1, 21, Value(4)); 0147 storage->setValue(1, 22, Value(8)); 0148 storage->setValue(1, 23, Value(16)); 0149 storage->setValue(1, 24, Value(32)); 0150 storage->setValue(1, 25, Value(64)); 0151 storage->setValue(1, 26, Value(128)); 0152 storage->setValue(1, 27, Value(256)); 0153 storage->setValue(1, 28, Value(512)); 0154 storage->setValue(1, 29, Value(1024)); 0155 storage->setValue(1, 30, Value(2048)); 0156 storage->setValue(1, 31, Value(4096)); 0157 0158 0159 // B3:B17 0160 storage->setValue(2, 3, Value("7")); 0161 storage->setValue(2, 4, Value(2)); 0162 storage->setValue(2, 5, Value(3)); 0163 storage->setValue(2, 6, Value(true)); 0164 storage->setValue(2, 7, Value("Hello")); 0165 // B8 leave empty 0166 storage->setValue(2, 9, Value::errorDIV0()); 0167 storage->setValue(2, 10, Value(0)); 0168 storage->setValue(2, 11, Value(3)); 0169 storage->setValue(2, 12, Value(4)); 0170 storage->setValue(2, 13, Value("2005-0131T01:00:00")); 0171 storage->setValue(2, 14, Value(1)); 0172 storage->setValue(2, 15, Value(2)); 0173 storage->setValue(2, 16, Value(3)); 0174 storage->setValue(2, 17, Value(4)); 0175 CHECK_EVAL("AVEDEV(1;2;3;4)", Value(1)); 0176 0177 // C4:C6 0178 storage->setValue(3, 4, Value(4)); 0179 storage->setValue(3, 5, Value(5)); 0180 storage->setValue(3, 6, Value(7)); 0181 // C11:C17 0182 storage->setValue(3, 11, Value(5)); 0183 storage->setValue(3, 12, Value(6)); 0184 storage->setValue(3, 13, Value(8)); 0185 storage->setValue(3, 14, Value(4)); 0186 storage->setValue(3, 15, Value(3)); 0187 storage->setValue(3, 16, Value(2)); 0188 storage->setValue(3, 17, Value(1)); 0189 // C19:C31 0190 storage->setValue(3, 19, Value(0)); 0191 storage->setValue(3, 20, Value(5)); 0192 storage->setValue(3, 21, Value(2)); 0193 storage->setValue(3, 22, Value(5)); 0194 storage->setValue(3, 23, Value(3)); 0195 storage->setValue(3, 24, Value(4)); 0196 storage->setValue(3, 25, Value(4)); 0197 storage->setValue(3, 26, Value(0)); 0198 storage->setValue(3, 27, Value(8)); 0199 storage->setValue(3, 28, Value(1)); 0200 storage->setValue(3, 29, Value(9)); 0201 storage->setValue(3, 30, Value(6)); 0202 storage->setValue(3, 31, Value(2)); 0203 // C51:C57 0204 storage->setValue(3, 51, Value(7)); 0205 storage->setValue(3, 52, Value(9)); 0206 storage->setValue(3, 53, Value(11)); 0207 storage->setValue(3, 54, Value(12)); 0208 storage->setValue(3, 55, Value(15)); 0209 storage->setValue(3, 56, Value(17)); 0210 storage->setValue(3, 57, Value(19)); 0211 0212 0213 // D51:D57 0214 storage->setValue(4, 51, Value(100)); 0215 storage->setValue(4, 52, Value(105)); 0216 storage->setValue(4, 53, Value(104)); 0217 storage->setValue(4, 54, Value(108)); 0218 storage->setValue(4, 55, Value(111)); 0219 storage->setValue(4, 56, Value(120)); 0220 storage->setValue(4, 57, Value(133)); 0221 0222 // F19:F26 0223 storage->setValue(6, 19, Value(20)); 0224 storage->setValue(6, 20, Value(5)); 0225 storage->setValue(6, 21, Value(-20)); 0226 storage->setValue(6, 22, Value(-60)); 0227 storage->setValue(6, 23, Value(75)); 0228 storage->setValue(6, 24, Value(-29)); 0229 storage->setValue(6, 25, Value(20)); 0230 storage->setValue(6, 26, Value(30)); 0231 // F51:F60 0232 storage->setValue(6, 51, Value(3)); 0233 storage->setValue(6, 52, Value(4)); 0234 storage->setValue(6, 53, Value(5)); 0235 storage->setValue(6, 54, Value(2)); 0236 storage->setValue(6, 55, Value(3)); 0237 storage->setValue(6, 56, Value(4)); 0238 storage->setValue(6, 57, Value(5)); 0239 storage->setValue(6, 58, Value(6)); 0240 storage->setValue(6, 59, Value(4)); 0241 storage->setValue(6, 60, Value(7)); 0242 0243 0244 // G51:G60 0245 storage->setValue(7, 51, Value(23)); 0246 storage->setValue(7, 52, Value(24)); 0247 storage->setValue(7, 53, Value(25)); 0248 storage->setValue(7, 54, Value(22)); 0249 storage->setValue(7, 55, Value(23)); 0250 storage->setValue(7, 56, Value(24)); 0251 storage->setValue(7, 57, Value(25)); 0252 storage->setValue(7, 58, Value(26)); 0253 storage->setValue(7, 59, Value(24)); 0254 storage->setValue(7, 60, Value(27)); 0255 0256 // H19:H31 0257 storage->setValue(8, 19, Value("2005-03-12")); 0258 storage->setValue(8, 20, Value("2002-02-03")); 0259 storage->setValue(8, 21, Value("2005-03-08")); 0260 storage->setValue(8, 22, Value("1991-03-27")); 0261 storage->setValue(8, 23, Value("1967-07-05")); 0262 storage->setValue(8, 24, Value("1912-12-23")); 0263 storage->setValue(8, 25, Value("1992-02-06")); 0264 storage->setValue(8, 26, Value("1934-07-04")); 0265 storage->setValue(8, 27, Value("1909-01-08")); 0266 storage->setValue(8, 28, Value("1989-11-28")); 0267 storage->setValue(8, 29, Value("2000-02-22")); 0268 storage->setValue(8, 30, Value("2004-03-29")); 0269 storage->setValue(8, 31, Value("1946-07-13")); 0270 0271 // I19:I31 0272 storage->setValue(9, 19, Value(13)); 0273 storage->setValue(9, 20, Value(12)); 0274 storage->setValue(9, 21, Value(11)); 0275 storage->setValue(9, 22, Value(10)); 0276 storage->setValue(9, 23, Value(9)); 0277 storage->setValue(9, 24, Value(8)); 0278 storage->setValue(9, 25, Value(7)); 0279 storage->setValue(9, 26, Value(6)); 0280 storage->setValue(9, 27, Value(5)); 0281 storage->setValue(9, 28, Value(4)); 0282 storage->setValue(9, 29, Value(3)); 0283 storage->setValue(9, 30, Value(2)); 0284 storage->setValue(9, 31, Value(1)); 0285 } 0286 0287 void TestStatisticalFunctions::testAVEDEV() 0288 { 0289 // ODF-tests 0290 CHECK_EVAL("AVEDEV(1;2;3;4)", Value(1)); // 0291 } 0292 0293 void TestStatisticalFunctions::testAVERAGE() 0294 { 0295 // ODF-tests 0296 CHECK_EVAL("AVERAGE(2; 4)", Value(3)); // 0297 } 0298 0299 void TestStatisticalFunctions::testAVERAGEA() 0300 { 0301 // ODF-tests 0302 CHECK_EVAL("AVERAGEA(2; 4)", Value(3)); // 0303 CHECK_EVAL("AVERAGEA(TRUE(); FALSE(); 5)", Value(2)); // 0304 } 0305 0306 void TestStatisticalFunctions::testBETADIST() 0307 { 0308 // ODF-tests 0309 0310 // Cumulative tests 0311 CHECK_EVAL("BETADIST( 0 ; 3; 4)", Value(0)); // 0312 CHECK_EVAL("BETADIST( 0.5; 3; 4)", Value(0.656250)); // 0313 CHECK_EVAL("BETADIST( 0.9; 4; 3)", Value(0.984150)); // 0314 CHECK_EVAL("BETADIST( 2 ; 3; 4)", Value(1)); // constraints x > b should be 1 if cumulative 0315 CHECK_EVAL("BETADIST(-1 ; 3; 4)", Value(0)); // constraints x < a 0316 0317 CHECK_EVAL_SHORT("BETADIST(1.5;3;4;1;2)", evaluate("BETADIST(0.5;3;4)")); // diff = -2.27021e-09 0318 CHECK_EVAL_SHORT("BETADIST(2;3;4;1;3)", evaluate("BETADIST(0.5;3;4)")); // diff = -2.27021e-09 0319 0320 // last parameter FALSE (non - Cumulative) 0321 CHECK_EVAL("BETADIST( 0 ;3;4;0;1;FALSE())", Value(0)); // 0322 CHECK_EVAL("BETADIST( 0.5;3;4;0;1;FALSE())", Value(0.0005208333)); // 0.000521 0323 CHECK_EVAL("BETADIST( 0.9;4;3;0;1;FALSE())", Value(0.0001215000)); // 0.000122 0324 CHECK_EVAL("BETADIST( 2 ;3;4;0;1;FALSE())", Value(0)); // constraints x > b should be 0 if non-cumulative 0325 CHECK_EVAL("BETADIST(-1 ;3;4;0;1;FALSE())", Value(0)); // constraints x < a 0326 0327 CHECK_EVAL("BETADIST(1.5;3;4;1;2;FALSE())", evaluate("BETADIST(0.5;3;4;0;1;FALSE())")); // 0328 CHECK_EVAL("BETADIST(2 ;3;4;1;3;FALSE())", evaluate("BETADIST(0.5;3;4;0;1;FALSE())")); // 0329 } 0330 0331 void TestStatisticalFunctions::testBETAINV() 0332 { 0333 // ODF-tests 0334 CHECK_EVAL("BETADIST(BETAINV(0;3;4);3;4)", Value(0)); // 0335 CHECK_EVAL("BETADIST(BETAINV(0.1;3;4);3;4)", Value(0.1)); // 0336 CHECK_EVAL("BETADIST(BETAINV(0.3;3;4);3;4)", Value(0.3)); // 0337 CHECK_EVAL("BETADIST(BETAINV(0.5;4;3);4;3)", Value(0.5)); // 0338 CHECK_EVAL("BETADIST(BETAINV(0.7;4;3);4;3)", Value(0.7)); // 0339 CHECK_EVAL("BETADIST(BETAINV(1;3;4);3;4)", Value(1)); // 0340 CHECK_EVAL("BETADIST(BETAINV(0;3;4;1;3);3;4;1;3)", Value(0)); // 0341 CHECK_EVAL("BETADIST(BETAINV(0.1;3;4;1;3);3;4;1;3)", Value(0.1)); // 0342 CHECK_EVAL("BETADIST(BETAINV(0.3;3;4;1;3);3;4;1;3)", Value(0.3)); // 0343 CHECK_EVAL("BETADIST(BETAINV(0.5;4;3;1;3);4;3;1;3)", Value(0.5)); // 0344 CHECK_EVAL("BETADIST(BETAINV(0.7;4;3;1;3);4;3;1;3)", Value(0.7)); // 0345 CHECK_EVAL("BETADIST(BETAINV(1;3;4;1;3);3;4;1;3)", Value(1)); // 0346 } 0347 0348 void TestStatisticalFunctions::testBINOMDIST() 0349 { 0350 // bettersolution.com 0351 CHECK_EVAL("BINOMDIST(10;10; 1 ;0)", Value(1)); // Prob.=100% - all trials successful 0352 CHECK_EVAL("BINOMDIST(9 ; 1; 10 ;0)", Value(0)); // Prob. of -exactly- 9 trials successful is 0 then 0353 CHECK_EVAL("BINOMDIST(10;10; 0.1;1)", Value(1)); // Sum of probabilities of 0..10 hits is 1. 0354 // CHECK_EVAL("BINOMDIST(4 ;10; 0.4;1)", Value( 0.6331032576 ) ); // Some random values. 0355 // my tests 0356 CHECK_EVAL_SHORT("BINOMDIST(4 ;10; 0.4;1)", Value(0.6331032576)); // Some random values. 0357 CHECK_EVAL_SHORT("BINOMDIST(5 ;10; 0.4;1)", Value(0.8337613824)); // Some random values. 0358 CHECK_EVAL_SHORT("BINOMDIST(6 ;10; 0.4;1)", Value(0.9452381184)); // Some random values. 0359 CHECK_EVAL_SHORT("BINOMDIST(4 ;10; 0.2;1)", Value(0.9672065024)); // Some random values. 0360 CHECK_EVAL_SHORT("BINOMDIST(5 ;10; 0.2;1)", Value(0.9936306176)); // Some random values. 0361 CHECK_EVAL_SHORT("BINOMDIST(6 ;10; 0.2;1)", Value(0.9991356416)); // Some random values. 0362 } 0363 0364 void TestStatisticalFunctions::testCHIDIST() 0365 { 0366 // bettersolution.com 0367 CHECK_EVAL("CHIDIST( 18.307;10)", Value(0.0500005892)); // 0368 CHECK_EVAL("CHIDIST( 2;2)", Value(0.3678794412)); // 0369 CHECK_EVAL("CHIDIST( -1;2)", Value(1)); // constraint x<0 TODO EXCEL return #NUM! 0370 // CHECK_EVAL("CHIDIST( 4;\"texr\")", Value::VALUE() ); // TODO 0371 } 0372 0373 void TestStatisticalFunctions::testCONFIDENCE() 0374 { 0375 // ODF-tests 0376 CHECK_EVAL("CONFIDENCE(0.5 ; 1;1)", Value(0.67448975)); // 0377 CHECK_EVAL("CONFIDENCE(0.25; 1;1)", Value(1.1503493804)); // 0378 CHECK_EVAL("CONFIDENCE(0.5 ; 4;1)", Value(2.6979590008)); // Multiplying stddev by X multiplies result by X. 0379 CHECK_EVAL("CONFIDENCE(0.5 ; 1;4)", Value(0.3372448751)); // Multiplying count by X*X divides result by X. 0380 0381 // check constraints 0382 CHECK_EVAL("CONFIDENCE(-0.5; 1;4)", Value::errorNUM()); // 0 < alpha < 1 0383 CHECK_EVAL("CONFIDENCE( 1.5; 1;4)", Value::errorNUM()); // 0 < alpha < 1 0384 CHECK_EVAL("CONFIDENCE( 0.5;-1;4)", Value::errorNUM()); // stddev > 0 0385 CHECK_EVAL("CONFIDENCE( 0.5; 1;0)", Value::errorNUM()); // size >= 1 0386 } 0387 0388 void TestStatisticalFunctions::testCORREL() 0389 { 0390 // Cell | Value Cell | Value 0391 // ------+------ ------+------ 0392 // B14 | 1 C14 | 4 0393 // B15 | 2 C15 | 3 0394 // B16 | 3 C16 | 2 0395 // B17 | 4 C17 | 1 0396 0397 // ODF-tests 0398 CHECK_EVAL("CORREL(B14:B17;B14:B17)", Value(1)); // Perfect positive correlation given identical sequences 0399 CHECK_EVAL("CORREL(B14:B17;C14:C17)", Value(-1)); // Perfect negative correlation given reversed sequences 0400 CHECK_EVAL("CORREL(1;2)", Value::errorNUM()); // Each list must contain at least 2 values 0401 CHECK_EVAL("CORREL(B14:B16;B15:B16)", Value::errorNUM()); // The length of each list must be equal 0402 } 0403 0404 void TestStatisticalFunctions::testCOVAR() 0405 { 0406 // Cell | Value Cell | Value 0407 // ------+------ ------+------ 0408 // B14 | 1 C14 | 4 0409 // B15 | 2 C15 | 3 0410 // B16 | 3 C16 | 2 0411 // B17 | 4 C17 | 1 0412 0413 // ODF-tests 0414 CHECK_EVAL("COVAR(C11:C17;C11:C17)", Value(4.9795918367)); // 0415 CHECK_EVAL("COVAR(B14:B17;C14:C17)", Value(-1.25)); // 0416 CHECK_EVAL("COVAR(B14:B17;C13:C17)", Value::errorNUM()); // TODO should we check for "array sizes don't match" or "value counts" in array?. 0417 } 0418 0419 void TestStatisticalFunctions::testDEVSQ() 0420 { 0421 // ODF-tests 0422 CHECK_EVAL("DEVSQ(4)", Value(0)); // One value - no deviation. 0423 CHECK_EVAL("DEVSQ(5;5;5;5)", Value(0)); // Identical values - no deviation. 0424 CHECK_EVAL("DEVSQ(2;4)", Value(2)); // Each value deviates by 1. 0425 CHECK_EVAL("DEVSQ(-5;5;-1;1)", Value(52)); // Average=0 must work properly. 0426 CHECK_EVAL("DEVSQ(C11:C17)", Value(34.8571428571)); // Test values. 0427 CHECK_EVAL("DEVSQ(B14:B17)", Value(5.00)); // Test values. 0428 CHECK_EVAL("DEVSQ(B14)", Value(0)); // One value - no deviation. 0429 } 0430 0431 // void TestStatisticalFunctions::testDEVSQA() 0432 // { 0433 // // no test available 0434 // } 0435 0436 void TestStatisticalFunctions::testEXPONDIST() 0437 { 0438 // ODF-tests 0439 CHECK_EVAL("EXPONDIST( 1;1;TRUE())", Value(0.6321205588)); // 0440 CHECK_EVAL("EXPONDIST( 2;2;TRUE())", Value(0.9816843611)); // 0441 CHECK_EVAL("EXPONDIST( 0;1;TRUE())", Value(0)); // 0442 CHECK_EVAL("EXPONDIST(-1;1;TRUE())", Value(0)); // constraint x<0 0443 0444 CHECK_EVAL("EXPONDIST( 1;1;FALSE())", Value(0.3678794412)); // 0445 CHECK_EVAL("EXPONDIST( 2;2;FALSE())", Value(0.0366312778)); // 0446 CHECK_EVAL("EXPONDIST( 0;1;FALSE())", Value(1)); // 0447 CHECK_EVAL("EXPONDIST(-1;1;FALSE())", Value(0)); // constraint x<0 0448 0449 // test disabled, because 3rd param. is not opt.! 0450 //CHECK_EVAL("EXPONDIST(1;1)", evaluate("EXPONDIST(1;1;TRUE())") ); 0451 } 0452 0453 void TestStatisticalFunctions::testFDIST() 0454 { 0455 // ODF-tests 0456 0457 // cumulative 0458 CHECK_EVAL("FDIST( 1;4;5)", Value(0.5143428033)); // 0459 CHECK_EVAL("FDIST( 2;5;4)", Value(0.7392019723)); // 0460 CHECK_EVAL("FDIST( 0;4;5)", Value(0)); // 0461 CHECK_EVAL("FDIST(-1;4;5)", Value(0)); // 0462 0463 CHECK_EVAL_SHORT("FDIST( 1;4;5;TRUE())", evaluate("FDIST(1;4;5)")); // diff = -1.39644e-09 0464 0465 // non-cumulative 0466 CHECK_EVAL("FDIST( 1;4;5;FALSE())", Value(0.3976140792)); // 0467 CHECK_EVAL("FDIST( 2;5;4;FALSE())", Value(0.1540004108)); // 0468 CHECK_EVAL("FDIST( 0;4;5;FALSE())", Value(0)); // 0469 CHECK_EVAL("FDIST(-1;4;5;FALSE())", Value(0)); // 0470 } 0471 0472 void TestStatisticalFunctions::testFINV() 0473 { 0474 // ODF-tests 0475 CHECK_EVAL("FDIST(FINV(0.1;3;4);3;4)", Value(0.1)); // 0476 CHECK_EVAL("FDIST(FINV(0.3;3;4);3;4)", Value(0.3)); // 0477 CHECK_EVAL("FDIST(FINV(0.5;3;4);3;4)", Value(0.5)); // 0478 CHECK_EVAL("FDIST(FINV(0.7;3;4);3;4)", Value(0.7)); // 0479 CHECK_EVAL("FDIST(FINV(0.0;3;4);3;4)", Value(0.0)); // 0480 } 0481 0482 void TestStatisticalFunctions::testFISHER() 0483 { 0484 // ODF-tests 0485 CHECK_EVAL("FISHER(0)", Value(0)); // Fisher of 0. 0486 CHECK_EVAL("FISHER((EXP(1)-1)/(EXP(1)+1))", Value(0.5)); // Argument chosen so that ln=1 0487 CHECK_EVAL_SHORT("FISHER(0.5)", Value(0.54930614)); // TODO - be more precise - Some random value. 0488 CHECK_EVAL("FISHER(0.47)+FISHER(-0.47)", Value(0)); // Function is symmetrical. 0489 } 0490 0491 void TestStatisticalFunctions::testFISHERINV() 0492 { 0493 // ODF-tests 0494 CHECK_EVAL("FISHERINV(0)", Value(0)); // Fisherinv of 0. 0495 CHECK_EVAL("FISHERINV(LN(2))", Value(0.6)); // e^(2*ln(2))=4 0496 CHECK_EVAL("FISHERINV(FISHER(0.5))", Value(0.5)); // Some random value. 0497 CHECK_EVAL("FISHERINV(0.47)+FISHERINV(-0.47)", Value(0)); // Function is symmetrical. 0498 } 0499 0500 void TestStatisticalFunctions::testFREQUENCY() 0501 { 0502 Value result(Value::Array); 0503 result.setElement(0, 0, Value(3)); 0504 result.setElement(0, 1, Value(2)); 0505 result.setElement(0, 2, Value(4)); 0506 result.setElement(0, 3, Value(1)); 0507 CHECK_EVAL("FREQUENCY({1;2;3;4;5;6;7;8;9;10};{3|5|9})", result); 0508 // the second arg has to be a column vector 0509 CHECK_EVAL("ISERROR(FREQUENCY({1;2;3;4;5;6;7;8;9;10};{3;5;9}))", Value(true)); 0510 // an empty second arg returns the overall number count 0511 CHECK_EVAL("FREQUENCY({1;2;3;4;5;6;7;8;9;10};)", Value(10)); 0512 } 0513 0514 void TestStatisticalFunctions::testFTEST() 0515 { 0516 // TODO - be more precise 0517 // ODF-tests 0518 CHECK_EVAL_SHORT("FTEST(B14:B17; C14:C17)", Value(1.0)); // Same data (second reversed), 0519 CHECK_EVAL_SHORT("FTEST(B14:B15; C13:C14)", Value(0.311916521)); // Significantly different variances, 0520 // so less likely to come from same data set. 0521 } 0522 0523 void TestStatisticalFunctions::testGAMMADIST() 0524 { 0525 // bettersolution.com non-cumulative 0526 CHECK_EVAL("GAMMADIST(10 ;9;2;FALSE())", Value(0.0326390197)); // 0527 0528 // bettersolution.com cumulative 0529 CHECK_EVAL("GAMMADIST(10 ;9;2;TRUE())", Value(0.0680936347)); // 0530 CHECK_EVAL("GAMMADIST(10 ;10;5;TRUE())", Value(0.0000464981)); // Bettersolution = 0 .rounded? 0531 CHECK_EVAL("GAMMADIST(7 ;5;1;TRUE())", Value(0.8270083921)); // TODO NOK / Bettersolution = 1 0532 0533 // bettersolution.com constraints 0534 CHECK_EVAL("GAMMADIST(10 ;9;0;TRUE())", Value::errorNUM()); // beta = 0 not allowed 0535 CHECK_EVAL("GAMMADIST(10 ;-2;2;TRUE())", Value::errorNUM()); // was wird getestet? alpha 0536 CHECK_EVAL("GAMMADIST(-1 ;9;2;TRUE())", Value::errorNUM()); // NOK 0537 CHECK_EVAL("GAMMADIST(7 ;\"text\";1;TRUE())", Value::errorVALUE()); // text not allowed 0538 CHECK_EVAL("GAMMADIST(7 ;5;\"text\";TRUE())", Value::errorVALUE()); // text not allowed 0539 0540 // ODF-tests non-cumulative 0541 CHECK_EVAL("GAMMADIST(0 ;3;4;FALSE())", Value(0)); 0542 CHECK_EVAL("GAMMADIST(0.5;3;4;FALSE())", Value(0.0017236268)); // 0543 CHECK_EVAL("GAMMADIST(9 ;4;3;FALSE())", Value(0.0746806026)); // ODF-Specs -> 0.0666979468 should be 0,0746 0544 CHECK_EVAL("GAMMADIST(0 ;3;4;FALSE())", Value(0)); 0545 CHECK_EVAL("GAMMADIST(9 ;4;3;FALSE())", Value(0.0746806026)); // TODO check ODF-Specs -> 0.390661 0546 0547 // ODF-tests cumulative 0548 CHECK_EVAL("GAMMADIST(0.5;3;4;TRUE())", Value(0.0002964775)); // 0549 CHECK_EVAL("GAMMADIST(9 ;4;3;TRUE())", Value(0.3527681112)); 0550 CHECK_EVAL("GAMMADIST(-1 ;4;3;TRUE())", Value(0)); // neg. x return always 0 0551 CHECK_EVAL("GAMMADIST(-1 ;3;4;FALSE())", Value(0)); // neg. x return always 0 0552 0553 // various tests cumulative 0554 CHECK_EVAL("GAMMADIST(9 ;9;2;TRUE())", Value(0.0402573125)); // 0555 CHECK_EVAL("GAMMADIST(9 ;8;2;TRUE())", Value(0.0865864716)); // 0556 } 0557 0558 void TestStatisticalFunctions::testGAMMAINV() 0559 { 0560 // ODF-tests 0561 CHECK_EVAL("GAMMADIST(GAMMAINV(0.1;3;4);3;4;1)", Value(0.1)); // 0562 CHECK_EVAL("GAMMADIST(GAMMAINV(0.3;3;4);3;4;1)", Value(0.3)); // 0563 CHECK_EVAL("GAMMADIST(GAMMAINV(0.5;3;4);3;4;1)", Value(0.5)); // 0564 CHECK_EVAL("GAMMADIST(GAMMAINV(0.7;3;4);3;4;1)", Value(0.7)); // 0565 CHECK_EVAL("GAMMADIST(GAMMAINV(0 ;3;4);3;4;1)", Value(0)); // 0566 } 0567 0568 void TestStatisticalFunctions::testGAUSS() 0569 { 0570 // ODF-tests 0571 CHECK_EVAL("GAUSS(0)", Value(0)); // Mean of one value. 0572 CHECK_EVAL("GAUSS(1)", Value(0.341344746)); // Multiple equivalent values. 0573 // my test 0574 CHECK_EVAL("GAUSS(-0.25)", Value(-0.0987063257)); // check neg. values. test for fixes gauss_func 0575 } 0576 0577 void TestStatisticalFunctions::testGROWTH() 0578 { 0579 // constraints 0580 CHECK_EVAL("GROWTH({}; C19:C23; 1)", Value::errorNA()); // empty knownY matrix 0581 CHECK_EVAL("GROWTH({5.0;\"a\"}; C19:C23; 1)", Value::errorNA()); // knownY matrix constains chars 0582 0583 // ODF-tests 0584 CHECK_ARRAY("GROWTH( A19:A23; C19:C23; 1 )", "{2.5198420998}"); // with offset 0585 CHECK_ARRAY("GROWTH( A19:A23; C19:C23; 1; FALSE() )", "{1.4859942891}"); // without offset 0586 0587 // http://www.techonthenet.com/excel/formulas/growth.php 0588 CHECK_ARRAY("GROWTH({4;5;6};{10;20;30};{15;30;45})", "{4.4569483434;6.0409611796;8.1879369384}"); // 0589 CHECK_ARRAY("GROWTH({4;5;6};{10;20;30})", "{4.0273074534;4.9324241487;6.0409611796}"); // 0590 CHECK_ARRAY_NOSIZE("GROWTH({4;5;6})", "{4.0273074534}"); // 0591 } 0592 0593 void TestStatisticalFunctions::testGEOMEAN() 0594 { 0595 // ODF-tests 0596 CHECK_EVAL("GEOMEAN(7)", Value(7)); // Mean of one value. 0597 CHECK_EVAL("GEOMEAN(5;5;5;5)", Value(5)); // Multiple equivalent values. 0598 CHECK_EVAL("GEOMEAN(2;8;2;8)", Value(4)); // Some values. 0599 CHECK_EVAL("GEOMEAN(8;0;8;8;8;8)", Value::errorNUM()); // Error if there is a 0 in the range. 0600 CHECK_EVAL("GEOMEAN(C11)", Value(5)); // One value, range. 0601 CHECK_EVAL("GEOMEAN(C11:C17)", Value(3.4451109418)); // Some values, range. 0602 CHECK_EVAL("GEOMEAN(B14:B17)", Value(2.2133638394)); // Some values, range. 0603 } 0604 0605 void TestStatisticalFunctions::testHARMEAN() 0606 { 0607 // ODF-tests 0608 CHECK_EVAL("HARMEAN(7)", Value(7)); // Mean of one value. 0609 CHECK_EVAL("HARMEAN(4;4;4;4)", Value(4)); // Multiple equivalent values. 0610 CHECK_EVAL("HARMEAN(2;4;4)", Value(3)); // Some values. 0611 CHECK_EVAL("HARMEAN(8;0;8;8;8;8)", Value::errorNUM()); // Error if there is a 0 in the range. 0612 CHECK_EVAL("HARMEAN(C11)", Value(5)); // One value, range. 0613 CHECK_EVAL("HARMEAN(C11:C17)", Value(2.7184466019)); // Some values, range. 0614 CHECK_EVAL("HARMEAN(B14:B17)", Value(1.92)); // Some values, range. 0615 } 0616 0617 void TestStatisticalFunctions::testHYPGEOMDIST() 0618 { 0619 // ODF-tests 0620 CHECK_EVAL("HYPGEOMDIST( 2 ;3;3;6;FALSE())", Value(0.45)); // If an urn contains 3 red balls and 3 green balls, the probability 0621 // that 2 red balls will be selected after 3 selections without replacement. 0622 // (0.45=27/60). 0623 CHECK_EVAL("HYPGEOMDIST( 2 ;3;3;6)", Value(0.45)); // The default for cumulative is FALSE(). 0624 CHECK_EVAL("HYPGEOMDIST( 0 ;3;3;6)", Value(0.05)); // There is a small (5%) chance of selecting only green balls. 0625 CHECK_EVAL("HYPGEOMDIST( 2 ;3;3;6;TRUE())", Value(0.95)); // The probability of selecting at most two red balls (i.e 0, 1 or 2). 0626 CHECK_EVAL("HYPGEOMDIST( 4 ;3;3;6)", Value::errorNUM()); // X must be <= M 0627 CHECK_EVAL("HYPGEOMDIST( 2.8;3;3;6)", Value(0.45)); // Non-integers are truncated. 0628 CHECK_EVAL("HYPGEOMDIST(-2 ;3;3;6)", Value::errorNUM()); // Values must be >= 0. 0629 CHECK_EVAL("HYPGEOMDIST( 0 ;0;0;0)", Value(1)); // 0630 } 0631 0632 void TestStatisticalFunctions::testINTERCEPT() 0633 { 0634 // bettersolution.com 0635 CHECK_EVAL_SHORT("INTERCEPT({2;3;9;1;8};{6;5;11;7;5})", Value(0.048387097)); // TODO - be more precise 0636 // CHECK_EVAL_SHORT("INTERCEPT({2;4;6};{6;3;8})", Value( 2.21053 ) ); // TODO - be more precise 0637 CHECK_EVAL("INTERCEPT({2;3;9};{6;5;11;7;5})", Value::errorNUM()); // 0638 CHECK_EVAL("INTERCEPT(\"text\";{6;5;11;7;5})", Value::errorNUM()); // text is not allowed 0639 } 0640 0641 void TestStatisticalFunctions::testKURT() 0642 { 0643 // TODO check function 0644 0645 // ODF-tests 0646 CHECK_EVAL("KURT(C20:C25)", Value(-0.446162998)); // 0647 CHECK_EVAL("KURT(C20:C23;4;4)", Value(-0.446162998)); // 0648 } 0649 0650 void TestStatisticalFunctions::testLARGE() 0651 { 0652 // Cell | Value | N'th 0653 // ------+-------+------ 0654 // B14 | 1 | 3 0655 // B15 | 2 | 2 0656 // B16 | 3 | 1 0657 0658 // ODF-tests 0659 CHECK_EVAL("LARGE(B14:B16;1)", Value(3)); // 0660 CHECK_EVAL("LARGE(B14:B16;3)", Value(1)); // 0661 CHECK_EVAL("LARGE(B14:B16;4)", Value::errorNUM()); // N is greater than the length of the list 0662 } 0663 0664 void TestStatisticalFunctions::testLEGACYCHIDIST() 0665 { 0666 // ODF-tests LEGACY.CHIDIST 0667 CHECK_EVAL("LEGACYCHIDIST(-1;2)", Value(1)); // constraint x<0 0668 CHECK_EVAL("LEGACYCHIDIST( 0;2)", Value(1)); // constraint x=0 0669 CHECK_EVAL("LEGACYCHIDIST( 2;2)", Value(0.3678794412)); // 0670 CHECK_EVAL("LEGACYCHIDIST( 4;4)", Value(0.4060058497)); // 0671 } 0672 0673 void TestStatisticalFunctions::testLEGACYCHIINV() 0674 { 0675 // ODF-tests LEGACY.CHIINV 0676 CHECK_EVAL("LEGACYCHIDIST(LEGACYCHIINV(0.1;3);3)", Value(0.1)); // 0677 CHECK_EVAL("LEGACYCHIDIST(LEGACYCHIINV(0.3;3);3)", Value(0.3)); // 0678 CHECK_EVAL("LEGACYCHIDIST(LEGACYCHIINV(0.5;3);3)", Value(0.5)); // 0679 CHECK_EVAL("LEGACYCHIDIST(LEGACYCHIINV(0.7;3);3)", Value(0.7)); // 0680 CHECK_EVAL("LEGACYCHIDIST(LEGACYCHIINV(0.9;3);3)", Value(0.9)); // 0681 CHECK_EVAL("LEGACYCHIDIST(LEGACYCHIINV(0.1;20);20)", Value(0.1)); // 0682 CHECK_EVAL("LEGACYCHIDIST(LEGACYCHIINV(0.3;20);20)", Value(0.3)); // 0683 CHECK_EVAL("LEGACYCHIDIST(LEGACYCHIINV(0.5;20);20)", Value(0.5)); // 0684 CHECK_EVAL("LEGACYCHIDIST(LEGACYCHIINV(0.7;20);20)", Value(0.7)); // 0685 CHECK_EVAL("LEGACYCHIDIST(LEGACYCHIINV(0.9;20);20)", Value(0.9)); // 0686 CHECK_EVAL("LEGACYCHIDIST(LEGACYCHIINV(1.0;20);20)", Value(1.0)); // 0687 } 0688 0689 void TestStatisticalFunctions::testLEGACYFDIST() 0690 { 0691 // ODF-tests 0692 CHECK_EVAL("LEGACYFDIST( 1;4;5)", Value(0.4856571967)); // 0693 CHECK_EVAL("LEGACYFDIST( 2;5;4)", Value(0.2607980277)); // 0694 CHECK_EVAL("LEGACYFDIST( 0;4;5)", Value(1)); // 0695 CHECK_EVAL("LEGACYFDIST(-1;4;5)", Value::errorNUM()); // 0696 } 0697 0698 void TestStatisticalFunctions::testLEGACYFINV() 0699 { 0700 // ODF-tests 0701 CHECK_EVAL("LEGACYFDIST(LEGACYFINV(0.1;3;4);3;4)", Value(0.1)); // 0702 CHECK_EVAL("LEGACYFDIST(LEGACYFINV(0.3;3;4);3;4)", Value(0.3)); // 0703 CHECK_EVAL("LEGACYFDIST(LEGACYFINV(0.5;3;4);3;4)", Value(0.5)); // 0704 CHECK_EVAL("LEGACYFDIST(LEGACYFINV(0.7;3;4);3;4)", Value(0.7)); // 0705 CHECK_EVAL("LEGACYFDIST(LEGACYFINV(1.0;3;4);3;4)", Value(1.0)); // 0706 } 0707 0708 void TestStatisticalFunctions::testLOGINV() 0709 { 0710 // TODO check function 0711 0712 // ODF-tests 0713 CHECK_EVAL("LOGNORMDIST(LOGINV(0.1;0;1);0;1;TRUE())", Value(0.1)); // 0714 CHECK_EVAL("LOGNORMDIST(LOGINV(0.3;0;1);0;1;TRUE())", Value(0.3)); // 0715 CHECK_EVAL("LOGNORMDIST(LOGINV(0.5;0;1);0;1;TRUE())", Value(0.5)); // 0716 CHECK_EVAL("LOGNORMDIST(LOGINV(0.7;0;1);0;1;TRUE())", Value(0.7)); // 0717 CHECK_EVAL("LOGNORMDIST(LOGINV(0.9;0;1);0;1;TRUE())", Value(0.9)); // 0718 CHECK_EVAL("LOGNORMDIST(LOGINV(0.1;1;4);1;4;TRUE())", Value(0.1)); // 0719 CHECK_EVAL("LOGNORMDIST(LOGINV(0.3;1;4);1;4;TRUE())", Value(0.3)); // 0720 CHECK_EVAL("LOGNORMDIST(LOGINV(0.5;1;4);1;4;TRUE())", Value(0.5)); // 0721 CHECK_EVAL("LOGNORMDIST(LOGINV(0.7;1;4);1;4;TRUE())", Value(0.7)); // 0722 CHECK_EVAL("LOGNORMDIST(LOGINV(0.9;1;4);1;4;TRUE())", Value(0.9)); // 0723 CHECK_EVAL("LOGINV(0.5)", Value(1)); // 0724 } 0725 0726 void TestStatisticalFunctions::testLOGNORMDIST() 0727 { 0728 // TODO - implement cumulative calculation 0729 // - check definition cumulative/non-cumulative and constraints 0730 0731 // ODF-tests 0732 0733 // cumulative 0734 CHECK_EVAL("LOGNORMDIST(1)", Value(0.5)); // 0735 CHECK_EVAL("LOGNORMDIST(1;1;4)", Value(0.4012936743)); // 0736 CHECK_EVAL("LOGNORMDIST(1;0;1;TRUE())", Value(0.5)); // 0737 CHECK_EVAL("LOGNORMDIST(1;1;4;TRUE())", Value(0.4012936743)); // 0738 CHECK_EVAL("LOGNORMDIST(1;-1;4;TRUE())", Value(0.5987063257)); // 0739 // CHECK_EVAL("LOGNORMDIST(2;-1;4;TRUE())", Value( 0.663957 ) ); // ?????? 0740 CHECK_EVAL("LOGNORMDIST(3;0;1;TRUE())", Value(0.8640313924)); // 0741 CHECK_EVAL("LOGNORMDIST(100;0;1;TRUE())", Value(0.9999979394)); // 0742 CHECK_EVAL("LOGNORMDIST(-1;0;1;TRUE())", Value(0)); // constraint x<0 returns 0 0743 0744 // non-cumulative 0745 // CHECK_EVAL("LOGNORMDIST( 1; 0; 1;FALSE())", Value( 0.398942 ) ); // 0746 // CHECK_EVAL("LOGNORMDIST( 1; 1; 4;FALSE())", Value( 0.096667 ) ); // 0747 // CHECK_EVAL("LOGNORMDIST( 1;-1; 4;FALSE())", Value( 0.096667 ) ); // 0748 // CHECK_EVAL("LOGNORMDIST( 2;-1; 4;FALSE())", Value( 0.045595 ) ); // 0749 // CHECK_EVAL("LOGNORMDIST(-1; 0; 1;FALSE())", Value::errorNUM() ); // constraint failure 0750 // CHECK_EVAL("LOGNORMDIST( 1; 0;-1;FALSE())", Value::errorNUM() ); // constraint failure 0751 } 0752 0753 void TestStatisticalFunctions::testMAX() 0754 { 0755 // Cell | Value Cell | Value 0756 // ------+------ ------+------ 0757 // B3 | "7" C14 | 4 0758 // B4 | 2 C15 | 3 0759 // B5 | 3 C16 | 2 0760 // B6 | true C17 | 1 0761 // B7 | "Hello" 0762 // B8 | 0763 // B9 | DIV/0 0764 0765 0766 // ODF-tests 0767 CHECK_EVAL("MAX(2;4;1;-8)", Value(4)); // Negative numbers are smaller than positive numbers. 0768 CHECK_EVAL("MAX(B4:B5)", Value(3)); // The maximum of (2,3) is 3. 0769 // CHECK_EVAL("ISNA(MAXA(NA())", Value(true)); // nline errors are propagated. 0770 CHECK_EVAL("MAX(B3:B5)", Value(3)); // Strings are not converted to numbers and are ignored. 0771 CHECK_EVAL("MAX(-1;B7)", Value(-1)); // Strings are not converted to numbers and are ignored. 0772 CHECK_EVAL("MAX(B3:B9)", Value::errorVALUE()); // TODO check function - Errors inside ranges are NOT ignored. 0773 } 0774 0775 void TestStatisticalFunctions::testMAXA() 0776 { 0777 // ODF-tests 0778 CHECK_EVAL("MAXA(2;4;1;-8)", Value(4)); // Negative numbers are smaller than positive numbers. 0779 CHECK_EVAL("MAXA(B4:B5)", Value(3)); // The maximum of (2,3) is 3. 0780 // CHECK_EVAL("ISNA(MAXA(NA())", Value(true)); // Inline errors are propagated. 0781 0782 // TODO check function - inline Text must be converted, but not Text in Cells 0783 // CHECK_EVAL("MAXA(B3:B5)", Value( 3 ) ); // Cell text is converted to 0. 0784 0785 CHECK_EVAL("MAXA(-1;B7)", Value(0)); // Cell text is converted to 0. 0786 CHECK_EVAL("MAXA(\"a\")", Value::errorVALUE()); // Text inline is NOT ignored. 0787 CHECK_EVAL("MAXA(B3:B9)", Value::errorVALUE()); // TODO check function - Errors inside ranges are NOT ignored. 0788 CHECK_EVAL("MAXA(B6:B7)", Value(1)); // Logicals are considered numbers. 0789 } 0790 0791 void TestStatisticalFunctions::testMEDIAN() 0792 { 0793 // ODF-tests 0794 CHECK_EVAL("=MEDIAN(10.5;7.2)", Value(8.85)); 0795 CHECK_EVAL("=MEDIAN(7.2;200;5.4;45)", Value(26.1)); 0796 CHECK_EVAL("=MEDIAN(7.2;200;5.4;8.1)", Value(7.65)); 0797 CHECK_EVAL("=MEDIAN(1;3;13;14;15)", Value(13.0)); 0798 CHECK_EVAL("=MEDIAN(1;3;13;14;15;35)", Value(13.5)); 0799 // Bug 148574: MEDIAN function gives incorrect results 0800 CHECK_EVAL("=MEDIAN(1;2;3)", Value(2)); 0801 CHECK_EVAL("=MEDIAN(1;2;3;4;5)", Value(3)); 0802 } 0803 0804 void TestStatisticalFunctions::testMIN() 0805 { 0806 // ODF-tests 0807 CHECK_EVAL("MIN(2;4;1;-8)", Value(-8)); // Negative numbers are smaller than positive numbers. 0808 CHECK_EVAL("MIN(B4:B5)", Value(2)); // The minimum of (2,3) is 2. 0809 CHECK_EVAL("MIN(B3)", Value(0)); // If no numbers are provided in all ranges, MIN returns 0 0810 CHECK_EVAL("MIN(\"a\")", Value::errorNUM()); // Non-numbers inline are NOT ignored. 0811 CHECK_EVAL("MIN(B3:B5)", Value(2)); // Cell text is not converted to numbers and is ignored. 0812 } 0813 0814 void TestStatisticalFunctions::testMINA() 0815 { 0816 // ODF-tests 0817 CHECK_EVAL("MINA(2;4;1;-8)", Value(-8)); // Negative numbers are smaller than positive numbers. 0818 CHECK_EVAL("MINA(B4:B5)", Value(2)); // The minimum of (2,3) is 2. 0819 CHECK_EVAL("MINA(1;B7)", Value(0)); // Cell text is converted to 0. 0820 CHECK_EVAL("MINA(\"a\")", Value::errorNUM()); // Cell text inline is NOT ignored. 0821 0822 // TODO check function - inline Text must be converted, but not Text in Cells 0823 // CHECK_EVAL("MINA(B3:B5)", Value( 0 ) ); // Cell text is converted to 0. 0824 0825 CHECK_EVAL("MINA(B6:C6)", Value(1)); // The value "True" is considered equivalent to 1. 0826 } 0827 0828 void TestStatisticalFunctions::testMODE() 0829 { 0830 // ODF-tests 0831 CHECK_EVAL("MODE(F51:F60)", Value(4)); // 0832 CHECK_EVAL("MODE(G51;G52;G53;G54;G55;G56;G57;G58;G59;G60)", Value(24)); // 0833 CHECK_EVAL("MODE(1;2;3;4;5;6;7;8;9;10)", Value::errorNUM()); // 0834 } 0835 0836 void TestStatisticalFunctions::testNEGBINOMDIST() 0837 { 0838 // ODF-test 0839 // CHECK_EVAL("NEGBINOMDIST(F20;I29;H6)", Value( 0.000130947 ) ); // 0840 0841 // bettersolutions.com 0842 CHECK_EVAL("NEGBINOMDIST( 0;1; 0.25)", Value(0.25)); // 0843 CHECK_EVAL("NEGBINOMDIST( 0;1; 0.5)", Value(0.5)); // 0844 CHECK_EVAL("NEGBINOMDIST( 1;6; 0.5)", Value(0.046875)); // 0845 CHECK_EVAL("NEGBINOMDIST(10;5; 0.25)", Value(0.0550486604)); // 0846 CHECK_EVAL("NEGBINOMDIST(10;5;-4)", Value::errorNUM()); // 0847 // CHECK_EVAL("NEGBINOMDIST(10;"text";0.25)", Value::NUM() ); // 0848 } 0849 0850 void TestStatisticalFunctions::testNORMDIST() 0851 { 0852 // ODF-tests 0853 CHECK_EVAL("NORMDIST(0;1;4;TRUE())", Value(0.4012936743)); // 0854 CHECK_EVAL("NORMDIST(0;0;1;FALSE())", Value(0.3989422804)); // 0855 CHECK_EVAL("NORMDIST(0;0;1;TRUE())", Value(0.5)); // 0856 CHECK_EVAL("NORMDIST(0;1;4;FALSE())", Value(0.0966670292)); // 0857 CHECK_EVAL("NORMDIST(0;-1;4;FALSE())", Value(0.0966670292)); // 0858 CHECK_EVAL("NORMDIST(0;-1;4;TRUE())", Value(0.5987063257)); // 0859 CHECK_EVAL("NORMDIST(1;-1;4;FALSE())", Value(0.0880163317)); // 0860 CHECK_EVAL("NORMDIST(1;-1;4;TRUE())", Value(0.6914624613)); // 0861 CHECK_EVAL("NORMDIST(1.281552;0;1;TRUE())", Value(0.9000000762)); // 0862 CHECK_EVAL("NORMDIST(0;-1.281552;1;TRUE())", Value(0.9000000762)); // 0863 CHECK_EVAL("NORMDIST(0;0;-1;FALSE())", Value::errorNUM()); // 0864 } 0865 0866 void TestStatisticalFunctions::testNORMINV() 0867 { 0868 // ODF-tests 0869 CHECK_EVAL("NORMDIST(NORMINV(0.1;0;1);0;1;TRUE())", Value(0.1)); // 0870 CHECK_EVAL("NORMDIST(NORMINV(0.3;0;1);0;1;TRUE())", Value(0.3)); // 0871 CHECK_EVAL("NORMDIST(NORMINV(0.5;0;1);0;1;TRUE())", Value(0.5)); // 0872 CHECK_EVAL("NORMDIST(NORMINV(0.7;0;1);0;1;TRUE())", Value(0.7)); // 0873 CHECK_EVAL("NORMDIST(NORMINV(0.9;0;1);0;1;TRUE())", Value(0.9)); // 0874 CHECK_EVAL("NORMDIST(NORMINV(0.1;1;4);1;4;TRUE())", Value(0.1)); // 0875 CHECK_EVAL("NORMDIST(NORMINV(0.3;1;4);1;4;TRUE())", Value(0.3)); // 0876 CHECK_EVAL("NORMDIST(NORMINV(0.5;1;4);1;4;TRUE())", Value(0.5)); // 0877 CHECK_EVAL("NORMDIST(NORMINV(0.7;1;4);1;4;TRUE())", Value(0.7)); // 0878 CHECK_EVAL("NORMDIST(NORMINV(0.9;1;4);1;4;TRUE())", Value(0.9)); // 0879 } 0880 0881 void TestStatisticalFunctions::testPEARSON() 0882 { 0883 // Cell | Value Cell | Value Cell | Value Cell | Value 0884 // ------+------- ------+------- ------+------- ------+------- 0885 // A19 | 1 C19 | 0 C51 | 7 D51 | 100 0886 // A20 | 2 C20 | 5 C51 | 9 D52 | 105 0887 // A21 | 4 C21 | 2 C53 | 11 D53 | 104 0888 // A22 | 8 C22 | 5 C54 | 12 D54 | 108 0889 // A23 | 16 C23 | 3 C55 | 15 D55 | 111 0890 // A24 | 32 C24 | 4 C56 | 17 D56 | 120 0891 // A25 | 64 C25 | 4 C57 | 19 D57 | 133 0892 // A26 | 128 C26 | 0 0893 // A27 | 256 C27 | 8 0894 // A28 | 512 C28 | 1 0895 // A29 | 1024 C29 | 9 0896 // A30 | 2048 C30 | 6 0897 // A31 | 4096 C31 | 2 0898 0899 // ODF-tests 0900 CHECK_EVAL_SHORT("PEARSON(A19:A31;C19:C31)", Value(0.045989147)); // 0901 CHECK_EVAL_SHORT("PEARSON(C51:C57;D51:D57)", Value(0.930164207)); // 0902 CHECK_EVAL("PEARSON(C51:C57;D51:D56)", Value::errorNUM()); // 0903 } 0904 0905 void TestStatisticalFunctions::testPERCENTILE() 0906 { 0907 // ODF-tests 0908 CHECK_EVAL("PERCENTILE(A19:A31;0.38)", Value(24.96)); // 0909 CHECK_EVAL("PERCENTILE(A19:A31;0.95)", Value(2867.2)); // 0910 CHECK_EVAL("PERCENTILE(A19:A31;0.05)", Value(1.6)); // 0911 0912 // my tests 0913 CHECK_EVAL("PERCENTILE(A10:A15;-0.1)", Value::errorVALUE()); // 0914 CHECK_EVAL("PERCENTILE(A19:A25;1.1)", Value::errorVALUE()); // 0915 0916 } 0917 0918 void TestStatisticalFunctions::testPERMUT() 0919 { 0920 // ODF-tests 0921 CHECK_EVAL("PERMUT(2;2)", Value(2)); // =2!/(2-2)! 0922 CHECK_EVAL("PERMUT(4;2)", Value(12)); // =4!/(4-2)! 0923 CHECK_EVAL("PERMUT(4.3;2.1)", Value(12)); // =PERMUT(4;2) 0924 CHECK_EVAL("PERMUT(-4;2)", Value::errorNUM()); // 0925 CHECK_EVAL("PERMUT(4;-2)", Value::errorNUM()); // 0926 } 0927 0928 void TestStatisticalFunctions::testPERMUTATIONA() 0929 { 0930 // ODF-tests 0931 CHECK_EVAL("PERMUTATIONA(64;2)", Value(4096)); // 0932 CHECK_EVAL("PERMUTATIONA(6;3)", Value(216)); // 0933 0934 // my tests 0935 CHECK_EVAL("PERMUTATIONA(0;0)", Value(1)); // 0936 CHECK_EVAL("PERMUTATIONA(-4;2)", Value::errorNUM()); // 0937 CHECK_EVAL("PERMUTATIONA(4;-2)", Value::errorNUM()); // 0938 } 0939 0940 void TestStatisticalFunctions::testPHI() 0941 { 0942 // Cell | Value 0943 // ------+------- 0944 // C23 | 3 0945 // | 0946 0947 // ODF-tests 0948 CHECK_EVAL_SHORT("PHI(C23/10)", Value(0.381387815)); // TODO - be more precise / 0949 CHECK_EVAL_SHORT("PHI(-C23/10)", Value(0.381387815)); // TODO - be more precise / 0950 CHECK_EVAL_SHORT("PHI(0)", Value(0.398942280)); // TODO - be more precise / 0951 } 0952 0953 void TestStatisticalFunctions::testPOISSON() 0954 { 0955 // ODF-tests 0956 CHECK_EVAL_SHORT("POISSON(0;1;FALSE())", Value(0.367880)); // TODO - be more precise / 0957 CHECK_EVAL_SHORT("POISSON(0;2;FALSE())", Value(0.135335)); // TODO - be more precise / 0958 } 0959 0960 void TestStatisticalFunctions::testRANK() 0961 { 0962 // Cell | Value 0963 // ------+------ 0964 // A19 | 1 0965 // A20 | 2 0966 // A21 | 4 0967 // A22 | 8 0968 // A23 | 16 0969 // A24 | 32 0970 // A25 | 64 0971 0972 // ODF-tests 0973 CHECK_EVAL("RANK(A20;A19:A25;1)", Value(2)); // ascending 0974 CHECK_EVAL("RANK(A25;A19:A25;0)", Value(1)); // descending 0975 CHECK_EVAL("RANK(A21;A19:A25 )", Value(5)); // omitted equals descending order 0976 } 0977 0978 void TestStatisticalFunctions::testRSQ() 0979 { 0980 // ODF-tests 0981 CHECK_EVAL("RSQ(H19:H31;I19:I31)", Value(0.075215010)); // 0982 CHECK_EVAL("RSQ(H19:H31;I19:I30)", Value::errorNA()); // array does not have the same size 0983 } 0984 0985 void TestStatisticalFunctions::testQUARTILE() 0986 { 0987 // flag: 0988 // 0 equals MIN() 0989 // 1 25th percentile 0990 // 2 50th percentile equals MEDIAN() 0991 // 3 75th percentile 0992 // 4 equals MAX() 0993 0994 // ODF-tests 0995 CHECK_EVAL("QUARTILE(A19:A25;3)", Value(24)); // 0996 CHECK_EVAL("QUARTILE(F19:F26;1)", Value(-22.25)); // 0997 CHECK_EVAL("QUARTILE(A10:A15;2)", Value::errorVALUE()); // 0998 CHECK_EVAL("QUARTILE(A19:A25;5)", Value::errorVALUE()); // flag > 4 0999 CHECK_EVAL("QUARTILE(F19:F26;1.5)", Value(-22.25)); // 1.5 rounded down to 1 1000 CHECK_EVAL("QUARTILE({1;2;4;8;16;32;64};3)", Value(24)); // 1001 1002 // my tests 1003 CHECK_EVAL("QUARTILE(A19:A25;0)", Value(1)); // MIN() 1004 CHECK_EVAL("QUARTILE(A19:A25;4)", Value(64)); // MAX() 1005 1006 } 1007 1008 void TestStatisticalFunctions::testSKEW() 1009 { 1010 // ODF-tests 1011 CHECK_EVAL_SHORT("SKEW( 1; 2; 4 )", Value(0.935219)); // TODO - be more precise / Expectation value: 2.333333 1012 // Standard deviation: 1.257525 1013 // Third central moment: 0.740741 1014 CHECK_EVAL_SHORT("SKEW(A19:A23)", Value(1.325315)); // TODO - be more precise / 1015 CHECK_EVAL("SKEW( 1; 2 )", Value::errorNUM()); // At least three numbers. 1016 } 1017 1018 void TestStatisticalFunctions::testSKEWP() 1019 { 1020 // ODF-tests 1021 CHECK_EVAL_SHORT("SKEWP( 1; 2; 4 )", Value(0.381802)); // TODO - be more precise / Expectation value: 2.333333 1022 // Standard deviation: 1.247219 1023 // Third central moment: 0.740741 1024 CHECK_EVAL_SHORT("SKEWP(A19:A23)", Value(0.889048)); // TODO - be more precise / 1025 CHECK_EVAL("SKEW( 1; 2 )", Value::errorNUM()); // At least three numbers. 1026 } 1027 1028 void TestStatisticalFunctions::testSLOPE() 1029 { 1030 // ODF-tests 1031 CHECK_EVAL("SLOPE(B4:B5;C4:C5)", Value(1)); // 1032 CHECK_EVAL_SHORT("SLOPE(A19:A24;A26:A31)", Value(0.007813)); // TODO - be more precise / 1033 } 1034 1035 void TestStatisticalFunctions::testSMALL() 1036 { 1037 // ODF-tests 1038 CHECK_EVAL("SMALL(B14:B16;1)", Value(1)); // 1039 CHECK_EVAL("SMALL(B14:B16;3)", Value(3)); // 1040 CHECK_EVAL("SMALL(B14:B16;4)", Value::errorNUM()); // N is greater than the length of the list 1041 } 1042 1043 void TestStatisticalFunctions::testSTANDARDIZE() 1044 { 1045 // ODF-tests 1046 CHECK_EVAL("STANDARDIZE( 1; 2.5; 0.1 )", Value(-15)); // 1047 CHECK_EVAL("STANDARDIZE( -1; -2; 2 )", Value(0.5)); // 1048 CHECK_EVAL("STANDARDIZE( 1; 1; 0 )", Value::errorNUM()); // N is greater than the length of the list 1049 } 1050 1051 void TestStatisticalFunctions::testSTDEV() 1052 { 1053 // ODF-tests 1054 CHECK_EVAL("STDEV(2;4)/SQRT(2)", Value(1)); // The sample standard deviation of (2;4) is SQRT(2). 1055 CHECK_EVAL("STDEV(B4:B5)*SQRT(2)", Value(1)); // The sample standard deviation of (2;3) is 1/SQRT(2). 1056 CHECK_EVAL("STDEV(B3:B5)*SQRT(2)", Value(1)); // Strings are not converted to numbers and are ignored. 1057 CHECK_EVAL("STDEV({10000000001;10000000002;" 1058 "10000000003;10000000004;10000000005;" 1059 "10000000006;10000000007;10000000008;" 1060 "10000000009;10000000010})", Value(3.0276503541)); // Ensure that implementations use a reasonably stable way of calculating STDEV. 1061 CHECK_EVAL("STDEV(1)", Value::errorNUM()); // At least two numbers must be included 1062 } 1063 1064 void TestStatisticalFunctions::testSTDEVA() 1065 { 1066 // ODF-tests 1067 CHECK_EVAL("STDEVA(2;4)/SQRT(2)", Value(1)); // The sample standard deviation of (2;4) is SQRT(2). 1068 CHECK_EVAL_SHORT("STDEVA(B5:C6)", Value(2.581989)); // TODO - be more precise / Logicals (referenced) are converted to numbers. 1069 CHECK_EVAL_SHORT("STDEVA( TRUE();FALSE() )", Value(0.707107)); // TODO - be more precise / Logicals (inlined) are converted to numbers. 1070 CHECK_EVAL("STDEVA(1)", Value::errorNUM()); // Logicals (inlined) are converted to numbers. 1071 } 1072 1073 void TestStatisticalFunctions::testSTDEVP() 1074 { 1075 // ODF-tests 1076 CHECK_EVAL("STDEVP(2;4)", Value(1)); // The standard deviation of the set for (2;4) is 1. 1077 CHECK_EVAL("STDEVP(B4:B5)*2", Value(1)); // The standard deviation of the set for (2;3) is 0.5. 1078 CHECK_EVAL("STDEVP(B3:B5)*2", Value(1)); // Strings are not converted to numbers and are ignored. 1079 CHECK_EVAL("STDEVP(1)", Value(0)); // STDEVP(1) is 0. 1080 } 1081 1082 void TestStatisticalFunctions::testSTDEVPA() 1083 { 1084 // ODF-tests 1085 CHECK_EVAL("STDEVPA(2;4)", Value(1)); // The sample standard deviation of (2;4) is 1. 1086 CHECK_EVAL_SHORT("STDEVPA(B5:C6)", Value(2.236068)); // TODO - be more precise / Logicals (referenced) are converted to numbers. 1087 CHECK_EVAL("STDEVPA(TRUE();FALSE())", Value(0.5)); // Logicals (inlined) are converted to numbers. 1088 } 1089 1090 void TestStatisticalFunctions::testSTEYX() 1091 { 1092 // ODF-tests 1093 CHECK_EVAL_SHORT("STEYX(C19:C23;A19:A23)", Value(2.370953)); // TODO - be more precise 1094 CHECK_EVAL("STEYX(A19:A23;A25:A29)", Value(0)); // 1095 CHECK_EVAL("STEYX(B4:B5;C4:C5)", Value::errorNUM()); // at least three number per sequence 1096 } 1097 1098 void TestStatisticalFunctions::testSUMPRODUCT() 1099 { 1100 CHECK_EVAL("SUMPRODUCT(C19:C23;A19:A23)", Value(106)); 1101 CHECK_EVAL("SUMPRODUCT(C19:C23^2;2*A19:A23)", Value(820)); 1102 } 1103 1104 void TestStatisticalFunctions::testTDIST() 1105 { 1106 // mode 1107 // 1 = one tailed distribution 1108 // 2 = two tailed distribution 1109 1110 // ODF-tests 1111 CHECK_EVAL("TDIST( 0.5; 1; 1 )", Value(0.3524163823)); // ODF-specs -> 0.352416 1112 CHECK_EVAL_SHORT("TDIST( -1.5; 2; 2 )", Value(0.272393)); // TODO - be more precise / OOo-2.3.0 returns error!!! 1113 CHECK_EVAL("TDIST( 0.5; 5; 1 )", Value(0.3191494358)); // ODF-specs -> 0.319149 1114 CHECK_EVAL("TDIST( 1; 1; 3 )", Value::errorNUM()); // mode = { 1; 2 } 1115 CHECK_EVAL("TDIST( 1; 0; 1 )", Value::errorNUM()); // degreeOfFreedom >= 1 1116 } 1117 1118 void TestStatisticalFunctions::testTINV() 1119 { 1120 // TODO - be more precise 1121 1122 // ODF-tests 1123 CHECK_EVAL("TINV( 1; 2 )", Value(0)); // p=1 -> t=0 1124 CHECK_EVAL_SHORT("TINV( 0.5; 2 )", Value(0.816497)); // 1125 CHECK_EVAL("TDIST( TINV(0.25;3); 3;2 )", Value(0.25)); // 1126 CHECK_EVAL("TDIST( TINV(0.5 ;3); 3;2 )", Value(0.5)); // 1127 CHECK_EVAL("TDIST( TINV(0.75;3); 3;2 )", Value(0.75)); // 1128 CHECK_EVAL("TDIST( 2; 3 )", Value::errorNUM()); // 0 <= probability <= 1 1129 CHECK_EVAL("TDIST( 1; 0 )", Value::errorNUM()); // degreeOfFreedom >= 1 1130 } 1131 1132 void TestStatisticalFunctions::testTREND() 1133 { 1134 // Cell | Value Cell | Value 1135 // ------+------ ------+------ 1136 // A19 | 1 C19 | 0 1137 // A20 | 2 C20 | 5 1138 // A21 | 4 C21 | 2 1139 // A22 | 8 C22 | 5 1140 // A23 | 16 C23 | 3 1141 1142 CHECK_ARRAY("TREND(A19:A23; C19:C23; 1) ", "{4.7555555555}"); // with offset 1143 CHECK_ARRAY("TREND(A19:A23; C19:C23; 1; 0 ) ", "{1.6825396825}"); // without offset 1144 } 1145 1146 void TestStatisticalFunctions::testTRIMMEAN() 1147 { 1148 // ODF-tests 1149 CHECK_EVAL("TRIMMEAN(A19:A23; 0.8 )", Value(4)); // cutOff = 2 1150 CHECK_EVAL("TRIMMEAN(A19:A23; 0.6 )", Value(4.6666666666)); // cutOff = FLOOR(5 * 0.6/ 2) = FLOOR(1.5) = 1; 1151 // result = 14 / 3 1152 CHECK_EVAL("TRIMMEAN(A19:A23; 0.19 )", Value(6.2)); // cutOff = 0 1153 CHECK_EVAL("TRIMMEAN(A19:A23; 0.999999 )", Value(4)); // cutOff = 2 1154 CHECK_EVAL("TRIMMEAN(A19:A23; 1)", Value::errorNUM()); // 0 <= cutOffFraction < 1 1155 } 1156 1157 void TestStatisticalFunctions::testTTEST() 1158 { 1159 // ODF-tests 1160 CHECK_EVAL("TTEST(A19:A23;A24:A28; 1; 1 )", Value(0.0427206184)); // 1161 CHECK_EVAL("TTEST(A19:A23;A24:A28; 2; 1 )", Value(0.0854412368)); // 1162 CHECK_EVAL("TTEST(A19:A23;A24:A28; 1; 2 )", Value(0.0294544970)); // 1163 CHECK_EVAL("TTEST(A19:A23;A24:A28; 1; 3 )", Value(0.0462125526)); // 1164 CHECK_EVAL("TTEST(A19:A23;A24:A29; 1; 1 )", Value::errorNUM()); // same amount of numbers for paired samples 1165 CHECK_EVAL("TTEST(A19:A19;A24:A24; 1; 3 )", Value::errorNUM()); // two numbers at least for each sequence 1166 } 1167 1168 void TestStatisticalFunctions::testVAR() 1169 { 1170 // ODF-tests 1171 CHECK_EVAL("VAR(2;4)", Value(2)); // The sample variance of (2;4) is 2. 1172 CHECK_EVAL("VAR(B4:B5)*2", Value(1)); // The sample variance of (2;3) is 0.5. 1173 CHECK_EVAL("VAR(B3:B5)*2", Value(1)); // Strings are not converted to numbers and are ignored. 1174 CHECK_EVAL("VAR(1)", Value::errorNUM()); // At least two numbers must be included 1175 } 1176 1177 void TestStatisticalFunctions::testVARA() 1178 { 1179 // ODF-tests 1180 CHECK_EVAL("VARA(2;4)", Value(2)); // The sample variance of (2;4) is 2. 1181 CHECK_EVAL("VARA(B5:C6)", Value(6.6666666667)); // Logicals (referenced) are converted to numbers. 1182 CHECK_EVAL("VARA(TRUE();FALSE())", Value(0.5)); // Logicals (inlined) are converted to numbers. 1183 CHECK_EVAL("VARA(1)", Value::errorNUM()); // Two numbers at least. 1184 } 1185 1186 void TestStatisticalFunctions::testVARIANCE() 1187 { 1188 // same as VAR 1189 1190 // ODF-tests 1191 CHECK_EVAL("VARIANCE(2;4)", Value(2)); // The sample variance of (2;4) is 2. 1192 CHECK_EVAL("VARIANCE(B4:B5)*2", Value(1)); // The sample variance of (2;3) is 0.5. 1193 CHECK_EVAL("VARIANCE(B3:B5)*2", Value(1)); // Strings are not converted to numbers and are ignored. 1194 CHECK_EVAL("VARIANCE(1)", Value::errorNUM()); // At least two numbers must be included 1195 } 1196 1197 void TestStatisticalFunctions::testVARP() 1198 { 1199 // Cell | Value 1200 // ------+------- 1201 // B3 | "7" 1202 // B4 | 2 1203 // B5 | 3 1204 1205 // ODF-tests 1206 CHECK_EVAL("VARP(2;4)", Value(1)); // The variance of the set for (2;4) is 1. 1207 CHECK_EVAL("VARP(B4:B5)*4", Value(1)); // The variance of the set for (2;3) is 0.25. 1208 CHECK_EVAL("VARP(B3:B5)*4", Value(1)); // Strings are not converted to numbers and are ignored. 1209 } 1210 1211 void TestStatisticalFunctions::testVARPA() 1212 { 1213 // Cell | Value Cell | Value 1214 // ------+------ ------+------ 1215 // B5 | 3 C5 | 5 1216 // B6 | true C6 | 7 1217 1218 // ODF-tests 1219 CHECK_EVAL("VARPA(2;4)", Value(1)); // The sample variance of (2;4) is 1. 1220 CHECK_EVAL("VARPA(B5:C6)", Value(5)); // Logicals (referenced) are converted to numbers. 1221 CHECK_EVAL("VARPA(TRUE();FALSE())", Value(0.25)); // Logicals (inlined) are converted to numbers. 1222 } 1223 1224 void TestStatisticalFunctions::testWEIBULL() 1225 { 1226 // TODO - be more precise 1227 1228 // ODF-tests 1229 CHECK_EVAL_SHORT("WEIBULL( 2; 3; 4; 0 )", Value(0.165468)); // pdf 1230 CHECK_EVAL_SHORT("WEIBULL( 2; 3; 4; 1 )", Value(0.117503)); // cdf 1231 CHECK_EVAL_SHORT("WEIBULL( -1; 3; 4; 0 )", Value::errorNUM()); // value >= 0 1232 CHECK_EVAL_SHORT("WEIBULL( 2; 0; 4; 0 )", Value::errorNUM()); // alpha > 0 1233 CHECK_EVAL_SHORT("WEIBULL( 2; 3; 0; 0 )", Value::errorNUM()); // beta > 0 1234 } 1235 1236 void TestStatisticalFunctions::testZTEST() 1237 { 1238 // ODF-tests 1239 CHECK_EVAL("ZTEST(B4:C5; 3.5 )", Value(0)); // mean = average, estimated standard deviation: fits well 1240 CHECK_EVAL("ZTEST(B4:C5; 3 ; 2 )", Value(0.3829249225)); // mean near average, standard deviation greater than estimate: probable 1241 CHECK_EVAL("ZTEST(B4:C5; 4 ; 0.5 )", Value(0.9544997361)); // mean near the average, but small deviation: not probable 1242 CHECK_EVAL("ZTEST(B4:C5; 5 )", Value(0.9798632484)); // mean at a border value, standard deviation ~ 1,3: nearly improbable 1243 CHECK_EVAL("ZTEST(B4:C5; 5 ; 0.1 )", Value(1)); // mean at a border value, small standard deviation: improbable 1244 } 1245 1246 void TestStatisticalFunctions::cleanupTestCase() 1247 { 1248 delete m_map; 1249 } 1250 1251 QTEST_MAIN(TestStatisticalFunctions)