Solving for variable with a circular reference

[MikeyT]

Hi

I am trying to solve for a variable that refers to itself. I am using MS
Excel 2003. I have a feeling I need to use the iteration function or solve
mathematically but am wondering if I can get some hints.

Example: solving for "A." All of the other numbers are variables that I
can solve for separately, and are used as examples here.

A = (2,403,293*54.23)/(129,788,655+(2,405,539*(A-0.07)/A))

If I do something like B44=(B38*B39)/(B40+(B20*(B44-B25)/B44)) I'll get the
#DIV/0! error.

Same if I try to split out the formula into 2 formulas and making them
dependent on one another:

B41=(B20*(B42-B25))/B42
B42=(B38*B39)/(B40+B41)

Any hints to help me would be greatly appreciated!

Thank you for your time and wisdom.

MikeyT

 

[Mike Middleton]

MikeyT -

In cell A1 I enter 1

In cell A2 I enter =(2403293*54.23)/(129788655+(2405539*(A1-0.07)/A1))

In cell A3 I enter =A1-A2

I choose Tools | Goal Seek, I enter "Set cell:" A2 "To value:" 0 (zero) "By
changing cell:" A1, and I click OK.

Cell A1 shows 0.987176191

- Mike
http://www.mikemiddleton.com

 

[Harlan Grove]

I am trying to solve for a variable that refers to itself. I am
using MS Excel 2003. I have a feeling I need to use the iteration
function or solve mathematically but am wondering if I can get some
hints.

Example: solving for "A." All of the other numbers are variables
that I can solve for separately, and are used as examples here.

A = (2,403,293*54.23)/(129,788,655+(2,405,539*(A-0.07)/A))

Oo-oo-oo, what a little algebra will do-oo-oo.

Rewrite your formula as follows and simplify.

A = B * C / (D + E * (A - F) / A)
= B * C / (D + E * A / A - E * F / A)
= B * C / (D + E - E * F / A)

B * C = A * (D + E - E * F / A)
= A * (D + E) - E * F * A / A
= A * (D + E) - E * F

A = (B * C + E * F) / (D + E)

A = (2403293 * 54.23 + 2405539 * 0.07) / (129788655 + 2405539)
A = 0.9871762380123895607699684601882

[evaluated with Microsoft's PowerToy Calculator]

 

[MikeyT]

Okay, here's what I tried, not sure yet if it works, what do you think?

B43=ROUND((B38*B39)/(B40+(B20*(B44-B25)/B44)),10)

B44=IF(B45=B43, "Yes", "No")

B45 Manual Input

Would rather not round, but this is how I got a number that seemed to work.
There's got to be a better way to do this.

 

[MikeyT]

Mike,

Thanks so much, this was really helpful. One follow up question, did you
mean to type "Set cell:" A3 "To value:" 0 (zero) instead of A2? Using A3
gets me very close to the number (appears good to about 5 decimal places,
then a bit different). Just wanted to make sure. My first time using Goal
Seek. Thanks again, I've learned a lot tonight!

 

[MikeyT]

Harlan,

Thanks so much, I had a feeling I'd be a bit red faced after getting
responses! Clearly it's been a long time since I've done much math! Thank
you very much! The solution you provided equates to the number I got when
using my method (without the rounding) and is very close to Mike M's approach
as well. Thanks for making my late night a lot easier. This discussion
group is an amazing resource. Thanks for making this experience so valuable.

MikeyT

I am trying to solve for a variable that refers to itself. I am
using MS Excel 2003. I have a feeling I need to use the iteration
function or solve mathematically but am wondering if I can get some
hints.

Example: solving for "A." All of the other numbers are variables
that I can solve for separately, and are used as examples here.

A = (2,403,293*54.23)/(129,788,655+(2,405,539*(A-0.07)/A))

Oo-oo-oo, what a little algebra will do-oo-oo.

Rewrite your formula as follows and simplify.

A = B * C / (D + E * (A - F) / A)
= B * C / (D + E * A / A - E * F / A)
= B * C / (D + E - E * F / A)

B * C = A * (D + E - E * F / A)
= A * (D + E) - E * F * A / A
= A * (D + E) - E * F

A = (B * C + E * F) / (D + E)

A = (2403293 * 54.23 + 2405539 * 0.07) / (129788655 + 2405539)
A = 0.9871762380123895607699684601882

[evaluated with Microsoft's PowerToy Calculator]

 

[Mike Middleton]

Yes, you're correct.

- Mike

Mike,

Thanks so much, this was really helpful. One follow up question, did you
mean to type "Set cell:" A3 "To value:" 0 (zero) instead of A2? Using A3
gets me very close to the number (appears good to about 5 decimal places,
then a bit different). Just wanted to make sure. My first time using
Goal
Seek. Thanks again, I've learned a lot tonight!