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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Can I ask why you don't just use formulas to reference those cells, in a way that makes the values adjacent?
 

ambera68

New Member
Joined
Dec 9, 2010
Messages
2
Thank you, Domenic! I searched the web 1/2 of yesterday, unable to find this answer. :)
 

Jeffrey C

New Member
Joined
Jan 29, 2015
Messages
1

ADVERTISEMENT

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

New Member
Joined
Nov 4, 2016
Messages
20
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

New Member
Joined
Nov 4, 2016
Messages
20

ADVERTISEMENT

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

New Member
Joined
Jun 19, 2017
Messages
1
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

New Member
Joined
Aug 31, 2018
Messages
1
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,617
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,747
Members
418,149
Latest member
amamiche67

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
Top