On Mon, 8 Oct 2012 18:50:49 -0700 (PDT), TheMilkGuy wrote:

Perhaps I'm not even phrasing the question correctly. I am arguably loathsome at math.

Given two ranges:

A B
1000 500
2000 1000
3000 1500

If I have a variable that gives me 1750 and I want to reference that to column A... How can I get the difference calculated from column B?

Example: 1750 finds the part of the A1:A3 range in which it fits (A1:A2), then applies its own difference between the two (75%) to the B1:B2 for an answer of 875.

Of course, IRL column A is not comprised of even thousands.

I hope that is clear enough. Thank you for your time.

Cheers

There are two ways to approach the problem. Not exactly what you requested, but sometimes what people with this sort of question really want, is the equivalent of a graphic solution. In other words, if the values are plotted on a two dimensional graph, and given a new x, what is the corresponding y value, and also assuming that the line representing that data is a straight line (although other shapes are possible with different equations), then the solution could be given by the FORECAST function:

=FORECAST(Variable,ColB,ColA)

On the other hand, if you are interested ONLY where Variable fits with regard to the values on either side, ignoring the remaining values, then you could still use the FORECAST function, but the computation of the x's and y's to use becomes more complex:

=FORECAST(Variable,INDIRECT(ADDRESS(MATCH(Variable ,ColA),2)
&":"&ADDRESS(MATCH(Variable,ColA)+1,2)),
INDIRECT(ADDRESS(MATCH(Variable,ColA),1)&":"
&ADDRESS(MATCH(Variable,ColA)+1,1)))

But, since INDIRECT is a volatile function, it becomes more time-consuiming.

To do the same thing without using Volatile functions, you could use this:

=(Variable-INDEX(ColA,MATCH(Variable,ColA)))/(INDEX(
ColA,MATCH(Variable,ColA)+1)-INDEX(ColA,MATCH(
Variable,ColA)))*((INDEX(ColB,MATCH(
Variable,ColA)+1))-(INDEX(ColB,MATCH(
Variable,ColA))))+INDEX(ColB,MATCH(Variable,ColA))