How to determine the value - 8 Dec?

[Eric]

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

 

[T. Valko]

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)

 

[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

 

[Jarek Kujawa]

works fine on my Excel 2003

 

[Eric]

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]

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

 

[Eric]

I try to re-word my statement
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 152
160 156
156 173
150 162
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 134
133 126
130 144
126 138
It should return 144 in cell C1

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

 

[T. Valko]

I would like to determine the closest and

maximum numbers between A and B
A B
144 134
133 126
130 144
126 138
It should return 144 in cell C1

The closest range is 133 126 and the max value of that range is 133.

Try these array formulas** :

For the MAX:

=MAX(INDEX(A2:B5,MATCH(TRUE,ABS(A2:A5-B2:B5)=MIN(ABS(A2:A5-B2:B5)),0),0))

For the MIN:

=MIN(INDEX(A2:B5,MATCH(TRUE,ABS(A2:A5-B2:B5)=MIN(ABS(A2:A5-B2:B5)),0),0))

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

 

[T. Valko]

Hmmm...

I just thought of something...

150...160
170...160

Both of those have the same range. The formulas I posted will find the
*first* instance of the closest range. If it's possible to have more than 1
instance of the same range let me/us know.

 

[Eric]

I try to re-word my description
In cell A1, there is 144. I would like to find the closest values within the
range in col B, then return the different 0 in cell C1
In cell A2, there is 133. I would like to find the closest values within the
range in col B, then return the different 1 in cell C2
In cell A3, there is 130. I would like to find the closest values within the
range in col B, then return the different 4 in cell C3
In cell A4, there is 126. I would like to find the closest values within the
range in col B, then return the different 0 in cell C4
After that, I would like to find the minimum value under column C and return
the maximum value 0 under column A 144.

Does anyone have any suggestions?
Thanks everyone very much for any suggestions
Eric

 

[T. Valko]

Sorry, I don't understand.

 

[Eric]

I try to re-word my description.
Let assume all numbers within the same column A
144, 133, 130, 126, 134, 126, 144, 138

I would like to determine the closest and maximum numbers by comparing one
by one.

In cell A1, there is 144. I would like to find the closest values within the
rest numbers, then return the different 0 in cell C1, because there is
another 144.
In cell A2, there is 133. I would like to find the closest values within the
rest numbers, then return the different 1 in cell C2 by comparing with the
number 134.
In cell A3, there is 130. I would like to find the closest values within the
rest numbers, then return the different 3 in cell C3 by comparing with the
number 133.
In cell A4, there is 126. I would like to find the closest values within the
rest numbers, then return the different 0 in cell C4, because there is
another 126.
In cell A5, there is 134. I would like to find the closest values within the
rest numbers, then return the different 1 in cell C5 by comparing with the
number 133.
In cell A6, there is 126. I would like to find the closest values within the
rest numbers, then return the different 0 in cell C6, because there is
another 126.
In cell A7, there is 144. I would like to find the closest values within the
rest numbers, then return the different 0 in cell C7, because there is
another 144.
In cell A8, there is 138. I would like to find the closest values within the
rest numbers, then return the different 4 in cell C8 by comparing with the
number 134.

After that, I would like to find the maximum value under column A by
matching the minimum value under column C and it would return 144,
because there are 4 numbers having 0 under column C, 144, 126, 126, 144.
The maximum value for this result is 144.

Does anyone have any suggestions?
Thanks everyone very much for any suggestions
Eric

 

[T. Valko]

Ok, I think we got it this time!

Enter this array formula** in B1 and copy down to B8:

=IF(COUNTIF(A$1:A$8,A1)>1,0,MIN(ABS(A1-IF(A$1:A$8<>A1,A$1:A$8,100000))))

Enter this array formula** in C1:

=MAX(IF(B1:B8=MIN(B1:B8),A1:A8))

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

 

[Eric]

Thanks everyone very much for suggestions
Eric

Ok, I think we got it this time!

Enter this array formula** in B1 and copy down to B8:

=IF(COUNTIF(A$1:A$8,A1)>1,0,MIN(ABS(A1-IF(A$1:A$8<>A1,A$1:A$8,100000))))

Enter this array formula** in C1:

=MAX(IF(B1:B8=MIN(B1:B8),A1:A8))

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

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks everyone very much for suggestions
Eric