From 26dc6796dfe7950566d935f12eb17171cc042051 Mon Sep 17 00:00:00 2001 From: =?utf8?q?C=C3=A9dric=20Walter?= Date: Mon, 4 Nov 2013 21:27:12 +0000 Subject: [PATCH] Bug 54535: Bug in INTERCEPT and SLOPE, review and integration test added to FormulaEvalTestData.xls git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1538772 13f79535-47bb-0310-9956-ffa450edef68 --- .../functions/LinearRegressionFunction.java | 11 +++++------ .../ss/formula/functions/TestIntercept.java | 18 +++++++++--------- .../poi/ss/formula/functions/TestSlope.java | 18 +++++++++--------- test-data/spreadsheet/FormulaEvalTestData.xls | Bin 174592 -> 174592 bytes 4 files changed, 23 insertions(+), 24 deletions(-) diff --git a/src/java/org/apache/poi/ss/formula/functions/LinearRegressionFunction.java b/src/java/org/apache/poi/ss/formula/functions/LinearRegressionFunction.java index a676f5b347..4fed7f9e98 100644 --- a/src/java/org/apache/poi/ss/formula/functions/LinearRegressionFunction.java +++ b/src/java/org/apache/poi/ss/formula/functions/LinearRegressionFunction.java @@ -68,7 +68,7 @@ public final class LinearRegressionFunction extends Fixed2ArgFunction { super(1); _value = value; } - @Override + protected ValueEval getItemInternal(int index) { return _value; } @@ -80,7 +80,7 @@ public final class LinearRegressionFunction extends Fixed2ArgFunction { super(1); _ref = ref; } - @Override + protected ValueEval getItemInternal(int index) { return _ref.getInnerValueEval(); } @@ -95,7 +95,7 @@ public final class LinearRegressionFunction extends Fixed2ArgFunction { _ae = ae; _width = ae.getWidth(); } - @Override + protected ValueEval getItemInternal(int index) { int rowIx = index / _width; int colIx = index % _width; @@ -114,8 +114,8 @@ public final class LinearRegressionFunction extends Fixed2ArgFunction { ValueEval arg0, ValueEval arg1) { double result; try { - ValueVector vvX = createValueVector(arg0); - ValueVector vvY = createValueVector(arg1); + ValueVector vvY = createValueVector(arg0); + ValueVector vvX = createValueVector(arg1); int size = vvX.getSize(); if (size == 0 || vvY.getSize() != size) { return ErrorEval.NA; @@ -137,7 +137,6 @@ public final class LinearRegressionFunction extends Fixed2ArgFunction { ErrorEval firstXerr = null; ErrorEval firstYerr = null; boolean accumlatedSome = false; - double result = 0.0; // first pass: read in data, compute xbar and ybar double sumx = 0.0, sumy = 0.0; diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestIntercept.java b/src/testcases/org/apache/poi/ss/formula/functions/TestIntercept.java index 942ab6b019..34b6d85794 100644 --- a/src/testcases/org/apache/poi/ss/formula/functions/TestIntercept.java +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestIntercept.java @@ -59,7 +59,7 @@ public final class TestIntercept extends TestCase { public void testBasic() { Double exp = Math.pow(10, 7.5); - ValueEval[] xValues = { + ValueEval[] yValues = { new NumberEval(3+exp), new NumberEval(4+exp), new NumberEval(2+exp), @@ -67,9 +67,9 @@ public final class TestIntercept extends TestCase { new NumberEval(4+exp), new NumberEval(7+exp), }; - ValueEval areaEvalX = createAreaEval(xValues); + ValueEval areaEvalY = createAreaEval(yValues); - ValueEval[] yValues = { + ValueEval[] xValues = { new NumberEval(1), new NumberEval(2), new NumberEval(3), @@ -77,7 +77,7 @@ public final class TestIntercept extends TestCase { new NumberEval(5), new NumberEval(6), }; - ValueEval areaEvalY = createAreaEval(yValues); + ValueEval areaEvalX = createAreaEval(xValues); confirm(INTERCEPT, areaEvalX, areaEvalY, -24516534.39905822); // Excel 2010 gives -24516534.3990583 } @@ -86,9 +86,9 @@ public final class TestIntercept extends TestCase { * number of items in array is not limited to 30 */ public void testLargeArrays() { - ValueEval[] xValues = createMockNumberArray(100, 3); // [1,2,0,1,2,0,...,0,1] - xValues[0] = new NumberEval(2.0); // Changes first element to 2 - ValueEval[] yValues = createMockNumberArray(100, 101); // [1,2,3,4,...,99,100] + ValueEval[] yValues = createMockNumberArray(100, 3); // [1,2,0,1,2,0,...,0,1] + yValues[0] = new NumberEval(2.0); // Changes first element to 2 + ValueEval[] xValues = createMockNumberArray(100, 101); // [1,2,3,4,...,99,100] confirm(INTERCEPT, createAreaEval(xValues), createAreaEval(yValues), 51.74384236453202); // Excel 2010 gives 51.74384236453200 @@ -136,8 +136,8 @@ public final class TestIntercept extends TestCase { confirmError(areaEvalX, areaEvalX, ErrorEval.REF_INVALID); // search for errors array by array, not pair by pair - confirmError(areaEvalX, areaEvalY, ErrorEval.REF_INVALID); - confirmError(areaEvalY, areaEvalX, ErrorEval.NULL_INTERSECTION); + confirmError(areaEvalX, areaEvalY, ErrorEval.NULL_INTERSECTION); + confirmError(areaEvalY, areaEvalX, ErrorEval.REF_INVALID); } /** diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestSlope.java b/src/testcases/org/apache/poi/ss/formula/functions/TestSlope.java index 2ea0332eda..db5fee109b 100644 --- a/src/testcases/org/apache/poi/ss/formula/functions/TestSlope.java +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestSlope.java @@ -54,7 +54,7 @@ public final class TestSlope extends TestCase { public void testBasic() { Double exp = Math.pow(10, 7.5); - ValueEval[] xValues = { + ValueEval[] yValues = { new NumberEval(3+exp), new NumberEval(4+exp), new NumberEval(2+exp), @@ -62,9 +62,9 @@ public final class TestSlope extends TestCase { new NumberEval(4+exp), new NumberEval(7+exp), }; - ValueEval areaEvalX = createAreaEval(xValues); + ValueEval areaEvalY = createAreaEval(yValues); - ValueEval[] yValues = { + ValueEval[] xValues = { new NumberEval(1), new NumberEval(2), new NumberEval(3), @@ -72,7 +72,7 @@ public final class TestSlope extends TestCase { new NumberEval(5), new NumberEval(6), }; - ValueEval areaEvalY = createAreaEval(yValues); + ValueEval areaEvalX = createAreaEval(xValues); confirm(SLOPE, areaEvalX, areaEvalY, 0.7752808988764045); // Excel 2010 gives 0.775280898876405 } @@ -81,9 +81,9 @@ public final class TestSlope extends TestCase { * number of items in array is not limited to 30 */ public void testLargeArrays() { - ValueEval[] xValues = createMockNumberArray(100, 3); // [1,2,0,1,2,0,...,0,1] - xValues[0] = new NumberEval(2.0); // Changes first element to 2 - ValueEval[] yValues = createMockNumberArray(100, 101); // [1,2,3,4,...,99,100] + ValueEval[] yValues = createMockNumberArray(100, 3); // [1,2,0,1,2,0,...,0,1] + yValues[0] = new NumberEval(2.0); // Changes first element to 2 + ValueEval[] xValues = createMockNumberArray(100, 101); // [1,2,3,4,...,99,100] confirm(SLOPE, createAreaEval(xValues), createAreaEval(yValues), -1.231527093596059); // Excel 2010 gives -1.23152709359606 @@ -131,7 +131,7 @@ public final class TestSlope extends TestCase { confirmError(areaEvalX, areaEvalX, ErrorEval.REF_INVALID); // search for errors array by array, not pair by pair - confirmError(areaEvalX, areaEvalY, ErrorEval.REF_INVALID); - confirmError(areaEvalY, areaEvalX, ErrorEval.NULL_INTERSECTION); + confirmError(areaEvalX, areaEvalY, ErrorEval.NULL_INTERSECTION); + confirmError(areaEvalY, areaEvalX, ErrorEval.REF_INVALID); } } diff --git a/test-data/spreadsheet/FormulaEvalTestData.xls b/test-data/spreadsheet/FormulaEvalTestData.xls index f06572b62525ff42cb865aff489bf25f6ee4b5de..afe3af10a0bcfb94cba54b3e11a127227e2b78ee 100644 GIT binary patch delta 3427 zcmZA432+ou8VB(Id)+f3_rZirI3h^}K?pE}2?+_1YZ3wm0wHoJu%HAI2oNBka%H%5 z1E|CnUI-u{hg(DijZ3fVvV<$Lf)*&NxC)|BQd=yGxKd~oX8$w8Sm303es8{h)7|fV zU%#HX&xN?pg&e5TEr^Xhzss1XKQGHB8Ety0h&B@SxbSLM^!Z(1d@Szmwmcz1eev&? z#`xl+X0$)QtNEry3TwZNo1V!T%|Bbm>c-HZ=;pl8=i3`~@!`gIagid)IGE7Q`T~ZY zn6rp|e)ytwkovhoEr3)UbO7xRtL*>N&=oG~+d zvhj6xU*lwUTysRul&soiSwI-ScbC3Emka_z1Q0$L*h&}EU>;Zu0XEP@)S>}D(pA)v z4IHLxs7+^^j-+Whz;U{brdWXXxxj059qnl4@$z^)D{8Tfjj$l#2fBh*@=&0eZl`@XGFuU_i`G$lI1n|A`)D1t zN7Mq^aNy+#;91%(68JZLhE97F_#gd*PL1NIih&FC1TBjOaz_AX=pkAh!j!!*LdJhw4T;>?ZT5y z;0(}ZG%SIspetzk_e(oZSViqeT3G5wV0uvW?@0Y~U(G+*-jlR10z zG9ASlnf(XgU-UaVhP6>Xh57%4jZ0I25iE|eQ-NdjEFHok8TKS_kRGEB)15W%^qCwb{gM{3CZ^5;KBi~rVAjN=Rlr~989JCXF>E$3oVL*3tckvJ zm}|P5Mzbc8<^nI#h6*+;EDBN0JktgA68(i%ur@Z#WA16}d|)MO<8^9dEp%PL+m&vm z!K?)jt)jDPfb;Ym&1FsGFJuwY*Nu|eJ+((U+{>F;F$VzS7)$XqllD1q`U=l>jl=(* z3&F3zt3u#TkiZz5z<_vx*$Dz~CJ9*k2qZY^K!Fbi3EapQn3XTEzfizaEHHYkz^Tas z&(0E#4is?%-C!=^>t2lv@#ukd}}ZUn8_7v?VuP_`B=u z-qP^?rVWv2vblh2TA;hP*Kglr_d5uIO_Rt)KaE8#Blt$opDsRj2~AzyC%Ti!CLifdqBlR4`!W0+X%c-IyvZ&>9vfHiAp2Nq+XzXTQi4iDu<~F*jOB^+k*5N%SLP0LeNc9Hh$6Hh=Kk5z&W1?Wh+s z^{BY3Z#pSXi%j*=NfFmOi|Y$34y-;5<~D>ItI)UpW5KN;g@w3R!Hb2LGUCgT&L{p?MfpL89tw;UQ7mUOX*L)e# z0~vWRfmyx;##y`*{@(b}t126AvOB+i_g=mqOkkETfp(UAE=qaX-UQkoIF~Ph_6Lq< z?zk>8WS2IsVkx3UEW)?qTeOJN=!^*8=1u9X8)A<`Xyz9FNZ{>k{SG;cN35hQJ$)N>< z*8#y&W-C;~EXk7y$~SovZ;Z13P$bdxnD34@OGn5f zG1hV8CDu;#Myk!aDYBbYxtB_pI=)Ui42Q1EA2rohCx=_O93#j`Tp4Fv(rqG2EnF*0 ze7!`By2ZAI4-SL{I(QdZe4&1>l)a2(X}8_=csyL7;gt)qPh*rEq4q>P{ZyH!r3Txzz>l|f;qST1YjUPB7yUMFj;e01W) zP}Md=4pYb0$$nOYf%#IkE#(~RY)1x;C$_1D%j8I3uh6%-hrchpFVkbl?OLP`%e~7N zEDKHLHp)cxp^{PRqLN83w-))QwzxIf->7LVwtFZ09Uj8#yY$cZ+Q#|TWb@Y-r7k*E zp&b2S92p%5lyl}io^H~vJZojVG8$y>(EIkOQw=ibfk21NzY9$L zec+AFa;EyMQ9A0Y!*n~#S+zCFl=^Cy8qz2m>Z?;MtX?=&cB3q*eXcCBTd7`G@JiOO z*QY)AVXiliyqmFO(T>%f5C3iCd)L#S?x-Hw6^PFYPjqKiUva&9ePKdfeaDzUeDCEK zOHOKf$(oeIr`BB=VhhC0*47hiM?PA7pz`Ua9&eVn1>&WPzq_&VPHb?=+P@bToE_6W z)ttUnJ}wVdNjFPuwQPtw_0lUHw4%^qf6SQr-ut~)nP!hQ^1QBccF7ji@~U(z_YPST z*34_&$j$rOv_lq&s6gVa2N|lCX4?*F6)I=D-16AK^nqy^>HSkP2BsCHItQfQza@o- zxJKm-PD(9E&F^23o99e*rMWU(1NOeE)H5nxi Z?v^eQj048!-hY=g-bVdR_a5oi{|^wBiXZ?0 delta 3164 zcmZA43sh8f9tZI6|2KCA7!*;+!50v#<|AYXP!mWV!$_uRY8K`Ld_wUOQPORj5j8C{ zff28j`HEa$Yvin6&uKk|l}nE`EPAHfKmYr?kGcQf z|M$OhaCceVUDmb=%{+C$i%GjZOSSbPvpqMoX)4-tr)KMUVcXWE-CvwhKOZ(fs=@+s zcfz(nJfOGREXvaD>-L!!dVjLa)V!6Et71I1gg%yySkaiB=1oZ$n>e;}CHU!uf)$Hu zSJsu4S1p;bXj$E|b+F-bsss)>%d1MO7A%@nKL6)Kci_1^&(85Sx$=`X+qqgxW>60{ZI&h2bpudd+VrBr} zQ4g)@$LV^AS)sq9v-$%!=x4NiCg2>vKK&z|{xD!42)s}C(fmgMSK=UMfSqQVQp~A{ z=Ty+u)HVzFk+#s<1VDQfxJcJgPA9b490grRb66{uIY2wzK=WBEwzzi z&e3gjI%~sL!ukJ*o#qmzg~btG3cN+v(EC^jXbWAm zkXd3)oTDvt`XV5THPJ~o(!#~eC~KmFuA!rs0NwQH5*Jg*;;@$i-_rebA&cYAa^Oq) z3Z2W^hOMn>Eoz zH`9q_)m%<23NNjqLuy#<^bfR*wUM`!H>ZE54XlmgS}rrz!bfxyEn3Fq$y#`wR?tCp zz~}U7n#-EFO513$=VjNct~w64=k_GQ2>y_EE-P^j_JFI6SCPMS|htoc&(c)QnAeE6(F!|y!)Sa-! z;@NW`BM{MiiEwtrZ?Sm4IiUApD8&@+i-GgPKwh@tM84L+j`zb8so~8XMMq96@lNU3 zmZB0pm#!so8+m;wxAz3L#euEczxDq9`qG6eQSUcL-A$Zx)DxN|5x4YHbJQfwCnlfK zXRcbWMcslGu^dk>Qd$4T@C9nGNjk&yvx`-%X&|DEON-SRhf=z6P~Ano4ia`qC37z$ z$bukEhg1rK?T6G|VYg#(uYxkJ98#^q;Fi}^I(aw9?bp;erHp&qRCRA<()YKk5u~eK zjV6(&l$~S+sXP@3ww_X>82mVZA#bVM+Sd2fSzUFg_f@w3mkwo19>*1)$;SD|eQXXk z)GiMH&TASj*@s9B@6NZR{Y z%nu93KI)Iu7=JC5Ar^0E!!)J4H%PvoUoWX1yQYQL3crt^tt-S~*4VLlsvB&c3tFtQ z>dRNi6x~tJA@tX;vTx=~6Oo}4%pn9*zGXeF^$0gNh-zqNIyIj<@eBh)z~BKELs0R;{1^m*V2Dw+OEzn&S|~9bzP~$blfE9CJcXR zM`G?S_Zxf99=&`b6z`5+dGeC?od^E3*>+?`{l0&O;w8(}{!hQpntV9vsqY8X#cl}2 zANsVo@MiSN0<qx4#JVl3fxPUJ+WByQI!? zf}?4HabuTQ)ZLDPj8sQ%L25y;@ir6kvy%$O6l6HZq&e+*`Du>y{B(c2C(rn9k3=i~ fI@jOaA#WaQ6^Dv&U!NEFf560IPBYxEid*|H