https://bugs.documentfoundation.org/show_bug.cgi?id=151806
Bug ID: 151806
Summary: : Unable to edit function formulas in T-Test
statistical function
Product: LibreOffice
Version: 7.4.2.3 release
Hardware: x86-64 (AMD64)
OS: Linux (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
While trying to edit the automatic formulas produced by running the "built-in"
Paired T-test statistical function, I have found that there are three complex
formulae used within the T-test statistical function that *cannot be edited.*
At all.
T-test is comprised of several sub-values,
for which the formulas CAN be edited by the user.
Those values produced by T-test with zero editing problems are listed here:
Alpha
Hypothesized Mean Difference
Mean
Variance
Observations
Pearson Correlation
t Stat
P (T<=t) one-tail
t Critical one-tail
P (T<=t) two-tail
t Critical two-tail
However, there are three values that use very complicated formulas.
Those three complicated formulas are labelled as:
Observed Mean Difference
Variance of the Differences
df
These three formulas *cannot* be user-edited and continue to work.
If you change *anything* by editing any part of these three formulas,
they will immediately stop producing correct results.
For example, the "Observed Mean Difference formula, used within the T-test
is: =AVERAGE(IF(ISODD(IF(ISNUMBER($Sheet2.$B$2:$B$11), 1, 0) *
IF(ISNUMBER($Sheet2.$C$2:$C$11), 1, 0)), $Sheet2.$B$2:$B$11 -
$Sheet2.$C$2:$C$11, "NA"))
The problem: For example, if you edit the above formula and change the Letter
"V" in the first function AVERAGE to "v", lower case, and press <ENTER>, then
the calculated value immediately changes to a WRONG value!
And if you think the lower case "v" might not work, go ahead try using
a capital "V" if you prefer. The result is the same - the calculated result
changes to a *bad* value.
Attempts to fix: I have updated my copy of Libreoffice to the newest version
7.4.2.3, running under Ubuntu Linux 22.04.1. I even tried testing this
Calc T-test formula editing issue on a different machine running an earlier
version of both Ubuntu and Libreoffice - I find that the attempted system
changes did NOT resolve the problem.
Replicating this problem is super simple.
1) Create a new spreadsheet in Calc
2) Copy the raw data below to cell starting at A1:
Item # DATA 1 DATA 2
1 21251 21251
2 21272 21248
3 21144 21348
4 21301 21150
5 21193 21276
6 21238 21226
7 21240 21034
8 21137 21181
9 21299 21299
10 21040 21247
Row 1 is a header row.
The raw data we will process is in columns B and C, rows 2 thru 11:
3) Using your cursor, highlight the twenty data numbers to be T-tested
(Cells B2:C11)
>From your Calc Menu select "Data", "Statistics", "Paired t-test".
Because you've already highlighted the data to be tested, the first
two required entry fields are already populated; all you have to do
now is:
4) Select the cell where you want the T-test results to go to. Click on cell
E1.
Calc will fill-in the t-test results into your spreadsheet, starting at
Cell E1.
This is what you should see:
Paired t-test
Alpha 0.05
Hypothesized Mean Difference 0
Variable 1 Variable 2
Mean 21211.5 21226
Variance 6918.05555555556 7658.66666666667
Observations 10 10
Pearson Correlation -0.198090290379547
Observed Mean Difference -14.5
Variance of the Differences 17460.5
df 9
t Stat -0.347008141418145
P (T<=t) one-tail 0.368280585837927
t Critical one-tail 1.83311293265624
P (T<=t) two-tail 0.736561171675855
t Critical two-tail 2.26215716279821
Sorry if this notepad screen changed formatting. But to continue . . .
Pay special attention to Cell F9 ("Observed Mean Difference".
It has a T-test calculated value of -14.5
Now we get to the good part!
Click on cell F9.
Press F2 key to Edit the formula in cell F9.
Remember I said the formula is complex? Here it is again:
=AVERAGE(IF(ISODD(IF(ISNUMBER($Sheet2.$B$2:$B$11), 1, 0) *
IF(ISNUMBER($Sheet2.$C$2:$C$11), 1, 0)), $Sheet2.$B$2:$B$11 -
$Sheet2.$C$2:$C$11, "NA"))
Highlight the left part of the long formula near the beginning, specifically
the word "AVERAGE". Over-type those seven letters (AVERAGE) with exactly
the same letters "AVERAGE". You say "But Bert, I have changed nothing!"
Yes you did.
Press ENTER to save your change. The formula should be identical to what was
there originally. All you did is replace AVERAGE with AVERAGE. The calculated
results should not change.
But the result of the calculation DID change!
Your answer changed from the original correct -14 to an incorrect -44.
Now we can both say together "What the heck just happened??!!"
You'll find that you can edit *any* part of this formula, with a
simple over-type of *any* letter(s) or numeral(s), and that edit will change
the initial answer from -14 to -44. If you edit a cell address in the formula,
and don't even change the value, your results will turn out to be wrong (for
example, change cell reference $C$2:$C$11 to $C$2:$C$11, and the incorrect
answer will suddenly appear, although no part of your formula changed).
This same bad action affects the other two complex formulas in cells
F10 and F11 too.
But the bug appears to only affect complex formulas in T-test; it does not mess
up the other more simple formulas.
The BUG in T-test only appears to affect these three complex formulas.
I suspect that there are likely OTHER Statistical functions in Calc that may
harbor this same issue, where simple formula can be dited with normal results,
but if the formula is complex, you will possibly find Calc screws us the
initial calculated result with some off-the-wall values, just like you see
here.
Can someone please fix this? I'm in the middle of a quantum physics
experiment, and I need to calculate valid T-test functions, and I HAVE to be
able to edit these complex formula without that action screwing up the answers!
Thank you,
Bert Pool
--
You are receiving this mail because:
You are the assignee for the bug.
