Does anyone have any suggestions on how to determine the value?
There are two lists of number under column A and B.
Example 1:
I would like to determine the closest and minimum numbers between A and B

A B
173 173
160 162
156 156
150 152
It should return 156 in cell C1

Example 2:
I would like to determine the closest and maximum numbers between A and B

A B
144 144
133 138
130 134
126 126
It should return 144 in cell C1

Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

These array formulas** work based on your limited samples...but...

In your samples column B is always = column A. Is this *always* the case?

Array entered** :

For the MIN:

=MIN(IF(B1:B4-A1:A4=MIN(B1:B4-A1:A4),B1:B4))

For the MAX:

=MAX(IF(B1:B4-A1:A4=MIN(B1:B4-A1:A4),B1:B4))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to determine the value?
There are two lists of number under column A and B.
Example 1:
I would like to determine the closest and minimum numbers between A and B

A B
173 173
160 162
156 156
150 152
It should return 156 in cell C1

Example 2:
I would like to determine the closest and maximum numbers between A and B

A B
144 144
133 138
130 134
126 126
It should return 144 in cell C1

Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

Thank you very much for suggestions
For this case, the formula does not work
=MAX(IF(B1:B4-A1:A4=MIN(B1:B4-A1:A4),B1:B4))

A B
54 48
48 42
37 38
34 35
48 should be returned in cell C1
Does anyone have any suggestions?
Thank everyone very much for any suggestion

"T. Valko" wrote:

These array formulas** work based on your limited samples...but...

In your samples column B is always = column A. Is this *always* the case?

Array entered** :

For the MIN:

=MIN(IF(B1:B4-A1:A4=MIN(B1:B4-A1:A4),B1:B4))

For the MAX:

=MAX(IF(B1:B4-A1:A4=MIN(B1:B4-A1:A4),B1:B4))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to determine the value?
There are two lists of number under column A and B.
Example 1:
I would like to determine the closest and minimum numbers between A and B

A B
173 173
160 162
156 156
150 152
It should return 156 in cell C1

Example 2:
I would like to determine the closest and maximum numbers between A and B

A B
144 144
133 138
130 134
126 126
It should return 144 in cell C1

Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

works fine on my Excel 2003

Thanks everyone very much for suggestions

What if I change A to B, and B to A
=MAX(IF(A1:A4-B1:B4=MIN(A1:A4-B1:B4),A1:A4))
A B
54 48
48 42
37 38
34 35

37 is returned in cell A1, but it should return 48.
Does anyone have suggestions?
Thanks everyone very much for any suggestions
Eric


"Jarek Kujawa" wrote:

works fine on my Excel 2003

yr formula gives required result
=MAX(IF(A1:A4-B1:B4=MIN(A1:A4-B1:B4),A1:A4)) should give 37


one way to achieve what you want is:
=LARGE(IF($A$1:$A$4-$B$1:$B$4=MAX($A$1:$A$4-$B$1:$B$4),$A$1:$A$4),1)
=LARGE(IF($A$1:$A$4-$B$1:$B$4=MAX($A$1:$A$4-$B$1:$B$4),$A$1:$A$4),2)
....
gives 54, 48...