Thanks to Biff, I found 1/2 of what I need. I thought I could figure out how to plug in the Offset formula to his solution to give me what I want so far, but no luck. So I need another formula to do the following:
I have is a list like this:
A B
1 1.10
2 1.20
3 1.31
4 1.40
5 1.49
6 1.57
7 1.65
8 1.70
9 1.74
10 1.75
11 1.73
12 1.66
13 1.58
14 1.49
15 1.41
I want quot;2quot; VALUES
Value #1 ( Using the value in Column B located above quot;peakquot; value 1.74 )
I want value in Column A adjacent to the highest value lt; 1.6 (which is 1.57)
I want this returned ( #6 ) which is adjacent to 1.57
Value #2 ( Using the value in Column B located below quot;peakquot; value 1.74 )
I want value in Column A adjacent to the highest value lt; 1.6 (which is 1.58)
I want this returned ( #13 ) which is adjacent to 1.58
================================================== ============
These are the formulas Biff gave me to extract values:
For V1:
Entered as an array using the key combination of CTRL,SHIFT,ENTER:
=MAX(IF(B1:INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0 ))lt;C1,B1:INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)) ))
For V2: (array entered)
=MAX(IF(INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)): B15lt;C1,INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)):B 15))
PS. I don't want the hash mark in the format.. I just want the numbers 6 and 13.
Thanks,
BillReese
Assume:
F1 = V1 = 1.57
F2 = V2 = 1.58
For V1 (array entered):
=INDEX(A1:A15,MATCH(F1,B1:INDEX(B1:B15,MATCH(MAX(B 1:B15),B1:B15,0)),0))
For V2 (array entered):
=INDEX(A15:INDEX(A1:A15,MATCH(MAX(B1:B15),B1:B15,0 )),MATCH(F2,INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15, 0)):B15,0))
Biff
quot;BillReesequot; gt; wrote in message news:2N06g.14257$O_6.12998@trnddc08...
Thanks to Biff, I found 1/2 of what I need. I thought I could figure out how to plug in the Offset formula to his solution to give me what I want so far, but no luck. So I need another formula to do the following:
I have is a list like this:
A B
1 1.10
2 1.20
3 1.31
4 1.40
5 1.49
6 1.57
7 1.65
8 1.70
9 1.74
10 1.75
11 1.73
12 1.66
13 1.58
14 1.49
15 1.41
I want quot;2quot; VALUES
Value #1 ( Using the value in Column B located above quot;peakquot; value 1.74 )
I want value in Column A adjacent to the highest value lt; 1.6 (which is 1.57)
I want this returned ( #6 ) which is adjacent to 1.57
Value #2 ( Using the value in Column B located below quot;peakquot; value 1.74 )
I want value in Column A adjacent to the highest value lt; 1.6 (which is 1.58)
I want this returned ( #13 ) which is adjacent to 1.58
================================================== ============
These are the formulas Biff gave me to extract values:
For V1:
Entered as an array using the key combination of CTRL,SHIFT,ENTER:
=MAX(IF(B1:INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0 ))lt;C1,B1:INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)) ))
For V2: (array entered)
=MAX(IF(INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)): B15lt;C1,INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)):B 15))
PS. I don't want the hash mark in the format.. I just want the numbers 6 and 13.
Thanks,
BillReese
Hi Biff,
I just wanted to thank you, the answer you gave me was just what I needed to get a much more complicated code to work. You gave me all the clues I needed.
thanks very much, I don't believe I was capable without this help !!!
regards,
BRquot;Biffquot; gt; wrote in message ...
Assume:
F1 = V1 = 1.57
F2 = V2 = 1.58
For V1 (array entered):
=INDEX(A1:A15,MATCH(F1,B1:INDEX(B1:B15,MATCH(MAX(B 1:B15),B1:B15,0)),0))
For V2 (array entered):
=INDEX(A15:INDEX(A1:A15,MATCH(MAX(B1:B15),B1:B15,0 )),MATCH(F2,INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15, 0)):B15,0))
Biff
quot;BillReesequot; gt; wrote in message news:2N06g.14257$O_6.12998@trnddc08...
Thanks to Biff, I found 1/2 of what I need. I thought I could figure out how to plug in the Offset formula to his solution to give me what I want so far, but no luck. So I need another formula to do the following:
I have is a list like this:
A B
1 1.10
2 1.20
3 1.31
4 1.40
5 1.49
6 1.57
7 1.65
8 1.70
9 1.74
10 1.75
11 1.73
12 1.66
13 1.58
14 1.49
15 1.41
I want quot;2quot; VALUES
Value #1 ( Using the value in Column B located above quot;peakquot; value 1.74 )
I want value in Column A adjacent to the highest value lt; 1.6 (which is 1.57)
I want this returned ( #6 ) which is adjacent to 1.57
Value #2 ( Using the value in Column B located below quot;peakquot; value 1.74 )
I want value in Column A adjacent to the highest value lt; 1.6 (which is 1.58)
I want this returned ( #13 ) which is adjacent to 1.58
================================================== ============
These are the formulas Biff gave me to extract values:
For V1:
Entered as an array using the key combination of CTRL,SHIFT,ENTER:
=MAX(IF(B1:INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0 ))lt;C1,B1:INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)) ))
For V2: (array entered)
=MAX(IF(INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)): B15lt;C1,INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)):B 15))
PS. I don't want the hash mark in the format.. I just want the numbers 6 and 13.
Thanks,
BillReese
留言列表
