1 /* This file is part of the KDE project
2 Copyright 2007 Ariya Hidayat <ariya@kde.org>
3 Copyright 2007 Sascha Pfau <MrPeacock@gmail.com>
4
5 This library is free software; you can redistribute it and/or
6 modify it under the terms of the GNU Library General Public
7 License as published by the Free Software Foundation; only
8 version 2 of the License.
9
10 This library is distributed in the hope that it will be useful,
11 but WITHOUT ANY WARRANTY; without even the implied warranty of
12 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13 Library General Public License for more details.
14
15 You should have received a copy of the GNU Library General Public License
16 along with this library; see the file COPYING.LIB. If not, write to
17 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
18 Boston, MA 02110-1301, USA.
19 */
20 #include "TestMathFunctions.h"
21
22 #include "TestKspreadCommon.h"
23
24 #include <CellStorage.h>
25 #include <Formula.h>
26 #include <Map.h>
27 #include <Sheet.h>
28 #include <CalculationSettings.h>
29
30 // NOTE: we do not compare the numbers _exactly_ because it is difficult
31 // to get one "true correct" expected values for the functions due to:
32 // - different algorithms among spreadsheet programs
33 // - precision limitation of floating-point number representation
34 // - accuracy problem due to propagated error in the implementation
35 #define CHECK_EVAL(x,y) QCOMPARE(TestDouble(x,y,6),y)
36 #define CHECK_EVAL_SHORT(x,y) QCOMPARE(TestDouble(x,y,10),y)
37 #define ROUND(x) (roundf(1e10 * x) / 1e10)
38
TestDouble(const QString & formula,const Value & v2,int accuracy)39 Value TestMathFunctions::TestDouble(const QString& formula, const Value& v2, int accuracy)
40 {
41 double epsilon = DBL_EPSILON * pow(10.0, (double)(accuracy));
42
43 Formula f(m_map->sheet(0));
44 QString expr = formula;
45 if (expr[0] != '=')
46 expr.prepend('=');
47 f.setExpression(expr);
48 Value result = f.eval();
49
50 bool res = fabs(v2.asFloat() - result.asFloat()) < epsilon;
51
52 if (!res)
53 qDebug() << "check failed -->" << "Epsilon =" << epsilon << "" << (double) v2.asFloat() << " to" << (double)result.asFloat() << " diff =" << (double)(v2.asFloat() - result.asFloat());
54 /* else
55 qDebug()<<"check -->" <<" diff =" << v2.asFloat()-result.asFloat();*/
56 if (res)
57 return v2;
58 else
59 return result;
60 }
61
62 // round to get at most 10-digits number
RoundNumber(double f)63 inline static Value RoundNumber(double f)
64 {
65 return Value(ROUND(f));
66 }
67
68 // round to get at most 10-digits number
RoundNumber(const Value & v)69 inline static Value RoundNumber(const Value& v)
70 {
71 if (v.isNumber()) {
72 double d = numToDouble(v.asFloat());
73 if (fabs(d) < DBL_EPSILON)
74 d = 0.0;
75 return Value(ROUND(d));
76 } else
77 return v;
78 }
79
evaluate(const QString & formula)80 Value TestMathFunctions::evaluate(const QString& formula)
81 {
82 Formula f(m_map->sheet(0));
83 QString expr = formula;
84 if (expr[0] != '=')
85 expr.prepend('=');
86 f.setExpression(expr);
87 Value result = f.eval();
88
89 #if 0
90 // this magically generates the CHECKs
91 printf(" CHECK_EVAL( \"%s\", %15g) );\n", qPrintable(formula), result.asFloat());
92 #endif
93
94 return RoundNumber(result);
95 }
96
initTestCase()97 void TestMathFunctions::initTestCase()
98 {
99 FunctionModuleRegistry::instance()->loadFunctionModules();
100
101 m_map = new Map(0 /* no Doc */);
102 m_map->addNewSheet("Sheet1");
103 m_map->addNewSheet("Sheet2");
104
105 Sheet* sheet1 = m_map->sheet(0);
106 CellStorage* storage1 = sheet1->cellStorage();
107
108 // Sheet1!B3:B7
109 storage1->setValue(2, 3, Value("7"));
110 storage1->setValue(2, 4, Value(2));
111 storage1->setValue(2, 5, Value(3));
112 storage1->setValue(2, 6, Value(true));
113 storage1->setValue(2, 7, Value("Hello"));
114 // Sheet1!B9
115 storage1->setValue(2, 9, Value::errorDIV0());
116
117 Sheet* sheet2 = m_map->sheet(1);
118 CellStorage* storage2 = sheet2->cellStorage();
119
120 // Sheet2!A1:B13
121 storage2->setValue(1, 1, Value("test"));
122 storage2->setValue(2, 1, Value(1));
123 storage2->setValue(1, 2, Value("test1"));
124 storage2->setValue(2, 2, Value(2));
125 storage2->setValue(1, 3, Value("test2"));
126 storage2->setValue(2, 3, Value(3));
127 storage2->setValue(1, 4, Value("test1*"));
128 storage2->setValue(2, 4, Value(4));
129 storage2->setValue(1, 5, Value("test1.*"));
130 storage2->setValue(2, 5, Value(5));
131 storage2->setValue(1, 6, Value("TeSt"));
132 storage2->setValue(2, 6, Value(6));
133 storage2->setValue(1, 7, Value("Test1"));
134 storage2->setValue(2, 7, Value(7));
135 storage2->setValue(1, 8, Value("Test2"));
136 storage2->setValue(2, 8, Value(8));
137 storage2->setValue(1, 9, Value(" test"));
138 storage2->setValue(2, 9, Value(9));
139 storage2->setValue(1, 10, Value(" test1"));
140 storage2->setValue(2, 10, Value(10));
141 storage2->setValue(1, 11, Value("*test"));
142 storage2->setValue(2, 11, Value(11));
143 storage2->setValue(1, 12, Value("*test test"));
144 storage2->setValue(2, 12, Value(12));
145 storage2->setValue(1, 13, Value("^test"));
146 storage2->setValue(2, 13, Value(13));
147 }
148
cleanupTestCase()149 void TestMathFunctions::cleanupTestCase()
150 {
151 delete m_map;
152 }
153
154 ///////////////////////////////////////////////////////////////////////////////////////////////
155
testABS()156 void TestMathFunctions::testABS()
157 {
158 CHECK_EVAL("ABS(0)", Value(0));
159 CHECK_EVAL("ABS(-1)", Value(1));
160 CHECK_EVAL("ABS(-2)", Value(2));
161 CHECK_EVAL("ABS(-3)", Value(3));
162 CHECK_EVAL("ABS(-4)", Value(4));
163 CHECK_EVAL("ABS(1)", Value(1));
164 CHECK_EVAL("ABS(2)", Value(2));
165 CHECK_EVAL("ABS(3)", Value(3));
166 CHECK_EVAL("ABS(4)", Value(4));
167 CHECK_EVAL("ABS(1/0)", Value::errorDIV0());
168 }
169
testACOS()170 void TestMathFunctions::testACOS()
171 {
172 // ODF-tests
173 CHECK_EVAL("ACOS(SQRT(2)/2)*4/PI()", Value(1.0)); // arc cosine of SQRT(2)/2 is PI()/4 radians.
174 CHECK_EVAL("ACOS(TRUE())", Value(0.0)); // TRUE() is 1 if inline.
175 CHECK_EVAL("ACOS(-1.0)/PI()", Value(1.0)); // The result must be between 0.0 and PI().
176 CHECK_EVAL("ACOS(2.0)", Value::errorVALUE()); // The argument must be between -1.0 and 1.0.
177
178 // ACosinus needs to be a numeric value between >=-1.0 and <=1.0
179 CHECK_EVAL("ACOS()", Value::errorVALUE());
180 CHECK_EVAL("ACOS(-1.1)", Value::errorVALUE());
181 CHECK_EVAL("ACOS(1.1)", Value::errorVALUE());
182
183 CHECK_EVAL("ACOS(1.0)", Value(0));
184 CHECK_EVAL_SHORT("2-ACOS(-1.0)", Value(-1.14159265));
185 }
186
testACOSH()187 void TestMathFunctions::testACOSH()
188 {
189 // ODF-tests
190 CHECK_EVAL("ACOSH(1)", Value(0)); //
191 CHECK_EVAL("ACOSH(2)", Value(1.316957897)); //
192 }
193
testACOT()194 void TestMathFunctions::testACOT()
195 {
196 // ODF-tests
197 CHECK_EVAL("ACOT(0)-PI()/2", Value(0)); //
198 }
199
testACOTH()200 void TestMathFunctions::testACOTH()
201 {
202 // ODF-tests
203 CHECK_EVAL("ACOTH(2)", Value(0.5493061443)); //
204 }
205
testASIN()206 void TestMathFunctions::testASIN()
207 {
208 // ODF-tests
209 CHECK_EVAL("ASIN(SQRT(2)/2)*4/PI()", Value(1.0)); // arc sine of SQRT(2)/2 is PI()/4 radians.
210 CHECK_EVAL("ASIN(TRUE())*2/PI()", Value(1.0)); // TRUE() is 1 if inline.
211 CHECK_EVAL("ASIN(-1)*2/PI()", Value(-1.0)); // The result must be between -PI()/2 and PI()/2.
212 CHECK_EVAL("ASIN(2)", Value::errorVALUE()); // The argument must be between -1.0 and 1.0.
213
214 // ASinus needs to be a numeric value between >=-1.0 and <=1.0
215 CHECK_EVAL("ASIN(1.2)", Value::errorVALUE());
216 CHECK_EVAL("ASIN(-99)", Value::errorVALUE());
217
218 CHECK_EVAL_SHORT("1-ASIN(1)", Value(-0.57079633));
219 CHECK_EVAL_SHORT("1+ASIN(-1.0)", Value(-0.57079633));
220 }
221
testASINH()222 void TestMathFunctions::testASINH()
223 {
224 // ODF-tests
225 CHECK_EVAL("ASINH(0)", Value(0)); //
226 CHECK_EVAL("ASINH(1)", Value(0.881373587)); //
227 }
228
testATAN()229 void TestMathFunctions::testATAN()
230 {
231 // ODF-tests
232 CHECK_EVAL("ATAN(1)*4/PI()", Value(1)); // arc tangent of 1 is PI()/4 radians.
233 CHECK_EVAL_SHORT("ATAN(-1.0e16)", Value(-1.570796)); // TODO expand / Check if ATAN gives reasonably accurate results,
234 // and that slightly negative values as input produce numbers near -PI/2.
235 }
236
testATAN2()237 void TestMathFunctions::testATAN2()
238 {
239 // ODF-tests
240 CHECK_EVAL("ATAN2(1;1)*4/PI()", Value(1)); // arc tangent of 1.0/1.0 is PI()/4 radians.
241 CHECK_EVAL("ATAN2(1;-1)*4/PI()", Value(-1)); // Location of sign makes a difference.
242 CHECK_EVAL("ATAN2(-1;1)*4/PI()", Value(3)); // Location of sign makes a difference.
243 CHECK_EVAL("ATAN2(-1;-1)*4/PI()", Value(-3)); // Location of sign makes a difference.
244 CHECK_EVAL("SIGN(ATAN2(-1.0;0.001))", Value(1)); // If y is small, it's still important
245 CHECK_EVAL("SIGN(ATAN2(-1.0;-0.001))", Value(-1)); // If y is small, it's still important
246 CHECK_EVAL("ATAN2(-1.0;0)/PI()", Value(1)); // By definition ATAN2(-1,0) should give PI() rather than -PI().
247 }
248
testATANH()249 void TestMathFunctions::testATANH()
250 {
251 // ODF-tests
252 CHECK_EVAL_SHORT("ATANH(0)", Value(0)); // TODO expand
253 CHECK_EVAL_SHORT("ATANH(0.5)", Value(0.549306144)); // TODO expand
254 }
255
testBESSELI()256 void TestMathFunctions::testBESSELI()
257 {
258 // ODF-tests
259 CHECK_EVAL_SHORT("BESSELI(2;2)", Value(0.688948)); // TODO expand
260 CHECK_EVAL_SHORT("BESSELI(0.7;3)", Value(0.007367));
261 // alternate function name
262 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETBESSELI(2;2)", Value(0.688948));
263 }
264
testBESSELJ()265 void TestMathFunctions::testBESSELJ()
266 {
267 // ODF-tests
268 CHECK_EVAL_SHORT("BESSELJ(1;0)", Value(0.765198)); // TODO expand
269 CHECK_EVAL_SHORT("BESSELJ(0.89;3)", Value(0.013974));
270 // alternate function name
271 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETBESSELJ(1;0)", Value(0.765198));
272 }
273
testBESSELK()274 void TestMathFunctions::testBESSELK()
275 {
276 // ODF-tests
277 CHECK_EVAL_SHORT("BESSELK(3;0)", Value(0.03474)); // TODO expand
278 // alternate function name
279 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETBESSELK(3;0)", Value(0.03474));
280 }
281
testBESSELY()282 void TestMathFunctions::testBESSELY()
283 {
284 // ODF-tests
285 CHECK_EVAL_SHORT("BESSELY(1;1)", Value(-0.781213)); // TODO expand
286 CHECK_EVAL_SHORT("BESSELY(4;2)", Value(0.215903595));
287 // alternate function name
288 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETBESSELY(1;1)", Value(-0.781213));
289 }
290
testCEIL()291 void TestMathFunctions::testCEIL()
292 {
293 CHECK_EVAL("CEIL(0)", Value(0));
294
295 CHECK_EVAL("CEIL(0.1)", Value(1));
296 CHECK_EVAL("CEIL(0.01)", Value(1));
297 CHECK_EVAL("CEIL(0.001)", Value(1));
298 CHECK_EVAL("CEIL(0.0001)", Value(1));
299 CHECK_EVAL("CEIL(0.00001)", Value(1));
300 CHECK_EVAL("CEIL(0.000001)", Value(1));
301 CHECK_EVAL("CEIL(0.0000001)", Value(1));
302
303 CHECK_EVAL("CEIL(1.1)", Value(2));
304 CHECK_EVAL("CEIL(1.01)", Value(2));
305 CHECK_EVAL("CEIL(1.001)", Value(2));
306 CHECK_EVAL("CEIL(1.0001)", Value(2));
307 CHECK_EVAL("CEIL(1.00001)", Value(2));
308 CHECK_EVAL("CEIL(1.000001)", Value(2));
309 CHECK_EVAL("CEIL(1.0000001)", Value(2));
310
311 CHECK_EVAL("CEIL(-0.1)", Value(0));
312 CHECK_EVAL("CEIL(-0.01)", Value(0));
313 CHECK_EVAL("CEIL(-0.001)", Value(0));
314 CHECK_EVAL("CEIL(-0.0001)", Value(0));
315 CHECK_EVAL("CEIL(-0.00001)", Value(0));
316 CHECK_EVAL("CEIL(-0.000001)", Value(0));
317 CHECK_EVAL("CEIL(-0.0000001)", Value(0));
318
319
320 CHECK_EVAL("CEIL(-1.1)", Value(-1));
321 CHECK_EVAL("CEIL(-1.01)", Value(-1));
322 CHECK_EVAL("CEIL(-1.001)", Value(-1));
323 CHECK_EVAL("CEIL(-1.0001)", Value(-1));
324 CHECK_EVAL("CEIL(-1.00001)", Value(-1));
325 CHECK_EVAL("CEIL(-1.000001)", Value(-1));
326 CHECK_EVAL("CEIL(-1.0000001)", Value(-1));
327 }
328
testCEILING()329 void TestMathFunctions::testCEILING()
330 {
331 CHECK_EVAL("CEILING(0 ; 0.1)", Value(0));
332 CHECK_EVAL("CEILING(0 ; 0.2)", Value(0));
333 CHECK_EVAL("CEILING(0 ; 1.0)", Value(0));
334 CHECK_EVAL("CEILING(0 ;10.0)", Value(0));
335
336 CHECK_EVAL("CEILING(0.1; 0.2)", Value(0.2));
337 CHECK_EVAL("CEILING(0.1; 0.4)", Value(0.4));
338 CHECK_EVAL("CEILING(1.1; 0.2)", Value(1.2));
339
340 // because can't divide by 0
341 CHECK_EVAL("CEILING(1; 0)", Value::errorDIV0());
342 CHECK_EVAL("CEILING(2; 0)", Value::errorDIV0());
343
344 // but this one should be just fine !
345 CHECK_EVAL("CEILING(0; 0)", Value(0));
346
347 // different sign does not make sense
348 CHECK_EVAL("CEILING(-1; 2)", Value::errorNUM());
349 CHECK_EVAL("CEILING(1; -2)", Value::errorNUM());
350
351 // mode param
352 CHECK_EVAL("CEILING(-5.4; -4.0; 0)", Value(-4.0));
353 CHECK_EVAL("CEILING(-5.4; -4.0; 1)", Value(-8.0));
354 }
355
testCOMBIN()356 void TestMathFunctions::testCOMBIN()
357 {
358 // ODF-tests
359 CHECK_EVAL("COMBIN(5;3)", Value(10)); //
360 CHECK_EVAL("COMBIN(6;3)", Value(20)); //
361 CHECK_EVAL("COMBIN(42;3)", Value(11480)); //
362 CHECK_EVAL("COMBIN(-1;3)", Value::errorNUM()); // N must be >= 0
363 CHECK_EVAL("COMBIN(4;-3)", Value::errorNUM()); // M must be >= 0
364 }
365
testCOMBINA()366 void TestMathFunctions::testCOMBINA()
367 {
368 // ODF-tests
369 CHECK_EVAL("COMBINA(5;3)", Value(35)); //
370 CHECK_EVAL("COMBINA(-1;3)", Value::errorNUM()); // N must be >= 0
371 CHECK_EVAL("COMBINA(4;-3)", Value::errorNUM()); // M must be >= 0
372 }
373
testCONVERT()374 void TestMathFunctions::testCONVERT()
375 {
376 // ODF-tests
377
378 // TODO add missing SI-units and expand up to 10 digits
379
380 CHECK_EVAL("CONVERT( 1; \"ft\"; \"in\")", Value(12)); // 1 foot is 12 inches. Conversion between
381 // units might involve an intermediate SI unit
382 // in some implementations, and such round-off
383 // error is considered acceptable.
384 CHECK_EVAL("CONVERT( 1; \"in\"; \"cm\")", Value(2.54)); // 1 inch is 2.54 cm. The result is exact, because
385 // this needs to be represented as accurately as the
386 // underlying numerical model permits
387 CHECK_EVAL("CONVERT( 5; \"m\"; \"mm\")", Value(5000)); // 5 meters is 5000 millimeters
388 CHECK_EVAL("CONVERT( 100; \"C\"; \"F\")", Value(212)); // 212 degrees F is 100 degrees C. Note that this
389 // is not simply a multiplicative relationship.
390 // Since internally this is (100/5*9+32), where
391 // 100/5 is exactly 20, this result needs to be
392 // exact even on floating-point implementations
393 CHECK_EVAL("CONVERT( 2; \"Ym\"; \"Zm\")", Value(2000)); // Must support Y and Z prefixes. (wrong ODF-specs 100)
394 CHECK_EVAL("CONVERT( 20; \"F\"; \"m\")", Value::errorNA()); // Different groups produce an error.
395 CHECK_EVAL_SHORT("CONVERT(1000;\"qt\";\"l\")", Value(946.5588641)); // Quart is U.S. customary, liquid measure
396 CHECK_EVAL_SHORT("CONVERT(1000;\"tbs\";\"l\")", Value(14.78998225)); // Tablespoon uses U.S. customary historic definition
397 // - note that there are many other definitions
398 CHECK_EVAL("CONVERT(1000; \"tsp\"; \"l\")", Value(4.929994084)); // Teaspoon uses U.S. customary historic definition
399 // - note that there are many other definitions
400 CHECK_EVAL("CONVERT( 1; \"das\"; \"sec\")", Value(10)); // Does it support both "s" and "sec" for second?
401 // Does it support "da" as the SI standard deka prefix?
402 CHECK_EVAL("CONVERT( 1; \"ar\"; \"m^2\")", Value(100)); // A hectare (ar) is 100 square meters.
403 // CHECK_EVAL( "CONVERT( 1; \"cal\"; \"J\")", Value( 4.1868 ) ); // "cal" is an International Table (IT) calorie, 4.1868 J.
404 CHECK_EVAL("CONVERT( 1; \"lbf\"; \"N\")", Value(4.448222)); // Converting pound-force to Newtons
405 CHECK_EVAL("CONVERT( 1; \"HP\"; \"W\")", Value(745.701)); // Horsepower to Watts
406 CHECK_EVAL("CONVERT( 1; \"Mibyte\"; \"bit\")", Value(8388608)); // Converts bytes to bits, and tests binary prefixes
407 CHECK_EVAL("CONVERT( 1; \"Gibyte\"; \"Mibyte\")", Value(1024)); // Converts bytes to bits, and tests binary prefixes
408 CHECK_EVAL("CONVERT( 1; \"T\"; \"ga\")", Value(10000)); // Tesla to Gauss
409 // CHECK_EVAL( "CONVERT( 1; \"lbm\"; \"g\")", Value( 453.59237 ) ); // International pound mass (avoirdupois) to grams.
410 // (This is actually exact.)
411 CHECK_EVAL("CONVERT( 1; \"uk_ton\"; \"lbm\")", Value(2240)); // Imperial ton, aka "long ton", "deadweight ton",
412 // or "weight ton", is 2240 lbm.
413 // CHECK_EVAL( "CONVERT( 1; \"psi\"; \"Pa\")", Value( 6894.76 ) ); // Pounds per square inch to Pascals.
414 CHECK_EVAL("CONVERT( 60; \"mph\"; \"km/h\")", Value(96.56064)); // Miles per hour to kilometers per hour.
415 CHECK_EVAL("CONVERT( 1; \"day\"; \"s\")", Value(86400)); // Day to seconds. Note: This test uses the
416 // international standard abbreviation for second (s),
417 // not the abbreviation traditionally used in spreadsheets
418 // (sec); both "s" and "sec" must be supported.
419 // CHECK_EVAL_SHORT( "CONVERT( 1; \"qt\"; \"L\")", Value( 0.9463529460 ) ); // Quart (U.S. customary liquid measure) to liter.
420 // This is 0.946352946 liters,
421 }
422
testCOT()423 void TestMathFunctions::testCOT()
424 {
425 // ODF-tests
426 CHECK_EVAL("COT(PI()/4.0)", Value(1)); // cotangent of PI()/4.0 radians.
427 CHECK_EVAL("COT(PI()/2.0)", Value(0)); // cotangent of PI()/2 radians. Not the same as TAN.
428 CHECK_EVAL("COT(0)", Value::errorDIV0()); // cotangent of PI()/4.0 radians.
429 }
430
testCOTH()431 void TestMathFunctions::testCOTH()
432 {
433 // ODF-tests
434 CHECK_EVAL("COTH(1)", Value(1.3130352855)); //
435 CHECK_EVAL("COTH(EXP(1))", Value(1.0087469296)); //
436 }
437
testDEGREES()438 void TestMathFunctions::testDEGREES()
439 {
440 // ODF-tests
441 CHECK_EVAL("DEGREES(PI())", Value(180)); // PI() radians is 180 degrees.
442 }
443
testDELTA()444 void TestMathFunctions::testDELTA()
445 {
446 // ODF-tests
447 CHECK_EVAL("DELTA(2;3)", Value(0)); // Different numbers are not equal
448 CHECK_EVAL("DELTA(2;2)", Value(1)); // Same numbers are equal
449 CHECK_EVAL("DELTA(0)" , Value(1)); // 0 equal to default 0
450 }
451
testEVEN()452 void TestMathFunctions::testEVEN()
453 {
454 // ODF-tests
455 CHECK_EVAL("EVEN(6)", Value(6)); // Positive even integers remain unchanged.
456 CHECK_EVAL("EVEN(-4)", Value(-4)); // Negative even integers remain unchanged.
457 CHECK_EVAL("EVEN(1)", Value(2)); // Non-even positive integers round up.
458 CHECK_EVAL("EVEN(0.3)", Value(2)); // Positive floating values round up.
459 CHECK_EVAL("EVEN(-1)", Value(-2)); // Non-even negative integers round down.
460 CHECK_EVAL("EVEN(-0.3)", Value(-2)); // Negative floating values round down.
461 CHECK_EVAL("EVEN(0)", Value(0)); // Since zero is even, EVEN(0) returns zero.
462 }
463
testEXP()464 void TestMathFunctions::testEXP()
465 {
466 // ODF-tests
467 CHECK_EVAL("EXP(0)", Value(1)); // Anything raised to the 0 power is 1.
468 CHECK_EVAL("EXP(LN(2))", Value(2)); // The EXP function is the inverse of the LN function.
469 CHECK_EVAL("EXP(1)", Value(2.71828182845904523536)); // The value of the natural logarithm e.
470 }
471
testFACT()472 void TestMathFunctions::testFACT()
473 {
474 CHECK_EVAL("FACT(0)", Value(1));
475 CHECK_EVAL("FACT(1)", Value(1));
476 CHECK_EVAL("FACT(2)", Value(2));
477 CHECK_EVAL("FACT(3)", Value(6));
478 CHECK_EVAL("FACT(-1)", Value::errorNUM());
479 CHECK_EVAL("FACT(\"xyzzy\")", Value::errorNUM());
480 }
481
testFACTDOUBLE()482 void TestMathFunctions::testFACTDOUBLE()
483 {
484 CHECK_EVAL("FACTDOUBLE(0)", Value(1));
485 CHECK_EVAL("FACTDOUBLE(1)", Value(1));
486 CHECK_EVAL("FACTDOUBLE(7)", Value(105));
487 CHECK_EVAL("FACTDOUBLE(6)", Value(48));
488 CHECK_EVAL("FACTDOUBLE(-1)", Value::errorNUM());
489 CHECK_EVAL("FACTDOUBLE(\"xyzzy\")", Value::errorNUM());
490 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETFACTDOUBLE(7)", Value(105)); // alternate function name
491 }
492
testFIB()493 void TestMathFunctions::testFIB()
494 {
495 CHECK_EVAL("FIB(1)", Value(1));
496 CHECK_EVAL("FIB(2)", Value(1));
497 CHECK_EVAL("FIB(3)", Value(2));
498 CHECK_EVAL("FIB(4)", Value(3));
499 CHECK_EVAL("FIB(5)", Value(5));
500 CHECK_EVAL("FIB(6)", Value(8));
501 CHECK_EVAL("FIB(7)", Value(13));
502 CHECK_EVAL("FIB(8)", Value(21));
503 CHECK_EVAL("FIB(9)", Value(34));
504 CHECK_EVAL("FIB(10)", Value(55));
505
506 // large number
507 CHECK_EVAL("FIB(100)/1E+20", Value(3.54224848179263));
508 CHECK_EVAL("FIB(200)/1E+41", Value(2.80571172992512));
509 CHECK_EVAL("FIB(300)/1E+62", Value(2.22232244629423));
510 CHECK_EVAL("FIB(400)/1E+83", Value(1.76023680645016));
511 CHECK_EVAL("FIB(500)/1E+104", Value(1.394232245617));
512 CHECK_EVAL("FIB(600)/1E+125", Value(1.10433070572954));
513
514 // invalid
515 CHECK_EVAL("FIB(0)", Value::errorNUM());
516 CHECK_EVAL("FIB(-1)", Value::errorNUM());
517 CHECK_EVAL("FIB(\"text\")", Value::errorVALUE());
518 }
519
testFLOOR()520 void TestMathFunctions::testFLOOR()
521 {
522 // ODF-Tests
523 CHECK_EVAL("=FLOOR(2; 1)", Value(2));
524 CHECK_EVAL("=FLOOR(2.5; 1)", Value(2));
525 CHECK_EVAL("=FLOOR(5; 2)", Value(4));
526 CHECK_EVAL("=FLOOR(5; 2.2)", Value(4.4));
527 CHECK_EVAL("=FLOOR(-2.5;1)", Value::errorVALUE());
528 CHECK_EVAL("=FLOOR(-2.5; -1)", Value(-3));
529 CHECK_EVAL("=FLOOR(-2.5; -1;1)", Value(-2));
530 CHECK_EVAL("=FLOOR(-2.5;0)", Value(0));
531 CHECK_EVAL("=FLOOR(0;-1)", Value(0));
532 CHECK_EVAL("=FLOOR(-1.1)", Value(-2));
533 }
534
testGAMMA()535 void TestMathFunctions::testGAMMA()
536 {
537 // ODF-Tests
538 CHECK_EVAL("GAMMA(1.00)", Value(1.0000000000));
539 CHECK_EVAL("GAMMA(1.10)", Value(0.9513507700));
540 CHECK_EVAL("GAMMA(1.50)", Value(0.8862269255));
541 }
542
testGAMMALN()543 void TestMathFunctions::testGAMMALN()
544 {
545 // ODF-Tests
546 CHECK_EVAL("GAMMALN(1.00)", Value(0));
547 CHECK_EVAL("GAMMALN(2.00)", Value(0));
548 CHECK_EVAL("GAMMALN(3.00)", Value(0.6931471806));
549 CHECK_EVAL("GAMMALN(1.50)", Value(-0.1207822376));
550 }
551
testGCD()552 void TestMathFunctions::testGCD()
553 {
554 CHECK_EVAL("GCD(5;15;25)", Value(5));
555 CHECK_EVAL("GCD(2;3)", Value(1));
556 CHECK_EVAL("GCD(18;24)", Value(6));
557 CHECK_EVAL("GCD(18.1;24.1)", Value(6));
558 CHECK_EVAL("GCD(1.1;2.2)", Value(1));
559 CHECK_EVAL("GCD(18.9;24.9)", Value(6));
560 CHECK_EVAL("GCD(7)", Value(7));
561 CHECK_EVAL("GCD(5;0)", Value(5));
562 CHECK_EVAL("GCD(0;0)", Value(0));
563 CHECK_EVAL("GCD(-2;3)", Value::errorNUM());
564 CHECK_EVAL("GCD(2;-4)", Value::errorNUM());
565 CHECK_EVAL("GCD(-2;-4)", Value::errorNUM());
566 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETGCD(18;24)", Value(6)); // alternate function name
567 }
568
testGESTEP()569 void TestMathFunctions::testGESTEP()
570 {
571 // ODF-tests
572 CHECK_EVAL("GESTEP(2;1)", Value(1)); //
573 CHECK_EVAL("GESTEP(-1;-2)", Value(1)); // Negative arguments are valid
574 CHECK_EVAL("GESTEP(1)", Value(1)); // Second parameter assumed 0 if omitted
575 CHECK_EVAL("GESTEP(-2;1)", Value(0)); //
576 CHECK_EVAL("GESTEP(3;3)", Value(1)); // Number identical to step value.
577 CHECK_EVAL("GESTEP(1.3;1.2)", Value(1)); // Floating point values where X is greater than Step.
578 CHECK_EVAL("GESTEP(-2;\"xxx\")", Value::errorNUM()); //
579 CHECK_EVAL("GESTEP(\"xxx\";-2)", Value::errorNUM()); //
580 }
581
testINT()582 void TestMathFunctions::testINT()
583 {
584 // ODF-tests
585 CHECK_EVAL("=INT(2)", Value(2));
586 CHECK_EVAL("=INT(-3)", Value(-3));
587 CHECK_EVAL("=INT(1.2)", Value(1));
588 CHECK_EVAL("=INT(1.7)", Value(1));
589 CHECK_EVAL("=INT(-1.2)", Value(-2));
590 CHECK_EVAL("=INT((1/3)*3)", Value(1));
591 }
592
testLCM()593 void TestMathFunctions::testLCM()
594 {
595 CHECK_EVAL("LCM(5;15;25)", Value(75));
596 CHECK_EVAL("LCM(2;3)", Value(6));
597 CHECK_EVAL("LCM(18;12)", Value(36));
598 CHECK_EVAL("LCM(12;18)", Value(36));
599 CHECK_EVAL("LCM(12.1;18.1)", Value(36));
600 CHECK_EVAL("LCM(18.1;12.1)", Value(36));
601 CHECK_EVAL("LCM(18.9;12.9)", Value(36));
602 CHECK_EVAL("LCM(7)", Value(7));
603 CHECK_EVAL("LCM(5;0)", Value(0));
604 CHECK_EVAL("LCM(-2;4)", Value::errorNUM());
605 CHECK_EVAL("LCM(2;-4)", Value::errorNUM());
606 CHECK_EVAL("LCM(-2;-4)", Value::errorNUM());
607 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETLCM(18;12)", Value(36)); // alternate function name
608 }
609
testLN()610 void TestMathFunctions::testLN()
611 {
612 // ODF-tests
613 CHECK_EVAL("LN(1)", Value(0)); // The logarithm of 1 (in any base) is 0.
614 CHECK_EVAL("LN(EXP(1))", Value(1)); // The natural logarithm of e is 1.
615 CHECK_EVAL_SHORT("LN(20)", Value(2.995732274)); // TODO expand / Trivial test
616 CHECK_EVAL_SHORT("LN(0.2)", Value(-1.609437912)); // TODO expand / This tests a value between 0 and 0.5.
617 // Values in this domain are valid, but implementations that compute LN(x)
618 // by blindly summing the series (1/n)((x-1)/x)^n won't get this value
619 // correct, because that series requires x > 0.5.
620 CHECK_EVAL("LN(0)", Value::errorNUM()); // The argument must be greater than zero.
621 CHECK_EVAL("LN(\"s\")", Value::errorNUM()); // The argument must be a number.
622 }
623
testLOG()624 void TestMathFunctions::testLOG()
625 {
626 CHECK_EVAL("LOG(1;10)", Value(0));
627 CHECK_EVAL("LOG(1;EXP(1))", Value(0));
628 CHECK_EVAL("LOG(10;10)", Value(1));
629 CHECK_EVAL("LOG(EXP(1);EXP(1))", Value(1));
630 CHECK_EVAL("LOG(10)", Value(1));
631 CHECK_EVAL("LOG(8*8*8;8)", Value(3));
632 CHECK_EVAL("LOG(0;10)", Value::errorNUM());
633 CHECK_EVAL("LOG(\"foo\";10)", Value::errorNUM());
634 CHECK_EVAL("LOG(2;\"foo\")", Value::errorNUM());
635 CHECK_EVAL("LOG(NA();10)", Value::errorNA());
636 CHECK_EVAL("LOG(10;NA())", Value::errorNA());
637 CHECK_EVAL("LOG(NA();NA())", Value::errorNA());
638 }
639
testLOG10()640 void TestMathFunctions::testLOG10()
641 {
642 CHECK_EVAL("LOG10(1)", Value(0));
643 CHECK_EVAL("LOG10(10)", Value(1));
644 CHECK_EVAL("LOG10(100)", Value(2));
645 CHECK_EVAL("LOG10(0)", Value::errorNUM());
646 CHECK_EVAL("LOG10(\"H\")", Value::errorNUM());
647 CHECK_EVAL("LOG10(-2)", Value::errorNUM());
648 }
649
testMDETERM()650 void TestMathFunctions::testMDETERM()
651 {
652 CHECK_EVAL("MDETERM({2;4|3;5})", Value(-2));
653 CHECK_EVAL("MDETERM({2;4})", Value::errorVALUE());
654 CHECK_EVAL("MDETERM({2;4|3;6})", Value(0));
655 CHECK_EVAL("MDETERM(2)", Value(2));
656 }
657
testMINVERSE()658 void TestMathFunctions::testMINVERSE()
659 {
660 Value value(Value::Array);
661 value.setElement(0, 0, Value(-2.5));
662 value.setElement(1, 0, Value(2.0));
663 value.setElement(0, 1, Value(1.5));
664 value.setElement(1, 1, Value(-1.0));
665 CHECK_EVAL("MINVERSE({2;4|3;5})", value); // simply invertible
666 value.setElement(0, 0, Value(5.0));
667 value.setElement(1, 0, Value(1.0));
668 value.setElement(2, 0, Value(-2.0));
669 value.setElement(0, 1, Value(-1.0));
670 value.setElement(1, 1, Value(-1.0));
671 value.setElement(2, 1, Value(1.0));
672 value.setElement(0, 1, Value(-2.0));
673 value.setElement(1, 1, Value(1.0));
674 value.setElement(2, 1, Value(0.0));
675 CHECK_EVAL("MINVERSE({1;2;1|2;4;3|3;7;4}", value); // fails without pivoting
676 CHECK_EVAL("MINVERSE({2;4})", Value::errorVALUE()); // non-square matrix
677 CHECK_EVAL("MINVERSE({2;4|3;6})", Value::errorDIV0()); // singular matrix
678 CHECK_EVAL("MINVERSE(2)", evaluate("{0.5}")); // one elementary matrix
679 }
680
testMMULT()681 void TestMathFunctions::testMMULT()
682 {
683 CHECK_EVAL("MMULT({2;4|3;5};{2;4|3;5})", evaluate("{16.0;28.0|21.0;37.0}"));
684 }
685
testMOD()686 void TestMathFunctions::testMOD()
687 {
688 CHECK_EVAL("MOD(10;3)", Value(1)); // 10/3 has remainder 1.
689 CHECK_EVAL("MOD(2;8)", Value(2)); // 2/8 is 0 remainder 2.
690 CHECK_EVAL("MOD(5.5;2.5)", Value(0.5)); // The numbers need not be integers.
691 CHECK_EVAL("MOD(-2;3)", Value(1)); // The location of the sign matters.
692 CHECK_EVAL("MOD(2;-3)", Value(-1)); // The location of the sign matters.
693 CHECK_EVAL("MOD(-2;-3)", Value(-2)); // The location of the sign matters.
694 CHECK_EVAL("MOD(10;3)", Value(1)); // 10/3 has remainder 1.
695 CHECK_EVAL("MOD(10;0)", Value::errorDIV0()); // Division by zero is not allowed
696 }
697
testMROUND()698 void TestMathFunctions::testMROUND()
699 {
700 // ODF-tests
701 CHECK_EVAL("=MROUND(1564;100)", Value(1600));
702 CHECK_EVAL("=MROUND(1520;100)", Value(1500));
703 CHECK_EVAL("=MROUND(1550;100)", Value(1600));
704 CHECK_EVAL("=MROUND(41.89;8)", Value(40));
705 // alternate function name
706 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETMROUND(1520;100)", Value(1500));
707 }
708
testMULTINOMIAL()709 void TestMathFunctions::testMULTINOMIAL()
710 {
711 // ODF-tests
712 CHECK_EVAL("=MULTINOMIAL(3;4;5)", Value(27720));
713 // alternate function name
714 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETMULTINOMIAL(3;4;5)", Value(27720));
715 }
716
testMUNIT()717 void TestMathFunctions::testMUNIT()
718 {
719 CHECK_EVAL("MUNIT(2)", evaluate("{1;0|0;1}"));
720 CHECK_EVAL("MUNIT(3)", evaluate("{1;0;0|0;1;0|0;0;1}"));
721 }
722
testODD()723 void TestMathFunctions::testODD()
724 {
725 CHECK_EVAL("ODD(5)", Value(5));
726 CHECK_EVAL("ODD(-5)", Value(-5));
727 CHECK_EVAL("ODD(2)", Value(3));
728 CHECK_EVAL("ODD(0.3)", Value(1));
729 CHECK_EVAL("ODD(-2)", Value(-3));
730 CHECK_EVAL("ODD(-0.3)", Value(-1));
731 CHECK_EVAL("ODD(0)", Value(1));
732 }
733
testPOWER()734 void TestMathFunctions::testPOWER()
735 {
736 CHECK_EVAL("POWER(10;0)", Value(1)); // Anything raised to the 0 power is 1
737 CHECK_EVAL("POWER(2;8)" , Value(256)); // 2^8 is 256
738 }
739
testPRODUCT()740 void TestMathFunctions::testPRODUCT()
741 {
742 CHECK_EVAL("PRODUCT(2;3;4)", Value(24)); // Anything raised to the 0 power is 1
743 CHECK_EVAL("PRODUCT(TRUE();2;3)" , Value(6)); // TRUE() is 1 if inline
744 CHECK_EVAL("PRODUCT()", Value(0)); // Product with no parameters returns 0
745 //TODO
746 // check inline-values e.g. product(2;3;"2")
747 }
748
testQUOTIENT()749 void TestMathFunctions::testQUOTIENT()
750 {
751 CHECK_EVAL("QUOTIENT(10;5)", Value(2)); //
752 CHECK_EVAL("QUOTIENT(14;5)" , Value(2)); //
753 CHECK_EVAL("QUOTIENT(-204;-23)", Value(8)); //
754 CHECK_EVAL("QUOTIENT(-45;8)", Value(-5)); //
755 CHECK_EVAL("QUOTIENT(24;-5)" , Value(-4)); //
756 CHECK_EVAL("QUOTIENT(21;-5)", Value(-4)); //
757 CHECK_EVAL("QUOTIENT(-14;5)", Value(-2)); //
758 CHECK_EVAL("QUOTIENT(5;0)" , Value::errorDIV0()); //
759 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETQUOTIENT(14;5)", Value(2)); // alternate function name
760 }
761
testRADIANS()762 void TestMathFunctions::testRADIANS()
763 {
764 CHECK_EVAL("RADIANS(180)/PI()", Value(1)); // 180 degrees is PI() radians.
765 }
766
testRAND()767 void TestMathFunctions::testRAND()
768 {
769 CHECK_EVAL("RAND()>=0", Value(true)); // The random number must be between 0 and 1.
770 CHECK_EVAL("RAND()<=1", Value(true)); // The random number must be between 0 and 1.
771 }
772
testRANDBETWEEN()773 void TestMathFunctions::testRANDBETWEEN()
774 {
775 CHECK_EVAL("RANDBETWEEN(8;8)", Value(8)); // If A=B, return A.
776 CHECK_EVAL("RANDBETWEEN(5;15)>=5", Value(true)); // Must return value in range
777 CHECK_EVAL("RANDBETWEEN(5;15)<=15", Value(true)); // Must return value in range
778 CHECK_EVAL("RANDBETWEEN(15;5)>=5", Value(true)); // Must return value in range
779 CHECK_EVAL("RANDBETWEEN(15;5)<=15", Value(true)); // Must return value in range
780 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETRANDBETWEEN(8;8)", Value(8)); // alternate function name
781 }
782
testROUND()783 void TestMathFunctions::testROUND()
784 {
785 // ODF-tests
786 CHECK_EVAL("=ROUND(10.1;0)", Value(10));
787 CHECK_EVAL("=ROUND(9.8;0)", Value(10));
788 CHECK_EVAL("=ROUND(0.5;0)", Value(1));
789 CHECK_EVAL("=ROUND(1/3;0) ", Value(0));
790 CHECK_EVAL("=ROUND(1/3;1)", Value(0.3));
791 CHECK_EVAL("=ROUND(1/3;2)", Value(0.33));
792 CHECK_EVAL("=ROUND(1/3;2.9)", Value(0.33));
793 CHECK_EVAL("=ROUND(5555;-1)", Value(5560));
794 CHECK_EVAL("=ROUND(-1.1; 0)", Value(-1));
795 CHECK_EVAL("=ROUND(-1.5; 0)", Value(-2));
796 CHECK_EVAL("=ROUND(-1.5)", Value(-2));
797 CHECK_EVAL("=ROUND(1.1)", Value(1));
798 CHECK_EVAL("=ROUND(9.8)", Value(10));
799 }
800
testROUNDDOWN()801 void TestMathFunctions::testROUNDDOWN()
802 {
803 // ODF-tests
804 CHECK_EVAL("=ROUNDDOWN(1.45673;2)", Value(1.45));
805 CHECK_EVAL("=ROUNDDOWN(1;0)", Value(1));
806 CHECK_EVAL("=ROUNDDOWN(1)", Value(1));
807 CHECK_EVAL("=ROUNDDOWN(9;-1)", Value(0));
808 CHECK_EVAL("=ROUNDDOWN(-9;-1)", Value(0));
809 CHECK_EVAL("=ROUNDDOWN(9;0)", Value(9));
810 CHECK_EVAL("=ROUNDDOWN(-1.1)", Value(-1));
811 CHECK_EVAL("=ROUNDDOWN(-1.9)", Value(-1));
812 }
813
testROUNDUP()814 void TestMathFunctions::testROUNDUP()
815 {
816 // ODF-tests
817 CHECK_EVAL("=ROUNDUP(1.45673;2)", Value(1.46));
818 CHECK_EVAL("=ROUNDUP(1.1;0)", Value(2));
819 CHECK_EVAL("=ROUNDUP(1.9;0)", Value(2));
820 CHECK_EVAL("=ROUNDUP(1)", Value(1));
821 CHECK_EVAL("=ROUNDUP(9;-1)", Value(10));
822 CHECK_EVAL("=ROUNDUP(-9;-1)", Value(-10));
823 CHECK_EVAL("=ROUNDUP(9;0)", Value(9));
824 CHECK_EVAL("=ROUNDUP(-1.1)", Value(-2));
825 CHECK_EVAL("=ROUNDUP(-1.9)", Value(-2));
826 }
827
testSERIESSUM()828 void TestMathFunctions::testSERIESSUM()
829 {
830 CHECK_EVAL("SERIESSUM(2;0;2;{1;2})", Value(9)); //
831 CHECK_EVAL("SERIESSUM(2;0;2;{1;2;3;4})", Value(313)); //
832 CHECK_EVAL("SERIESSUM(2;0;2;{1;2;3;4;5;6;7})", Value(36409)); //
833 CHECK_EVAL("SERIESSUM(2;2;2;{1;6;5;4;3;2;7})", Value(127396)); //
834 CHECK_EVAL("SERIESSUM(3;0;2;{1;2;3;4})", Value(3178)); //
835 CHECK_EVAL("SERIESSUM(\"error\";0;2;{1;2})", Value::errorNUM()); // Text is not allowed
836 }
837
testSIGN()838 void TestMathFunctions::testSIGN()
839 {
840 CHECK_EVAL("SIGN(-4)", Value(-1)); // N < 0 returns -1
841 CHECK_EVAL("SIGN(4)", Value(1)); // N > 0 returns +1
842 CHECK_EVAL("SIGN(0)", Value(0)); // N == 0 returns 0
843 }
844
testSQRT()845 void TestMathFunctions::testSQRT()
846 {
847 CHECK_EVAL("SQRT(4)", Value(2)); // The square root of 4 is 2.
848 CHECK_EVAL("SQRT(-4)", Value::errorNUM()); // N > 0 returns +1
849 }
850
testSQRTPI()851 void TestMathFunctions::testSQRTPI()
852 {
853 CHECK_EVAL_SHORT("SQRTPI(1)", Value(1.77245385)); // TODO more digits / The square root of PI
854 CHECK_EVAL("SQRTPI(2)", Value(2.5066282746)); // The square root of 2PI
855 CHECK_EVAL("SQRTPI(-4)", Value::errorNUM()); // The argument must be non-negative
856 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETSQRTPI(2)", Value(2.5066282746)); // alternate function name
857 }
858
testSUBTOTAL()859 void TestMathFunctions::testSUBTOTAL()
860 {
861 CHECK_EVAL("SUBTOTAL(1;7)", Value(7)); // Average.
862 CHECK_EVAL("SUBTOTAL(2;8)", Value(1)); // Count.
863 CHECK_EVAL("SUBTOTAL(3;11)", Value(1)); // Count.
864 CHECK_EVAL("SUBTOTAL(11;33)", Value(0));
865 CHECK_EVAL("SUBTOTAL(12;33)", Value::errorVALUE());
866 CHECK_EVAL("SUBTOTAL(102;8)", Value(1)); // Count.
867 CHECK_EVAL("SUBTOTAL(111;33)", Value(0)); // Average.
868 CHECK_EVAL("SUBTOTAL(1111;33)", Value(0)); // Average.
869 }
870
testSUMA()871 void TestMathFunctions::testSUMA()
872 {
873 CHECK_EVAL("SUMA(1;2;3)", Value(6)); // Simple sum.
874 CHECK_EVAL("SUMA(TRUE();2;3)", Value(6)); // TRUE() is 1.
875 }
876
testSUMIF()877 void TestMathFunctions::testSUMIF()
878 {
879 // B3 = 7
880 // B4 = 2
881 // B5 = 3
882 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.
883 CHECK_EVAL("SUMIF(B3:B5;B4)", Value(2)); // Test if a cell equals the value in B4.
884 CHECK_EVAL("SUMIF("";B4)", Value::errorNUM()); // Constant values are not allowed for the range.
885 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.
886 CHECK_EVAL("SUMIF(B3:B10;1+1)", Value(2)); // The criteria can be an expression.
887 CHECK_EVAL("SUMIF(B3:B4;\"7\")", Value(0)); // TODO B3 is the string "7", but only numbers are summed.
888 }
889
testSUMIF_STRING()890 void TestMathFunctions::testSUMIF_STRING()
891 {
892 m_map->calculationSettings()->setUseWildcards(false);
893 m_map->calculationSettings()->setUseRegularExpressions(false);
894
895 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test\";Sheet2!B1:B32767)", Value(7));
896 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test1\";Sheet2!B1:B32767)", Value(9));
897 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test*\";Sheet2!B1:B32767)", Value(0));
898 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test?\";Sheet2!B1:B32767)", Value(0));
899 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test.*\";Sheet2!B1:B32767)", Value(0));
900 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test.+\";Sheet2!B1:B32767)", Value(0));
901 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\".*est.*1.*\";Sheet2!B1:B32767)", Value(0));
902 }
903
testSUMIF_WILDCARDS()904 void TestMathFunctions::testSUMIF_WILDCARDS()
905 {
906 m_map->calculationSettings()->setUseWildcards(true);
907 m_map->calculationSettings()->setUseRegularExpressions(false);
908
909 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test\";Sheet2!B1:B32767)", Value(7));
910 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test*\";Sheet2!B1:B32767)", Value(36));
911 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test?\";Sheet2!B1:B32767)", Value(20));
912 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test.*\";Sheet2!B1:B32767)", Value(0));
913 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test.+\";Sheet2!B1:B32767)", Value(0));
914 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\".*est.*1.*\";Sheet2!B1:B32767)", Value(0));
915 }
916
testSUMIF_REGULAREXPRESSIONS()917 void TestMathFunctions::testSUMIF_REGULAREXPRESSIONS()
918 {
919 m_map->calculationSettings()->setUseWildcards(false);
920 m_map->calculationSettings()->setUseRegularExpressions(true);
921
922 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test\";Sheet2!B1:B32767)", Value(7));
923 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test*\";Sheet2!B1:B32767)", Value(7));
924 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test?\";Sheet2!B1:B32767)", Value(7));
925 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test.*\";Sheet2!B1:B32767)", Value(36));
926 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\"test.+\";Sheet2!B1:B32767)", Value(29));
927 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\".*est.*1.*\";Sheet2!B1:B32767)", Value(28));
928
929 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\".*\";A1:A32767)", Value(0));
930 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\".*\";B1:B32767)", Value(5));
931 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\".*\";Sheet2!A1:A32767)", Value(0));
932 CHECK_EVAL("=SUMIF(Sheet2!A1:A32767;\".*\";Sheet2!B1:B32767)", Value(91));
933 CHECK_EVAL("=SUMIF(A1:A32767;\".*\";A1:A32767)", Value(0));
934 CHECK_EVAL("=SUMIF(B1:B32767;\".+\";B1:B32767)", Value(5));
935 }
936
testSUMSQ()937 void TestMathFunctions::testSUMSQ()
938 {
939 CHECK_EVAL("SUMSQ(1;2;3)", Value(14)); // Simple sum.
940 CHECK_EVAL("SUMSQ(TRUE();2;3)", Value(14)); // TRUE() is 1.
941 CHECK_EVAL("SUMSQ(B4:B5)", Value(13)); // 2*2+3*3 is 13.
942 }
943
testTRUNC()944 void TestMathFunctions::testTRUNC()
945 {
946 // ODF-tests
947 CHECK_EVAL("=TRUNC(10.1)", Value(10));
948 CHECK_EVAL("=TRUNC(0.5)", Value(0));
949 CHECK_EVAL("=TRUNC(1/3;0)", Value(0));
950 CHECK_EVAL("=TRUNC(1/3;1)", Value(0.3));
951 CHECK_EVAL("=TRUNC(1/3;2)", Value(0.33));
952 CHECK_EVAL("=TRUNC(1/3;2.9)", Value(0.33));
953 CHECK_EVAL("=TRUNC(5555;-1)", Value(5550));
954 CHECK_EVAL("=TRUNC(-1.1)", Value(-1));
955 CHECK_EVAL("=TRUNC(-1.5)", Value(-1));
956 }
957
958 QTEST_MAIN(TestMathFunctions)
959