https://bz.apache.org/bugzilla/show_bug.cgi?id=62836
--- Comment #4 from Yegor Kozlov <ye...@dinom.ru> ---
>> - So if all x and y values are the same, Excel just returns that value, no
>> matter what?
I concluded it empirically and it seems to be true. The only exception are cass
when Excel returns !REF#, but the code in Trend performs this check before
evaluation.
>> If the y's are different but all x's are the same, Commons Math works but
>> gives a different answer than Excel.
>> For instance the test "All x values the same" (B65; currently skipped) gives
>> -32 but Excel gives 12,
>> which is simply the average of the two y values (20 and 4). Is this just
>> mathematically illegal,
>> and Excel chooses to simply average the y values or what is happening here?
A good catch. If all x values are the same then Excel really returns the
average.
TREND({20, 4}, {3, 3}) 12
TREND({20, 4, 3},{3, 3, 3}) 9
TREND({20, 4, 3, 3},{3, 3, 3, 3}) 7.5
TREND({20, 4, 3, 3, 5},{3, 3, 3, 3, 3}) 7
changing an x value just beyond significance, e.g. 3 -> 3.0000001 changes the
behavior. Excel starts performing regression and evaluates to 20
TREND({20, 4},{3.0000001, 3}) 20
TREND({20, 4, 3},{3.0000001, 3, 3}) 20
TREND({20, 4, 3, 3},{3.0000001, 3, 3, 3}) 20
TREND({20, 4, 3, 3, 5},{3.0000001, 3, 3, 3, 3}) 20
>> When there is only one y value, Excel generally just returns that,
>> but it does still calculate the linear regression if the constant is set to
>> false in order to force the line to go through zero.
Another good catch. I guess in this case the regression parameters will be {0,
y/x} where y/x is the slope.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org
