minimum and the corresponding value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dinesh kumar - 12 Sep 2007 20:11 GMT

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

Reply to this Message

Bernard Liengme - 12 Sep 2007 20:32 GMT

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

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> hi ,
> can any help with VBA code for the below case IN EXCEL
[quoted text clipped - 8 lines]
> MY OUTPUT SHOULD LOOK LIKE
> THE MINIMUM OF B-C IS 5 AND THE VALUES ARE X AND y

Reply to this Message

Teethless mama - 12 Sep 2007 20:38 GMT

=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

> hi ,
> can any help with VBA code for the below case IN EXCEL
[quoted text clipped - 8 lines]
> MY OUTPUT SHOULD LOOK LIKE
> THE MINIMUM OF B-C IS 5 AND THE VALUES ARE X AND y

Reply to this Message

Bernard Liengme - 12 Sep 2007 20:59 GMT

That is impressive!

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> =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))))
>
[quoted text clipped - 13 lines]
>> MY OUTPUT SHOULD LOOK LIKE
>> THE MINIMUM OF B-C IS 5 AND THE VALUES ARE X AND y

Reply to this Message

Bernard Liengme - 12 Sep 2007 20:53 GMT

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

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> hi ,
> can any help with VBA code for the below case IN EXCEL
[quoted text clipped - 8 lines]
> MY OUTPUT SHOULD LOOK LIKE
> THE MINIMUM OF B-C IS 5 AND THE VALUES ARE X AND y

Reply to this Message