How do I find value of "x" (col A) when I know "f(x)" (B)?

[TECHNOID]

Suppose I have two columns of data. Column A is the independent variable,
say TIME. Column B is the dependent variable, say SPEED. The data are
collected on a car that starts from rest, accelerates, decellerates, then
stops. Clearly, the car attained some maximum speed during the test. I can
find the value of the maximum speed by using the worksheet function =MAX(B),
but I want to know at what time (column A) the maximum occurred. Of course I
can do a manual (visual) search, but this is laborious and eye-straining for
reams of data. What single worksheet function (or more likely, nested
functions) can I use to return the time value?

 

[Aladin Akyurek]

=INDEX(A2:A100,MATCH(MAX(B2:B100),B2:B100,0))

 

[TECHNOID]

Thank you "Gary's Student," but it didn't work. I think that the
instructions for VLOOKUP have buried in them the admonition that the data
column you are searching has to be sorted in either ascending or descending
order. That is not true of a data column with a max value buried in the
middle of it somewhere. Of course, it would be possible just to do a brute
force sort of the two columns of data, but it seems that would be an
unnecessary and time-consuming operation. You would then have to manually
transcribe the value you discovered. Thanks anyway. TECHNOID

 

[TECHNOID]

Thank you "Aladin Akyurek," your formula seems to work! Regards, TECHNOID

 

[Harlan Grove]

Aladin Akyurek wrote...

=INDEX(A2:A100,MATCH(MAX(B2:B100),B2:B100,0))

....

Finds the topmost match. If there were multiple instances of the MAX
value, would any corresponding col A value be equally acceptable? If
so,

=LOOKUP(2,1/(B2:B100=MAX(B2:B100)),A2:A100)

would be a bit more efficient, and would return the bottommost match.

 

[Krishnakumar]

Hi,

This avoids the division, but don't know how much efficient it is,

=LOOKUP(2,SEARCH(MAX(B2:B100),B2:B100),A2:A100)

HTH

 

[Aladin Akyurek]

[1]

=INDEX(A2:A100,MATCH(MAX(B2:B100),B2:B100,0))

appears a tad faster than

[2]

=LOOKUP(2,1/(B2:B100=MAX(B2:B100)),A2:A100)

which is a tad faster than

[3]

=LOOKUP(2,SEARCH(MAX(B2:B100),B2:B100),A2:A100)

The difference can probably be attributed to the fact that [1] operates

 

[Aladin Akyurek]

If you want to also retrieve times corresponding to multiple instances
of the max speed (as Harlan noted), try my post in:

http://tinyurl.com/562xz

or construct a pivot table, which can be made show the Top 1 values.

Thank you "Aladin Akyurek," your formula seems to work! Regards, TECHNOID

:

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.