Using Arrays in the Excel Formula

Question

I am wondering if in Excel we have command like ones or range command.

I am using the following command in one of my experiment :

=TTEST(A1:A5, {1,1,1,1,1},2,1)

However, if the iterations of the experiment increase, you have a really big formula. So, Any idea if I can write the formula in a more elegant way so that I donot have to repeat 1 as the number of experiments.

Thanks

D Stanley · Accepted Answer · 2013-01-12 05:01:17Z

5

IT's a little ugly, but you can use a little trick with the ROW function to generate a series of ones:

The ROW function, when used as an array function, will return a sequence of the row numbers in the reference. So to generate the array 1,2,3,4,5 you could use

ROW(1:5)

To generate a sequence of n ones just divide that array by itself:

ROW(1:n)/ROW(1:n)

To make n a variable, just use the INDIRECT function and a cell reference:

ROW(INDIRECT("1:"&C1))/ROW(INDIRECT("1:"&C1))

So your formula would be:

=TTEST(A1:A5, ROW(A1:A5)/ROW(A1:A5),2,1)

Or, more simply for your case, just divide the range by itself:

=TTEST(A1:A5, (A1:A5)/(A1:A5), 2, 1)

answered Jan 12, 2013 at 5:01

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kiran Over a year ago

Thank you so much, your answer consists of so many little gems which I thought never existed in Excel. Even though I will not be using 1 all the time, but variable n, I got the idea how I can do it. Thanks a lot for your insight and help

Collectives™ on Stack Overflow

Using Arrays in the Excel Formula

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related