Mimic 'Goal Seek' function with formula

[Tj_El]

Hello Excel Guru's

I could do with your collective help on this task.
There is a function in Excel called Goal Seek which I know how to use but what I actually need to have is a formula that mimics this function.

My scenario is as follows:
Cell A1 = 13.50 - derived by formula
Cell A2 = 30.00 - derived by formula
Cell A3 = 687,500.00
Cell A4 = variable value but for now the value = 1
Cell A5 = A4(A2-A1)

Using goal seek, I set Cell A5 to equal Cell A3 by changing Cell A4. That gave me a value.

Now, what would the formula be to mimic that series of calculations to arrive at the answer?

I hope I have explained my request correctly...

 

[Juan Pablo González]

The formula in A5 is

=A4*(A2-A1)

or

=A4/(A2-A1)

or what ?

 

[Tj_El]

I knew I had omitted something....

Cells A2 and A1 themselves are formulae based on the value in Cell A4 and that is what is causing me to flounder.

A1 = (10)+(10+(10*(A4*10%)))+(10+(10*(A4*10%)))

A2 = (20)+(20+(20*(A4*15%)))+(20+(20*(A4*15%)))

A4 is the variable representing number of years in sequence so in the first yr it will be 1, the following year 2 and so on.

This is getting a bit much for my brain to deal with on a Friday afternoon....

I hope I have filled in the missing part sufficiently?
Maybe I will resolve to working things out year by year....

 

[Juan Pablo González]

Ok, but you didn't answer my question either

 

[Juan Pablo González]

Ok, since you decided not to answer, I'm assuming:

Libro3
	A	B	C	D
1	851.69
2	2525.07
3	687,500.00
4	410.85
5	687,500.00
Hoja1

Is this what you want ? to do this, you need to check the "Iterate" option in Tools | Options, Calculate. I used 32000 iterations with a max change of 0.0000001

 

[Tj_El]

Ok, but you didn't answer my question either

Sorry, I was away from my desk hence the tardy reply......

But to answer your question, yes A5 = A4*(A2-A1)

 

[PHMayfield]

Since you only have one unknown, why not reduce this to a single equation and solve it. your given formulas reduce to:

4*(A4)^2 + 30*(A4) - 687500 = 0

which has solutions 410.8451 and -418.3451

 

[Tj_El]

Fanatastic!

I will try that out.

Huge thanks to you all! :D