Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Minimum Value | Excel Worksheet Functions | |||
How to lookup the minimum, 2nd minimum and 3rd minimum......... | Excel Worksheet Functions | |||
Minimum but One?? | Excel Worksheet Functions | |||
Minimum Value | Excel Worksheet Functions | |||
second minimum value | Excel Discussion (Misc queries) |