I have a table of values in Excel (2103 if that makes a difference), which when plotted looks something like that:

I am trying to find for each series the index of the value, after which point all subsequent values lie within a certain range (between 99 and 101 in that case). Note that the values are not necessarily in ascending or descending order, but they will all eventually converge towards the same value (100 in that case).

I have done some searching and it feels like some combination of MATCH, IF and/or VLOOKUP should do it, but I can't figure it out. Can anybody help?

Thanks!

A couple of possible approaches:

OPTION 1

1) Sort your data based on the X value and sort in descending order. You want your largest X value to be your first point.

2) Determine the slope of the line between each subsequent point. In an adjacent column.

3) When the slope of the line is calculated but is greater than the max value for point 1 and min value for point two (or vice versa), you know your are beyond your convergence point.

OPTION 2

1) Sort your data based on the X value in descending order. same as option1

2) Assuming you have a way of determining the convergence value put that in a cell.

3) Find the first Y value in the list that exceeds the convergence value by + or - your tolerance.

FINDING FIRST OCCURRENCE

In order to find the first occurrence of something, you will want to use either an array formula, or a formula that performs array-like calculations. I will attempt to illustrate using the AGGREGATE function, but there are other methods. Since AGGREGATE performs array calculation for the options I am choosing, avoid using full column references within the AGGREGATE function or excessive calculations may be made that could slow down your system.

AGGGREGATE (Function #, Error/Hidden Options, Your formula, Parameter)

The above is the basic break down of the AGGREGATE function. What we are going to do is tell AGGREGATE to sort the results from our formula in ascending order, ignore any formula results that are errors, and then give us the first entry in the sorted list. Believe it or not, most of that is pretty straight forward. I'll leave that for now, and focus on the hard part...our formula.

What we are looking for is the first occurrence of something. We are either going to need the value of that something or its position in a list (or row number). Normally in this situation, the thing I am trying to find I usually put as the numerator in the formula. Any conditions that need to be met that give TRUE or FALSE results get placed in the denominator of the formula.

The trick here is that when TRUE or FALSE are placed through a math operation, they are converted to 1 for TRUE and 0 for FALSE. (As a total aside, when functions a like IF are looking for TRUE and FALSE in their decision making, 0 is treated as FALSE and EVERY OTHER NUMBER IS TRUE.) So any time your divide by FALSE, you will be dividing by 0, which causes an error for the calculation, which was previously stated that AGGREGATE would be set to ignore.

So with proper formula building you will get a sorted list of items that only match your conditions in the denominator. And since its a sorted list in Ascending order, when its set to return the 1st entry, you will get (in our case) the lowest row number, or least value.

Example: Lets assume your X values are in column B and your Y values are in column C and your data is in rows 2:100. We want to find the first Y value that is more than 2.4 away from 81.3

=AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1)

Now to return the value of that cell, you would embed the AGGREGATE formula in an INDEX formula.

=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1))

Notice that the full column reference of B:B is outside the AGGREGATE function, which is OK. IF you want to know what Y value this is, change the B:B to C:C.

Based on your criteria of being between 101 and 99, you could use:

=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1))



OR



=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1))

Those equations will give you the value of the first point outside your limits. So to get the last point from inside your limits, you need to subtract 1 from the row number which gives a new formula of:

=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1)-1)



OR



=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1)-1)

Note the * between the two condition checks in the denominator. The * acts like an AND function (only 1 * 1 = 1; both conditions must be true). A + would act like an OR function (either or both can be true for the result to be non-zero).