% Difference between cells based on variable...

[TheMilkGuy]

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

 

[Ron Rosenfeld]

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))

 

[Ron Rosenfeld]

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

And here's another formula looking at only the values on either side; also volatile but shorter:

=FORECAST(Variable,OFFSET(INDEX(ColA,MATCH(
Variable,ColA)),0,1,2,1),OFFSET(INDEX(ColA,MATCH(
Variable,ColA)),0,0,2,1))

 

[TheMilkGuy]

Hi Ron,

First of all, I appreciate the quick reply.

The second formula you sent works *perfectly* and I am more than pleased with the result. Saves me about a dozen nested IF's.

Thank you very much.

Cheers,
Craig

 

[Ron Rosenfeld]

Hi Ron,

First of all, I appreciate the quick reply.

The second formula you sent works *perfectly* and I am more than pleased with the result. Saves me about a dozen nested IF's.

Thank you very much.

Cheers,
Craig

Glad to help. Thanks for the feedback.
Actually, both the 2nd (using INDIRECT) and 3rd (using OFFSET) formulas should provide the same results (the 3rd formula is in my second post) and the 3rd might be easier to understand and maintain.

 

[TheMilkGuy]

I changed to the 3rd formula and you are right, it is much easier to understand.

Thanks again

 

[TheMilkGuy]

Hi again Ron,

I am sorry to resurrect a topic, but I'm in need of a bit more assistance. I keep getting "Circular Reference" warnings associated with the formula you so kindly provided me. In most cases I can make them go away (example: A1 value of 1000, A4 value of 7000 - I change A1 to 999 and A4 to 7001) however, some I just cannot get rid of.

I suppose not fully understanding the formula is my problem (repeat: it ISmy problem) but I was wondering if you could a)dumb-it-down a bit for my understanding or b) perhaps edit it in some way to circumvent this issue?

Again, many thanks for the help.
Craig