Developing a code that selects a correlation based on a ranking system of data inputs?

I'm trying to program a macro that will select a single correlation based on where data falls on the correlation's data range. I'm looking to select the correlation whose range midpoint is closest to my data point.

For example, a few correlations are applicable for the following temperature data ranges:

Correlation 1 - from 74 to 240 degrees Fahrenheit

Correlation 2 - from 74 to 342 degrees Fahrenheit

Correlation 3 - from 85 to 245 degrees Fahrenheit

If I'm looking at a data point with 216 degrees F, the nearest applicable correlation would be Correlation 2, because the midpoint of its range is 208, which is closer than the other two.


Of course, I'm trying to do this for tens of correlations and so doing it individually would be tiresome, and I already have a code written that will weed out correlations whose ranges are beyond my data.

These correlations rely on a number of input parameters, and I would like to do this for all of the parameters. At the end, I would like to rank the correlations based on which correlation has the most parameters closest to the "midpoint" of their data ranges.

This is, however, a different beast to conquer.

For now, I'm just looking for help on how to rank the correlations by one parameter at a time.

Any suggestions?

Thanks so much!

Thank you so much for this!! This helps a ton.

I will be working with a very large spreadsheet full of data, however - I'm wondering if there's some way I can incorporate the "midpoint" of each correlation's data range combined with this Index function (or some VBA equivalent) into a macro so as to "clean up" the spreadsheet. Do you think this is possible?

Thanks again!