Is it possible to do statistical analysis among non-contiguous rows/columns?

[AidanofVT]

Hello.
I have a file with several similar sheets. These sheets are simply columns of numbers. I want to do statistical analysis (specifically, Analysis of Variance) comparing the sum of one column in all sheets, like “(all sheets)B3:B40,” to another column in all sheets, like “(all sheets)C3:C40.”

I have no problem combining these ranges for things like averaging:

=AVERAGE($'23-086'.B3:B37, $'23-087'.B3:B41, $'23-088'.B3:B41, $'23-089'.B3:B41, $'23-090'.B3:B41, $'23-091'.B3:B41)

But this formatting doesn’t seem to work in Analysis of Variance. Indeed, even within a single sheet, it only seems able to analyze contiguous rows/columns.

I understand that I could simply reformat things to bunch together the data I want to compare, but that feels like an inelegant solution to me. I’m not an advanced user, so I’m wondering: is there some special syntax that would allow me to accomplish this?

[Villeroy]

Form follows function.

[LeroyG]

Try with “~” instead of “,” or “;”.
=AVERAGE($Sheet1.C1:C5~$Sheet2.C1:C5)


EDIT: Just trying. Not sure what do you need (sorry, but I’m not an statistics specialist).
=VAR(Sheet1.B1:B4~Sheet2.B1:B4;Sheet1.C1:C4~Sheet2.C1:C4)
This gives the same value if the data are in the same sheet.
VAR_split data.ods (8.8 KB)

[AidanofVT]

Looking at the documentation, that looks like it should work, but in the case of Analysis of Variance, unfortunately it doesn’t.

[eeigor]

Try to use AGGREGATE function.
https://help.libreoffice.org/7.4/en-US/text/scalc/01/04060184.html?&DbPAR=SHARED&System=UNIX#avedev
225 agrs to include??