Lookup function experts.. I need ascending and decending lookups of a series of data.. can you h

[BillReese]

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

 

[Biff]

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

 

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

====================================================================
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
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

 

[Biff]

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

====================================================================
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
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

 

[Biff]

P.S.

In the formulas, C1 holds the criteria: 1.6

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

====================================================================
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
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

 

[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

P.S.

In the formulas, C1 holds the criteria: 1.6

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

====================================================================
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
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

 

[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

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

P.S.

In the formulas, C1 holds the criteria: 1.6

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

====================================================================
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
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