Lookup function experts.. I need ascending and decending lookups of a series of data.. can you help me sort this out

I have a list of values 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 to extract two values. One from the top and one from below the maximum value ( 1.75) which are lesser than 1.6.



The reason I need this formula is: I work with spectra files. Spectra traces have a "Peak" value.. I need to find values at 1/2 height peak values. I also need the location of those two values in order to return adjacent values in column A.



thanks very much for any help.



BillReese

Hi!

What does this mean?

>One from the top and one from below the maximum value ( 1.75) which are lesser than 1.6

Based on your sample data what results are you looking for?

Biff
"BillReese" <[email protected]> wrote in message news:__R5g.11676$Un3.1421@trnddc05...
I have a list of values 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 to extract two values. One from the top and one from below the maximum value ( 1.75) which are lesser than 1.6.



The reason I need this formula is: I work with spectra files. Spectra traces have a "Peak" value.. I need to find values at 1/2 height peak values. I also need the location of those two values in order to return adjacent values in column A.



thanks very much for any help.



BillReese

I said it already, you must not have read my note completly.

I am looking for "2" VALUES

Value #1 ( the value above "peak" value )
I am looking for highest value < 1.6

Value #2 (the value below "peak" value)
I am looking for highest value < 1.6

I don't know how to say it more simple than that.

====================================================================
"Biff" <[email protected]> wrote in message news:[email protected]...
Hi!

What does this mean?

>One from the top and one from below the maximum value ( 1.75) which are lesser than 1.6

Based on your sample data what results are you looking for?

Biff
"BillReese" <[email protected]> wrote in message news:__R5g.11676$Un3.1421@trnddc05...
I have a list of values 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 to extract two values. One from the top and one from below the maximum value ( 1.75) which are lesser than 1.6.



The reason I need this formula is: I work with spectra files. Spectra traces have a "Peak" value.. I need to find values at 1/2 height peak values. I also need the location of those two values in order to return adjacent values in column A.



thanks very much for any help.



BillReese

Oh, I think I get it now!

So:

Value #1 = 1.57
Value #2 = 1.58

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))<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)):B15<C1,INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)):B15))

Biff
"BillReese" <[email protected]> wrote in message news:IOT5g.137$yh.47@trnddc04...
I said it already, you must not have read my note completly.

I am looking for "2" VALUES

Value #1 ( the value above "peak" value )
I am looking for highest value < 1.6

Value #2 (the value below "peak" value)
I am looking for highest value < 1.6

I don't know how to say it more simple than that.

====================================================================
"Biff" <[email protected]> wrote in message news:[email protected]...
Hi!

What does this mean?

>One from the top and one from below the maximum value ( 1.75) which are lesser than 1.6

Based on your sample data what results are you looking for?

Biff
"BillReese" <[email protected]> wrote in message news:__R5g.11676$Un3.1421@trnddc05...
I have a list of values 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 to extract two values. One from the top and one from below the maximum value ( 1.75) which are lesser than 1.6.



The reason I need this formula is: I work with spectra files. Spectra traces have a "Peak" value.. I need to find values at 1/2 height peak values. I also need the location of those two values in order to return adjacent values in column A.



thanks very much for any help.



BillReese

P.S.

In the formulas, C1 holds the criteria: 1.6

Biff
"Biff" <[email protected]> wrote in message news:[email protected]...
Oh, I think I get it now!

So:

Value #1 = 1.57
Value #2 = 1.58

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))<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)):B15<C1,INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)):B15))

Biff
"BillReese" <[email protected]> wrote in message news:IOT5g.137$yh.47@trnddc04...
I said it already, you must not have read my note completly.

I am looking for "2" VALUES

Value #1 ( the value above "peak" value )
I am looking for highest value < 1.6

Value #2 (the value below "peak" value)
I am looking for highest value < 1.6

I don't know how to say it more simple than that.

====================================================================
"Biff" <[email protected]> wrote in message news:[email protected]...
Hi!

What does this mean?

>One from the top and one from below the maximum value ( 1.75) which are lesser than 1.6

Based on your sample data what results are you looking for?

Biff
"BillReese" <[email protected]> wrote in message news:__R5g.11676$Un3.1421@trnddc05...
I have a list of values 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 to extract two values. One from the top and one from below the maximum value ( 1.75) which are lesser than 1.6.



The reason I need this formula is: I work with spectra files. Spectra traces have a "Peak" value.. I need to find values at 1/2 height peak values. I also need the location of those two values in order to return adjacent values in column A.



thanks very much for any help.



BillReese

Thank you, I figued out I needed to put 1.6 into cell C1 right away.

But when I paste both formulas into cells D1 and D2 and enter the formulas as "array formulas" I get " #N/A " reported in both D1 and D2.

Perhaps it's because your outside "IF" statement has no explicit failure condition. I am not great with array formulas, and I am finding it a little tough to debug your problem, I can basically see what you are trying to do, and I don't know what you got wrong yet..

Thanks,
BillReese

"Biff" <[email protected]> wrote in message news:%[email protected]...
P.S.

In the formulas, C1 holds the criteria: 1.6

Biff
"Biff" <[email protected]> wrote in message news:[email protected]...
Oh, I think I get it now!

So:

Value #1 = 1.57
Value #2 = 1.58

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))<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)):B15<C1,INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)):B15))

Biff
"BillReese" <[email protected]> wrote in message news:IOT5g.137$yh.47@trnddc04...
I said it already, you must not have read my note completly.

I am looking for "2" VALUES

Value #1 ( the value above "peak" value )
I am looking for highest value < 1.6

Value #2 (the value below "peak" value)
I am looking for highest value < 1.6

I don't know how to say it more simple than that.

====================================================================
"Biff" <[email protected]> wrote in message news:[email protected]...
Hi!

What does this mean?

>One from the top and one from below the maximum value ( 1.75) which are lesser than 1.6

Based on your sample data what results are you looking for?

Biff
"BillReese" <[email protected]> wrote in message news:__R5g.11676$Un3.1421@trnddc05...
I have a list of values 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 to extract two values. One from the top and one from below the maximum value ( 1.75) which are lesser than 1.6.



The reason I need this formula is: I work with spectra files. Spectra traces have a "Peak" value.. I need to find values at 1/2 height peak values. I also need the location of those two values in order to return adjacent values in column A.



thanks very much for any help.



BillReese

I'm sorry... Just found out when I pasted my data from column B into a fresh Excel worksheet.. Those numbers came back formatted as TEXT... After I found that out.. then I manually typed them in as numbers... everything now works as you said it would.

Thanks
BR

"BillReese" <[email protected]> wrote in message news:E2V5g.1645$g01.403@trnddc01...
Thank you, I figued out I needed to put 1.6 into cell C1 right away.

But when I paste both formulas into cells D1 and D2 and enter the formulas as "array formulas" I get " #N/A " reported in both D1 and D2.

Perhaps it's because your outside "IF" statement has no explicit failure condition. I am not great with array formulas, and I am finding it a little tough to debug your problem, I can basically see what you are trying to do, and I don't know what you got wrong yet..

Thanks,
BillReese

"Biff" <[email protected]> wrote in message news:%[email protected]...
P.S.

In the formulas, C1 holds the criteria: 1.6

Biff
"Biff" <[email protected]> wrote in message news:[email protected]...
Oh, I think I get it now!

So:

Value #1 = 1.57
Value #2 = 1.58

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))<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)):B15<C1,INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)):B15))

Biff
"BillReese" <[email protected]> wrote in message news:IOT5g.137$yh.47@trnddc04...
I said it already, you must not have read my note completly.

I am looking for "2" VALUES

Value #1 ( the value above "peak" value )
I am looking for highest value < 1.6

Value #2 (the value below "peak" value)
I am looking for highest value < 1.6

I don't know how to say it more simple than that.

====================================================================
"Biff" <[email protected]> wrote in message news:[email protected]...
Hi!

What does this mean?

>One from the top and one from below the maximum value ( 1.75) which are lesser than 1.6

Based on your sample data what results are you looking for?

Biff
"BillReese" <[email protected]> wrote in message news:__R5g.11676$Un3.1421@trnddc05...
I have a list of values 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 to extract two values. One from the top and one from below the maximum value ( 1.75) which are lesser than 1.6.



The reason I need this formula is: I work with spectra files. Spectra traces have a "Peak" value.. I need to find values at 1/2 height peak values. I also need the location of those two values in order to return adjacent values in column A.



thanks very much for any help.



BillReese