creating an array from non-adjacent cells

[ambera68]

I need to use the ttest function. The format of the ttest command is: ttest (array1, array2, tails, type).

However, my data are not in adjacent cells, which is the way I'm used to using array data (e.g. A2:A10 would be array 1). Is there a way to select several non-adjacent cells as an array?

For example, suppose the first sample is in cells A15, A20, A23 and the second sample is in cells B18, B23, B26. I know the following is an incorrect format, but am looking for something like:

ttest(A15;A20;A23, B18;B23;B26, 2, 1)

Thanks in advance for any help!

 

[GlennUK]

Can I ask why you don't just use formulas to reference those cells, in a way that makes the values adjacent?

 

[Domenic]

Try...

=TTEST(CHOOSE({1;2;3},A15,A20,A23),CHOOSE({1;2;3},B18,B23,B26),2,1)

 

[ambera68]

Thank you, Domenic! I searched the web 1/2 of yesterday, unable to find this answer.

 

[Jeffrey C]

Building on the non-adjacent cells theme.

I am trying to create a Criteria array for DCOUNTA from non-adjacent cells. In this simplified example with a two row/single column array the standard method works but not my array created with CHOOSE. When I use F9 to view the formula results, it looks correct. What am I missing?

Standard Criteria definition method (this works):

=DCOUNTA(E12:F21,F4,F4:F5)​

Criteria definition utilizing CHOOSE (gives #VALUE! error):

=DCOUNTA(E12:F21,F4,CHOOSE({1;2},F4,F5))​

Eventually, I want to expand this out to a multi-column Criteria array.
Thanks

 

[GraemeMcRae]

This is a great tip! I used it to linearly smooth a column of numbers using LINEST. My dates are in column A, the unsmoothed numbers are in column M starting in row 14.

The number of rows of data will increase over time, so I made my formula independent of the actual number of rows. LINEST returns a horizontal array consisting of the slope and y-intercept, so I multiply that on the right by a vertical array consisting of a given date (in column A) and the number 1. The matrix multiplication results in a 1-by-1 matrix giving the value of the smoothed data for that date, i.e. slope*A14+intercept in the formula, below, for row 14. I extended this formula downward to fill all the rows.

=MMULT(LINEST(OFFSET(M$14,0,0,COUNT(M:M),1),OFFSET(A$14,0,0,COUNT(M:M),1),TRUE,FALSE),CHOOSE({1;2},A14,1))

I couldn't have done this without your excellent tip for making a matrix using CHOOSE! Thanks, Domenic!

 

[GraemeMcRae]

One more thing... since this data happens to be the annual output of my solar panels calculated at the end of each month, the best way to model the gradual decrease is using exponential decay. So I tweaked the formula a bit, as follows:

=EXP(MMULT(LINEST(LN(OFFSET(M$14,0,0,COUNT(M:M),1)),OFFSET(A$14,0,0,COUNT(M:M),1),TRUE,FALSE),CHOOSE({1;2},A14,1)))

Now the linear best-fit is on the logs of the numbers in column M.

 

[j2bryson]

I need to use the ttest function. The format of the ttest command is: ttest (array1, array2, tails, type).

However, my data are not in adjacent cells, which is the way I'm used to using array data (e.g. A2:A10 would be array 1). Is there a way to select several non-adjacent cells as an array?

I needed to go a bit further than what you did, this thread helped a lot so I thought I'd stick my findings here to help people in the future:

How to average the top four of eight columns that are not adjacent in excel.
=AVERAGE(LARGE(CHOOSE({1,2,3,4,5,6,7,8},(P3),(R3),(T3),(V3),(X3),(Z3),(AB3),(AD3)),ROW($1:$4)))
Notes:

• you need to push ctrl shift enter to evaluate such an expression (it's building a table on the fly) – that will wrap curly brackets {} around it.
• the ()s around a cell reference mean that if it's blank (say a student didn't do a quiz) it gets translated as 0 not #fail
• the dollar signs $ are necessary or you can't copy & paste this into other rows and get the relative stuff to all work.

 

[BrianKirk]

Try...

=TTEST(CHOOSE({1;2;3},A15,A20,A23),CHOOSE({1;2;3},B18,B23,B26),2,1)

Domenic, you are fantastic. I joined the forum just so I could let you know that your method let me finally do cross products in Excel.

If you have two vectors in A1:A3 and B1:B3, then

=MMULT(CHOOSE({1,2,3;4,5,6;7,8,9},0,-A3,A2,A3,0,-A1,-A2,A1,0),(B1:B3))

will give you the cross product. If you need a normalized cross product, then this will do the trick.

=MMULT(CHOOSE({1,2,3;4,5,6;7,8,9},0,-A3,A2,A3,0,-A1,-A2,A1,0),(B1:B3))/SQRT(SUMSQ(MMULT(CHOOSE({1,2,3;4,5,6;7,8,9},0,-A3,A2,A3,0,-A1,-A2,A1,0),(B1:B3))))

 

[Domenic]

Brian,

That's really nice of you to take the time to say so. I really appreciate it, and I'm glad you found the solution to be helpful.

Cheers!