Find a value in a limit where its difference from an array of values is maximum

I have a pre-defined limit, lets say, lower limit is 63 and upper limit is 68.

I have an array of values, lets say

65.25
67.32
68.34
64.5
62.5
64.25
67.5

I want to use an excel function or a combination of functions to find out a value in the range 63 to 68 where its difference from the next lower value in the above mentioned array list is maximum. For this example, I would like to have the value as 67.32 since its difference with the next lower value (65.25) is 67.32 - 65.25 = 1.93 (maximum for the range 63 to 68).

What excel function should I use? Please help me.

I am attaching a sample file along.

Thanks for the help but I am looking for a more general answer as my array is much larger and can be dynamic as well. If you can help me in that, it would be great.

Originally Posted by tigeravatar

amahindroo,

Using your sample file, give this formula a try:

=INDEX(LARGE(INDEX((D3:D9>=C14)*(D3:D9<=D14)*D3:D9,),ROW(INDIRECT("1:"&COUNTIFS(D3:D9,">="&C14,D3:D9,"<="&D14)-1))),MATCH(MAX(INDEX(LARGE(INDEX((D3:D9>=C14)*(D3:D9<=D14)*D3:D9,),ROW(INDIRECT("1:"&COUNTIFS(D3:D9,">="&C14,D3:D9,"<="&D14)-1)))-LARGE(INDEX((D3:D9>=C14)*(D3:D9<=D14)*D3:D9,),ROW(INDIRECT("2:"&COUNTIFS(D3:D9,">="&C14,D3:D9,"<="&D14)))),)),INDEX(LARGE(INDEX((D3:D9>=C14)*(D3:D9<=D14)*D3:D9,),ROW(INDIRECT("1:"&COUNTIFS(D3:D9,">="&C14,D3:D9,"<="&D14)-1)))-LARGE(INDEX((D3:D9>=C14)*(D3:D9<=D14)*D3:D9,),ROW(INDIRECT("2:"&COUNTIFS(D3:D9,">="&C14,D3:D9,"<="&D14)))),),0))

tigeravtar,

the formula you have given is working just well for the range 63 to 68 but I have a small twist to that.

Lets say the array values are:-

65.25
67.32
68.5
64.5
60.5
64.25
67.5

and the range is the same, 63 to 68. Now if you see, using the formula you gave, it gives the result as 67.32 since the difference there is 67.32 - 65.25 = 2.08. But if you see, there is a value 60.5 which has a difference of 2.50 from the lower limit 63 i.e. 63 - 60.5 = 2.50. So, this is the value I would like to extract.

I hope you got my point. I am attaching the new sample file again for your reference.

Originally Posted by amahindroo

tigeravtar,

the formula you have given is working just well for the range 63 to 68 but I have a small twist to that.

Lets say the array values are:-

65.25
67.32
68.5
64.5
60.5
64.25
67.5

and the range is the same, 63 to 68. Now if you see, using the formula you gave, it gives the result as 67.32 since the difference there is 67.32 - 65.25 = 2.08. But if you see, there is a value 60.5 which has a difference of 2.50 from the lower limit 63 i.e. 63 - 60.5 = 2.50. So, this is the value I would like to extract.

I hope you got my point. I am attaching the new sample file again for your reference.

It can even be 64.5, which is one of the values in the array.

Originally Posted by tigeravatar

amahindroo,

The only way I can think of to accomplish that is with a UDF (using VBA).
Attached is a modified version of your most recent sample file.
It contains the following UDF:

Please Login or Register  to view this content.

How to use a User Defined Function (UDF):

1. Make a copy of the workbook the macro will be run on
   • Always run new code on a workbook copy, just in case the code doesn't run smoothly
   • This is especially true of any code that deletes anything
2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
3. Insert | Module
4. Copy the provided code and paste into the module
5. Close the Visual Basic Editor

Then in cell E2 is this formula:

Please Login or Register  to view this content.

tigeravatar,

The function is still not working well. I know a bit of vb and am trying to debug, but still if you could work out and correct it, it would be great.

Thanks for the help anyways.