formula to define relationship between two sets
 

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

formula to define relationship between two sets
 

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" wrote:

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

formula to define relationship between two sets
 

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


On Apr 15, 6:36 pm, Barb Reinhardt
wrote:
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" wrote:
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- Hide quoted text -

- Show quoted text -

formula to define relationship between two sets
 

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.

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


"Greg Lovern" wrote in message
ps.com...
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


On Apr 15, 6:36 pm, Barb Reinhardt
wrote:
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" wrote:
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- Hide quoted text -

- Show quoted text -

formula to define relationship between two sets
 

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



On Apr 15, 9:12 pm, "Dana DeLouis" wrote:
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.

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

"Greg Lovern" wrote in message

ps.com...



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

On Apr 15, 6:36 pm, Barb Reinhardt
wrote:
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" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

formula to define relationship between two sets
 

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

"Greg Lovern" wrote in message
ups.com...
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



On Apr 15, 9:12 pm, "Dana DeLouis" wrote:
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.

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

"Greg Lovern" wrote in message

ps.com...



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

On Apr 15, 6:36 pm, Barb Reinhardt
wrote:
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" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

formula to define relationship between two sets
 

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

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


"Greg Lovern" wrote in message
ups.com...
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



On Apr 15, 9:12 pm, "Dana DeLouis" wrote:
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.

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

"Greg Lovern" wrote in message

ps.com...



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

On Apr 15, 6:36 pm, Barb Reinhardt
wrote:
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" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

formula to define relationship between two sets
 

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


On Apr 16, 2:48 am, "Charles Williams"
wrote:
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 availablewww.DecisionModels.com

"Greg Lovern" wrote in message

ups.com...



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 getB:

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

On Apr 15, 9:12 pm, "Dana DeLouis" wrote:
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.

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

"Greg Lovern" wrote in message

oups.com...

Thanks, but where can I find a refresher on how to solve forMandB?
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

On Apr 15, 6:36 pm, Barb Reinhardt
wrote:
Y = MX +B

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

Solve forMandB.

In B1 enter = (your value forM) * A1 + (Your value forB)

"Greg Lovern" wrote:
I've been given a table with two columns and two rows:

ColumnA ColumnB
5 3
100 1

Each number in columnBis 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 columnBpercentage.

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

formula to define relationship between two sets
 

Hi Dana,

Doh! Now I get it. Thanks.


Greg


On Apr 16, 6:21 am, "Dana DeLouis" wrote:
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

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

"Greg Lovern" wrote in message

ups.com...



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 getB:

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

On Apr 15, 9:12 pm, "Dana DeLouis" wrote:
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.

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

"Greg Lovern" wrote in message

oups.com...

Thanks, but where can I find a refresher on how to solve forMandB?
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

On Apr 15, 6:36 pm, Barb Reinhardt
wrote:
Y = MX +B

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

Solve forMandB.

In B1 enter = (your value forM) * A1 + (Your value forB)

"Greg Lovern" wrote:
I've been given a table with two columns and two rows:

ColumnA ColumnB
5 3
100 1

Each number in columnBis 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 columnBpercentage.

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -