formula to define relationship between two sets

[Greg Lovern]

I've been given a table with two columns and two rows:

ColumnA ColumnB
5 3
100 1

Each number in column B is a percentage of the corresponding number in
column A, but the percentage changes depending on the amount in column
A.

I need a formula that, for any column A value between 5 and 100, will
give me the column B percentage.

The relationship between the two columns is linear, so that if you
graphed all possible values, you'd get a straight line.

Thanks for any suggestions.


Greg

 

[Guest]

Y = MX + B

3 = M *5 + B
1 = M *100 + B

Solve for M and B.

In B1 enter = (your value for M) * A1 + (Your value for B)

 

[Greg Lovern]

Thanks, but where can I find a refresher on how to solve for M and B?
I haven't done that since high school, long ago.

Is it something like this?

(3 + 1) = ((M * 5) + B) + ((M * 100) + B)

Once I know how to combine the two equations, I think I can take it
from there.


Thanks,

Greg

 

[Dana DeLouis]

Another option:

=FORECAST(52.5,B1:B2,A1:A2)

Returns 2

Since 52.5 is between 5 & 100, 2 is between 1 & 3.

See also the functions Slope, and Intercept.

 

[Greg Lovern]

Thanks, FORECAST works great.

I looked at Slope and Intercept, but I don't see how they would help;
they average the two columns to get an average relationship. That
average doesn't help as far as I can tell.

I also got a refresher on how to solve two equations with two
variables: Combine the two equations in any way that eliminates one of
the variables, then solve for the other, then use that result in
either of the two original equations to solve for the first variable:

(3 - 1) = ((M * 5) + B) - ((M * 100) + B)

2 = (M * 5) - (M * 100)

2 = -(M * 95)

-2/95 = M

M = -0.0210526315789474

Then, use -2/95 (or -0.0210526315789474) in either of the original
equations to get B:

1 = (-2/95) * 100 + B

1 - B = (-2/95) * 100

-B = ((-2/95) * 100) - 1

B = -(((-2/95) * 100) - 1)

B = -((-200/95) - 1)

B = (200/95) + 1

B = 3.105263158


That would be interesting to work out in formulas that allow the user
to change any of the four original constants, but FORECAST is sure a
lot easier.


Greg

 

[Charles Williams]

assuming A1 contains the 5, A2 contains the 100, and B1:B2 contains the 3
and the 1
then if A5 contains the A value to solve for, this formula gives you the
answer

=$B$1+($B$1-$B$2)/($A$1-$A$2)*(A5-$A$1)

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

 

[Dana DeLouis]

I looked at Slope and Intercept, but I don't see how they would help;

M = -2/95
M = -0.0210526315789474

B = (200/95) + 1
B = 3.105263158

Hi.

=SLOPE(B1:B2,A1:A2)
-0.021052632

=INTERCEPT(B1:B2,A1:A2)
3.105263158

 

[Greg Lovern]

Thanks Charles,

That's a big improvement over solving for two variables in two
equations.

But, since FORECAST is even easier and does the job, and doesn't
require the Analysis Toolpak, I'll be lazy and just use FORECAST.


Thanks,

Greg

 

[Greg Lovern]

Hi Dana,

Doh! Now I get it. Thanks.


Greg

Hi.

=SLOPE(B1:B2,A1:A2)
-0.021052632

=INTERCEPT(B1:B2,A1:A2)
3.105263158

--
HTH :>)
Dana DeLouis
Windows XP & Office 2007






















- Show quoted text -