creating an array from non-adjacent cells

ambera68

New Member
Joined
Dec 9, 2010
Messages
2
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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Can I ask why you don't just use formulas to reference those cells, in a way that makes the values adjacent?
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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))))
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top