Extracting First Instance from Index (Match) function

[NMacs]

Hello,

I am trying to write an equation that will allow me to pull out the value that corresponds to the closest instance that my criteria becomes true. To provide an example: I have two columns of values. Column A represents acceleration; A = {0.2, 0.5, 0.8, 0.2, 0.5, 1.0, 0.9, 0.6, 1.3, 1.7, 2.8, 1.0, 0.5} and Column B represents location; B = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13}. I want to extract the value that represents 60% of maximum acceleration.

I have been trying the following equation : Index(A1:A13,Match(Max((A1:A13))*0.6,A1:A13,1)).

The issue I am facing is that within my true larger data set, the 60% of max acceleration occurs repeats several times (as does this example with 1.0) and the above equation will extract the final occurrence of 60% acceleration when in reality I want the first occurrence. (Equation pulls acceleration = 1.0 at position 12 opposed to acceleration = 1.0 at position 6).

Any help on this would be greatly appreciated!

[Glenn Kennedy]

Next time, please post a sample file rather than strings in the post that are a pain to get into an Excel sheet!!!


=LOOKUP(0,0/FREQUENCY(0,ABS(A3:A15-0.6*MAX(A3:A15))),B3:B15)

[NMacs]

Hi Glenn,

Thanks for the help, unfortunately that example still does not seem to work. I have attached the excel sheet pertaining my data set. Within it I have highlighted the error.

For additional context - I am trying to determine the x location at which 40 and 60% max y occur. Within this data set it is pulling the closest value to 60% max y when I am looking for the first instance of the closest value. (I have highlighted the cell I am trying to pull).

Thanks again for the help,

Nick

[Glenn Kennedy]

No. You're wrong.

Max 1.472. 60% of same is 0.8832

I filtered the data to show a narrow range round thast value. Row 846 is closest. The formula in F2 gives the correct answer.

[NMacs]

It is the correct answer the way you approached it but it is not the answer I need. I need the value that is 60% of max Y prior to Max Y, yet still pulling from the entire array as I will be using this general setup for 60 data sets.

If you don't know a solution to this problem hopefully another individual can provide some insight.

[Glenn Kennedy]

Mea culpa. I misunderstood...


=LOOKUP(0,0/FREQUENCY(0,ABS(A4:INDEX(A:A,MATCH(I1,A:A,0))-0.6*MAX(A4:A1487))),B4:INDEX(B:B,MATCH(I1,A:A,0)))

[NMacs]

Thanks a lot for your help Glenn, much appreciated.

For future reference / simplicity, is there a way in excel that the length of an array in an equation can be described by another cell? I.e. if I have 1 cell that specifies the cell at which max y occurs (152 rows down from beginning), can the length of the array within the equation vary based on the cell containing "152"?

Nick

[Glenn Kennedy]

Yes... but I hate using INDIRECT. It's volatile and recalculates if/when anything changes.

=LOOKUP(0,0/FREQUENCY(0,ABS(INDIRECT("A4:A"&F5)-0.6*MAX(A4:A1487))),INDIRECT("B4:B"&F5))

[Fluff13]

Another option without indirect

Please Login or Register  to view this content.

[NMacs]

Good to know, I deciphered the same idea from the original equation you provided.

Thanks again,

nick