TTest with values in non-contiguous ranges

[Kristina]

Hi!
I need to perform a T-Test between two groups of values that can be
held in non-contiguous ranges, is this possible?

For example the two groups for the T-Test could be:
group1: A1:A3,A5:A6
group2: B1:B6

When I define the ranges for the ttest using Excel's "formula editor" I
get the following for the result cell:
=TTEST(A1:A3,A5:A6,B1:B6,2,3)

The above generates an error saying that to many arguments have been
entered. I have tried to enclose the non-contiguous range in different
ways using () and {} which doesn't work. I have also tried using a
named ranges, but without luck.

In the formula editor, the groups for the T-test are called Array1 and
Array2, does the problem have to do with defining arrays?

Any help would be appreciated.

Regards,
Kristina

 

[Domenic]

I'm not familiar with that particular function, but see if the following
helps...

=TTEST(SUBTOTAL(9,OFFSET(A1:A6,{0;1;2;4;5},0,1)),B1:B6,2,3)

Note that the array constant {...} determines which cells to evaluate.

Hope this helps!

 

[Conrad Carlberg]

Hi Kristina,

Don't worry about the missing value in A4 -- Excel ignores it. Just use:

=TTEST(A1:A6,B1:B6,2,3)

But do beware of the Behrens-Fisher problem. Excel's use of Welch's
correction is sensible but imperfect. Much better to get two equal sized
samples (Box, 1954, Annals of Mathematical Statistics, p. 290).

C^2
Conrad Carlberg

 

[Kristina]

Thanks for your reply, I tried your function and it seems to work.
However it is a bit complicated, is this really the only way to do it?
Do you know why a non-contiguous range cannot be entered into the
t-test-function in the first place? I'll have to generate this formula
from a VBA-script and it would of course be simpler to define the
function if it was not a combination of several as well as it is would
be easier for the end-user to see what the function actually does. But
if this is the only way I'll start to put the code in.

Regards,
Kristina

 

[Kristina]

Thanks for your reply, but your solution will not solve my problem. The
thing is that there is a value in the cells that shouldn't be included
in the ttest formula. These values are marked as outliers but cannot be
removed from the datasheet.
Since values can be excluded it is not possible to ensure that the
sample sizes are equal. I don't know what other T-test to use then the
one supplied by Excel. In what way is the Excel T-test imperfect?

Regards,
Kristina

 

[Jerry W. Lewis]

=TTEST(IF(ROW(A1:A6)<>4,A1:A6),B1:B6,2,3)
Array entered (Ctrl-Shift-Enter) would also work, and you may consider
it less complicated.

Jerry

 

[carlbergc]

Hi Kristina,

It's not Excel itself but the Welch correction that's a problem.
You call for that correction when you supply 3 as the fourth argument
to Excel's TTEST function, and the sample sizes are unequal. The
t-test, as developed by Gosset, assumes homogeneity of variance -
that is, that the populations from which the two samples are taken are
equivalent as to their variability on the measure you're interested
in.

When you've taken samples of the same size, it turns out that you
don't have to worry about violating that assumption.

But when they're of different sizes, you have to worry about it. When
the larger sample also has the larger variance, the t-test is
conservative as to type I errors; when the larger sample has the
smaller variance, the t-test is liberal as to type I errors.

Welch's correction helps with that problem, but it's not a perfect
solution. If you can arrange for equal sample sizes without
dismembering reality, you're better off.

C^2
Conrad Carlberg