1

The basic answer to my question is provided here: How to look up a value in a list and return multiple corresponding values. However, once the formula is entered, I need to drag down, or copy/paste the formula, to get the rest of the corresponding values. To quote the article:

When you enter or fill this formula in subsequent cells, the formula returns the subsequent corresponding values

However, I have no way of knowing how many values to expect, and thus no way of knowing how far to copy/paste the formula. Copying and pasting it all the way down crashes Excel.

EDIT If my data looks like this:

Tag | Loc | Time
---|---|----
NN | IN | 7
CD | OUT | 4
VB | OUT | 12
NN | OUT | 4
NN | IN | 2
NN | OUT | 6
VB | OUT | 23
VB | OUT | 4
VB | IN | 6

I'd want to perform t-tests for each Tag, comparing the Times of IN vs OUT. Using the INDEX formula, I could, hypothetically, pull up all of the Times for, e.g. NN & OUT.

8
  • 1
    I'd have to ask for some kind of example data set, to see what you're indexing against. There is a very powerful formula that can be used as kind of a multiple-column VLOOKUP, but there may be an easier solution for you such as clever use of sorting and filtering in Excel, or a quick and easy VBA script. Sep 16 '14 at 23:04
  • Automating the accumulation of noncontiguous data into an array for the purpose of the TTEST is possible by creating a UDF (user-defined function). Unfortunately, that wanders way off the reservation. I've provided here an example of indexing and retrieving data (which actually functions much faster on Google Docs than it does in Excel), but it sounds like you've already accomplished that much. Sep 17 '14 at 1:02
  • While possible to do so, it seems like more of a headache than is necessary. You can simply retrieve the data from your master sheet with the appropriate indexing search string, sort as required, and perform the TTEST to flavor. Sep 17 '14 at 1:08
  • 1
    Adam, can you give me an example of the formula for a specific TTEST you're trying to run? I know you're doing IN vs OUT, but I also need the tail and the type variables to ensure that I can get the formula I have in mind to return accurate data. Sep 17 '14 at 1:32
  • 1
    I get a "page not found" error on the link that explains the question, making the question impossible to understand at this point or for anyone else to answer (or to understand the existing answers). Can you update the question with enough information so the link isn't necessary?
    – fixer1234
    Dec 19 '14 at 13:15
2

I think the issue here is that the suggestion given in that link for dealing with the results of formulas in rows beyond the number of expected returns is to simply wrap the formula in some sort of IFERROR clause.

However, this set-up is extremely inefficient, particularly if the dataset in question is a dynamic, potentially expanding one.

The point is that, if you have a set-up such as:

=IFERROR([some_large_array_formula],"")

which is intended to be copied down a sufficient number of rows so as to encapsulate all desired returns, then you will be faced with two choices.

Firstly, you can perform some calculation to determine precisely how many such returns you will have at any given time, and then drag this formula down that number of rows. Obviously this is not ideal, even less so if you have, as I said, a dynamically-changing dataset.

Secondly, we can copy the formulas down to an arbitrarily large number of rows such that we are guaranteed to cover all possible returns even should our dataset expand at some future time, and so need not worry ourselves about it again.

Obviously this second method is preferable in practice. The problem with the IFERROR construction (even worse if you're on 2003 or earlier and have to use a repeated IF(ISERROR clause) is that, in rows to which the formula is copied beyond that which is effectively necessary, there is nothing to prevent the large, resource-heavy array formula calculating needlessly.

The point is that, in the above construction, even in rows beyond that containing our last expected return Excel still has to spend all the resource on calculating the array formula part before it can then decide for itself whether it is in fact an error or not.

Far, far better than this idle IFERROR approach - which is sadly almost ubiquitously recommended for this set-up around the various sources on the internet - is, as James points out, to use a single "helper" cell to first determine the number of rows which we expect to have returns, and then reference this in the formula instead.

So, for example, if the posted data was in A1:C10 (with headers in row 1) and we put e.g. NN in E1 and OUT in F1, we would first enter a single, non-array formula in e.g. G1:

=SUMPRODUCT(0+(A2:A10=E1),0+(B2:B10=F1))

The **array formula**** in our first cell of choice would then be:

=IF(ROWS($1:1)>$G$1,"",INDEX($C$2:$C$10,SMALL(IF($A$2:$A$10=$E$1,IF($B$2:$B$10=$F$1,ROW($C$2:$C$10)-MIN(ROW($C$2:$C$10))+1)),ROWS($1:1))))

and copied down as required.

Granted, we still have the question of how far to copy this formula down. And even though we have a calculation in place to determine the required number of rows, we still don't want to have to manually re-adjust the number of cells containing formulas every time we want to update results. This should be a one-off initial job.

I certainly wouldn't recommend copying down to the very end of the spreadsheet. However, provided a suitably large upper bound can be chosen, then it should not matter a great deal in terms of performance if we end up with even several thousand extraneous cells containing formulas. The reason being, and the massive difference between this set-up and the "lazy" IFERROR approach, that here the initial clause:

=IF(ROWS($1:1)>$G$1,""

means that, in rows beyond the expected number of returns, the IF clause returns TRUE and so a blank is returned. And the nice part about the IF function is that, if the clause passed to it is TRUE, then the FALSE part - here a large, resource-heavy array formula - does not even get considered for calculation.

This is not at all the case with the IFERROR version, which carries on churning away, oblivious to the fact that its calculations are needless and a burden on resource.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

0
0

I would say format your data in a table (Home>Format as Table), or create a dynamic named range over your data. This addresses the problem of not knowing how much data you will have, as the range/table will grow as new data is added while you can still refer to the range/table by name.

tldr: format as table, if this is the first table in your workbook, the table will be give the name "Table1"

My suggestion would be to create a pivot table at this point (using the name of your table/range as the source) and put "Tag" into Row Labels, "Loc" into Column labels and "Time" into Values. You will get data that looks like this:

. IN OUT CD 4 NN 9 10 VB 6 39

You could then add a simple formula to the right and drag down to work out the difference between IN and OUT

2
  • I'm not following. Making a table didn't get me anywhere.
    – Adam_G
    Sep 17 '14 at 22:30
  • This solution uses a data table and a pivot table. As you stated that you don't know how much data your are going to have it's useful to put it into a data table, which will resize as you add new data. This means that when you create a pivot table you can refer to the table name as the input range, meaning that when you add new data it will be captured in the pivot table (on refresh). Please be more specific with your comments if this hasn't addressed your problem
    – CallumDA
    Sep 18 '14 at 7:56

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