specify non-contiguous cell range as arguments to TTEST()

[ttest_guy]

Hello,

I have data from two groups interleaved in the same column. For the sake of
simplicity, let's imagine group A: rows 1-3,7-9; group B: rows 4-6,10-12. I
want to compare groups A and B with a ttest -witout having to displace values
everywhere-. However, I can not specify ranges, otherwise the ttest gets too
many arguments (the coma for specifying ranges is taken as the coma that
separates the different arguments. I tried using named selections
(shift+click on cells of group A, then name 'groupA' at the left of the
function bar, similar for group B), but it still doesn't work. I still get
ERROR IN VALUE.
Am I missing a simple workaround for that ?

 

[Héctor Miguel]

hi, !

assuming the test groups in ranges: "A" = [B2:B4,B7:B9] and "B" = [C2:C4,C7:C9]

=ttest(subtotal(9,offset(b2,{0;1;2;5;6;7},)),subtotal(9,offset(c2,{0;1;2;5;6;7},)),1,2)

(same result as using contiguous ranges)

hth,
hector.

__ OP __

 

[Lori Miller]

let's imagine group A: rows 1-3,7-9; group B: rows 4-6,10-12

Other methods...

1) With data in column B, maybe try with Ctrl+Shift+Enter {CSE}:

=TTEST(IF({1,0},B1:B3,B7:B9),IF({1,0},B4:B6,B10:B12),1,2)

This relies on data being regularly spread and could be extended
by using CHOOSE({1,2,3,...},...) instead of IF({1,0},...).

2) More generally insert a group column in column A which contains
"A" in rows 1-3,7-9 and "B" in rows 4-6,10-12, then try {CSE}:

=TTEST(IF(A1:A12="A",B1:B12),IF(A1:A12="B",B1:B12),1,2)