minimum and the corresponding value
 

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

minimum and the corresponding value
 

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"dinesh kumar" wrote in message
ps.com...
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

minimum and the corresponding value
 

=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


"dinesh kumar" wrote:

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

minimum and the corresponding value
 

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 V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"dinesh kumar" wrote in message
ps.com...
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

minimum and the corresponding value
 

That is impressive!
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Teethless mama" wrote in message
...
=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


"dinesh kumar" wrote:

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