What you want is a Top 1 (Max) list of time points, based on
measurements. What follows is a formula system that takes the ties of
the max measurement value into account...
Lets consider the following sample (smaller than yours for illustrative
purposes).
Let A1:B59 house the following sample:
{"Time","Value";
"time-1",100;
"time-2",125;
"time-3",110;
"time-4",140;
"time-5",140;
"time-6",120;
"time-7",110;
"time-8",110}
In C51 enter: Rank.
In C52 enter & copy down:
=RANK(B52,$B$52:$B$59)+COUNTIF($B$52:B52,B52)-1
In E48 enter:
=MAX(B52:B59)
In E49 enter: 1 (meaning Top N = 1)
In E50 enter:
=COUNTIF(B52:B59,LARGE(B52:B59,E49))-1
In E51 enter: Top List Time Points
In E52 enter & copy down:
=IF(ROW()-ROW(E$52)+1<=$E$49+$E$50,INDEX($A$52:$A$59,MATCH(R OW()-ROW(E$52)+1,$C$52:$C$59,0)),"")
The ROW(E$52) anchors the formula to the first cell the formula is
entered, which is E52.
The results list will show:
{"time-4";"time-5"}
given the sample under consideration.
Gwen Frishkoff Wrote:
Hello. I think this is a simple question, but the match, lookup, and
index functions that I've tried haven't worked. I have some experience
with XL, but I am not an expert user.
Let's say I have two columns, colA and colB. ColA is a list of time
markers; colB is a list of numerical values (measurements at each
timepoint). I want to:
1. find the max value in colB (between B52:B151).
2. find the timepoint that corresponds to the max value computed in 1.
The result that I want is the value computed in 2.
Any assistance would be greatly appreciated.
Gwen
--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread:
http://www.excelforum.com/showthread...hreadid=273889