minimum and the corresponding value

[dinesh kumar]

hi ,
can any help with VBA code for the below case IN EXCEL


A B C
X 3 8
Y 5 10
Z 10 20


I WANT THE MINIMUM OF C-B AND ALSO THE VALUE CORRESPONDING TO THE
MINIMUM
MY OUTPUT SHOULD LOOK LIKE
THE MINIMUM OF B-C IS 5 AND THE VALUES ARE X AND y

 

[Bernard Liengme]

Why 5, why not 3?
Do you want X and Y - X corresponds to 3, Y to 5 ???
Or do you want the B and C values?
best wishes

 

[Guest]

=IF(ISERR(SMALL(IF((rngC-rngB)=MIN(rngC-rngB),ROW(INDIRECT("1:"&ROWS(rngA)))),ROWS($1:1))),"",INDEX(rngA,SMALL(IF((rngC-rngB)=MIN(rngC-rngB),ROW(INDIRECT("1:"&ROWS(rngA)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

 

[Bernard Liengme]

I now see what you mean by 3! It is the minimum difference.
Here is the UDF that is called with =MINDIFF(A1:C3)

Function mindiff(myrange)
mycount = myrange.Count
mydiff = 1E+307
myrow = " values are "
For j = 1 To mycount / 3
mytest = myrange(j, 3) - myrange(j, 2)
If mytest <= mydiff Then
mydiff = mytest
If k > 0 Then
myrow = myrow & " and "
End If
myrow = myrow & myrange(j, 1)
k = 1 + 1
End If
Next j
mindiff = "The minimum difference is " & mydiff & myrow
End Function

 

[Bernard Liengme]

That is impressive!