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?

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

TECHNOID wrote:
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?

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

"Gary''s Student" wrote:

FIrst thing is to reverse your columns, so that speed in in column A. As you
point out you can get the max speed with the MAX() function. Then you can
use VLOOKUP() to find the associated time. If speed is now in A1:A100 and
time is now in B1:B100 then use:

=VLOOKUP(MAX(A1:A100),A1:B100,2)
--
Gary's Student


"TECHNOID" wrote:

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?

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

"Aladin Akyurek" wrote:

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

TECHNOID wrote:
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 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.

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


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=476134

[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
on range objects, while [2] and [3] must process calculated arrays.

Krishnakumar wrote:
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

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.

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

"Aladin Akyurek" wrote:


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

TECHNOID wrote:

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?


--

[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.