I'm not sure I phrased this title correctly. Please feel welcome to offer suggested edits.

I have data formatted like so:


Ultimately, I would like to perform t-tests on the Quantity of each Token, comparing IN and OUT. Is there a way to do this without filtering for each, and performing a t-test?

The t-test part isn't relevant, per se, I'm just looking to insert a range into a formula. The =TTEST function, for example, takes the following arguments: TTEST(array1,array2,tails,type). If I wanted rows 2 and 6 in Array1 (NN,IN), and row 5 in Array2, is there a formula I use within the TTEST formula, in lieu of selecting the cells?

  • cross posted in stackoverflow.com/questions/25858336/… -- please don't post the same question in more than one StackExchange site.
    – teylyn
    Sep 16 '14 at 0:15
  • I figured on here I would get more of an Excel function answer, whereas on SO it would be more VBA based.
    – Adam_G
    Sep 16 '14 at 3:09
  • 1
    Please edit your question. Don't assume that everybody knows what a t-test is. Explain what needs to be done with the data and what you want to see as a result. Post an Excel sample file to a file sharing service. Enter the expected results manually. Explain the logic.
    – teylyn
    Sep 16 '14 at 4:18
  • Agreed; we need more information. In addition to what's been mentioned, how many sets of data? How many duplicate IN/OUT for a token? Where should the information be stored? Sep 16 '14 at 9:06
  • Thanks all, for your comments. I've updated the question.
    – Adam_G
    Sep 16 '14 at 21:47

User Adam_G has found his own answer to this particular question through the use of a slew of various tutorials and Microsoft help files, but I'm providing here a working example of the method to index and retrieve data across multiple columns for anyone else who happens across the question.

Example array formula:
={INDEX('master data'!$A:$C,SMALL(IF('master data'!$A:$A=_____,ROW('master data'!$A:$A)),ROW(1:1)),_____)}

The blank at the end of the formula is to be replaced by the column number in the row of data to return, matching against the blank to be replaced near the middle of the formula as search criteria. The final row will update as pasted down, or can be changed to the numerical value of the row to return. The array formula establishes data to be searched as an array, and returns the data from the column specified in the row searched against. This method is very slow, and should not be utilized when there are other, more scalable methods of arranging/manipulating data sets.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.