Optimisation without Solver or Macros

Hi all, first post on the forum so please be gentle with me if I break any specific forum etiquette!

I have a spreadsheet that I would like to run through Spreadsheet Converter to turn it into a Flash or HTML file for use on a webpage, however I have been informed by the support team at SC that their software cannot make use of the Solver or any macro functions. Unfortunately Solver forms the core feature of the spreadsheet, so without it I cannot create the webpage.

I am looking for a way to solve a rather complex equation fx=0 ( fx consists of around 20 inputs each used 3 to 4 times) by altering 1 specific input. Solver does this nicely, I am not familiar with macros but I imagine they would also make light work of it, however since they are not supported, that is a moot point.

So, my question would be, can I replicate the solver feature using just basic functions within the spreadsheet, and without significantly re-arranging the equation in question?

I am attempting to come to solution myself using a small mock up:
\1
(I see [img] tags do not work here?)

I want AB-C2D=0 by altering D. Obviously this one would be easy to do with a quick re-arrangement however I would really rather avoid that route if possible given the complexity of the equation I am actually attempting to solve.

Thanks in advance for any help or suggestions!

You might want to consider looking through a few iterative processes to solve your equation.

One of the most commonly used iterative methods is the newton-raphson method. Do a search on that and have a look if it's something would solve your problem.

Hi kozy,

For your case, since you're only changing the variable D, it is a 1 variate equation. A B and C can be treated as a constant.

If differentiating is an issue, consider using a "proxy" for differentiation as the change in the function for a small change in D.

I.e. f'(D) = f(D) - f(D + delta) where delta is a very small number (the smaller the more accurate it is)

That sounds far easier, I will look into it, thanks!

Hi kozy,

First thing, that's one heck of a formula.

Second thing, see attached.

Note that, for a newton raphson method, if there are multiple solutions, the starting point (in sheet 2, i started with 50), can play a big role in determining the end solution.
P/S: Just did a check, the function appears to be strictly linear.

Regards,
Bing.

Do you want get the result without using Goalseek.Using Goalseek you will get result easily.For given problem D2=77.06 is answere.

hi Cozy,

Check this link
http://office.microsoft.com/en-gb/ex...005203894.aspx

you should be able to find Goal Seek in Tools menu

Regards,

Veejar

Originally Posted by shg

How about a binary search using formulas?

Well it certainly seems to work, however it looks far more complicated than the Newton method posted by quekbc. Thanks for your suggestion though!

Goalseek is in Tools menu in Excel2003
In 2007 Data, What if analysis, Goalseek.