Thread: Selecting multiple ranges as a single argument

How do you select multiple ranges as a single function argument?

For example, I want to do a t-test between two sets of data: First set is A1:A10,A15:A20. Second set is B1:B10,B15:B20. I have some other non-blank cells in A11:A14 and B11:B14.
The ttest formula requires me to select each set as a single argument, so I can't use ctrl-click to select A1:A10 and A15:A20 as "array 1" (since that inserts a ',' which excels interprets as a separator between arguments). I know the formula ignores blank cells, but since rows 11-14 are not blank I cannot just select A1:A20.

Right now I can only think of copying out the values to another part of the worksheet so that I have two contiguous rows and pointing the formula to that, but surely there is a more elegant way of going about this problem?

You could select the ranges of cells that you want and give them a name (Type a name into the box at the top-left of the worksheet which normally just contains the cell reference), but I can tell you now that TTEST doesn't like this and returns an error.

Calling TTEST from a macro which has joined split ranges also produces an error, so it seems likely that the function is just not set up to cope with anything other than straightforward ranges.

Assigning the values of the range to an array and then calling TTEST on those values does work, but seems to return the wrong result.

In short I'd say your options are:

1. Change your spreadsheet design so that the numbers you want to test are in a contiguous block or

2. Use maths formula to work out the TTEST result yourself based on the cells you want. I would help you with this, but I've got no idea of the maths behind the test.

Originally Posted by Andrew-R

You could select the ranges of cells that you want and give them a name (Type a name into the box at the top-left of the worksheet which normally just contains the cell reference), but I can tell you now that TTEST doesn't like this and returns an error.

Calling TTEST from a macro which has joined split ranges also produces an error, so it seems likely that the function is just not set up to cope with anything other than straightforward ranges.

Assigning the values of the range to an array and then calling TTEST on those values does work, but seems to return the wrong result.

In short I'd say your options are:

1. Change your spreadsheet design so that the numbers you want to test are in a contiguous block or

2. Use maths formula to work out the TTEST result yourself based on the cells you want. I would help you with this, but I've got no idea of the maths behind the test.

Thanks, Andrew-R!

I did try defining names, but I realized that at the end of the day it just inserts the commas just like the ctrl-click method, and thus gives the same error. Thanks for telling me that even macros could not solve the problem. The specific ttest formula I need has a couple of intermediate steps, and doing it manually doesn't seem like it's worth the effort.

Seems like better spreadsheet design is the way to go.