Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
How do I find value of "x" (col A) when I know "f(x)" (B)?
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? |
#2
|
|||
|
|||
=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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
How do I find value of "x" (col A) when I know "f(x)" (B)?
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
How do I find value of "x" (col A) when I know "f(x)" (B)?
[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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find last occurance of text in range | Excel Worksheet Functions | |||
Find within Workbook. | Excel Discussion (Misc queries) | |||
How do I find the cell address of the 2nd largest of a set? | Excel Discussion (Misc queries) | |||
Using the Find tool in EXCEL | Excel Worksheet Functions | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) |