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!
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,245
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
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
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,013
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!
 

Forum statistics

Threads
1,082,284
Messages
5,364,281
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top