Select non-contiguous cells within a T.Test-CodePudding

I think that it is not possible after some search, but here is my question:

I have a row of values, which correspond to a quantity we calculate every day. We alternate the experiments between product A and product B.

Now, I want to run student tests on the averages of 3 consecutive experiments, on product A versus product B. A toy example can be found below:

I would like to run a T.Test to compare the sets of values (B2,D2,F2) to (C2,E2,G2). However, nothing seems to work, at least not the following:

=T.Test((B2,D2,F2);(C2,E2,G2);2;1)

=T.Test((B2;D2;F2);(C2;E2;G2);2;1)

=T.Test({"B2";"D2";"F2"};{"C2";"E2";"G2"};2;1)

Note that I was able to get my test results by reorganizing the data so that the ranges are contiguous, but this has definitely made be curious to see if there is a solution.

CodePudding user response：

For those with access to HSTACK() (or VSTACK() for that matter), try:

=T.TEST(HSTACK(B2,D2,F2),HSTACK(C2,E2,G2),2,1)

If not available, try:

=T.TEST(INDEX(B2:G2,{1,3,5}),INDEX(B2:G2,{2,4,6}),2,1)

EDIT: for those with older versions of Excel, as per @JosWoolley you'd indeed need to pursuade Excel to use arrays:

=T.TEST(INDEX(B2:G2,N(IF(1,{1,3,5}))),INDEX(B2:G2,N(IF(1,{2,4,6}))),2,1)