Finding solution by narrowing down

[pietermeulendijk]

Hi guys,

I am new to this forum and obviously have an Excel VBA question:

I have a macro that finds an optimal solution in K14 while N55 must be 0, apart from some other constraints. The method I currently use consists of starting with entering 0 in K14, check whether N55 is 0, and if not, entering 0.5 in K14, check whether N55 is zero, and if not, entering 1.0 in K14, etc. etc. Excel will find the solution, but it takes a lot of time.

The macro looks like this:

Please Login or Register  to view this content.

I was wondering whether there would be a faster method of obtaining my desired result. For example, if I know that the value must lie between 0 and 1000, could I use a macro the narrows down to the solution, instead of trial and error starting from 0?
If so, could you help me with such code? Or perhaps, such code is already available?

Please let me know if I do not comply with certain forum rules, as I do intent to.

Thanks for your help!
Pieter

[Richard Buttrey]

Hi Pieter,

Before considering a VBA solution have you tried the standard Excel Solver functionality approach?

Rgds

[Domski]

Have you tried using Solver? If you haven't already you may need to enble it through Tools...Add-Ins.

Remember to use code tags when quoting code as per the forum rules.

Dom

[pietermeulendijk]

Yes, I have tried using Solver, but that gives an error: 'error in constraint or destination cells'.

Perhaps, the calculations behind the applications

Application.Run "Conversie"
Application.Run "BBV_max"
Application.Run "BBV_max_A12"

are too complicated to have Solver find a solution.

Would there be any other solutions?

Regards,
Pieter

[Richard Buttrey]

Hi Pieter,

Do any of the cell formulae in the constraint or destination cells evaluate with an error?

Rgds

[pietermeulendijk]

No, they don't. The constraint formulae are optimizations on their own (they are macros using the goal seek function). That's why I think the Solver does not work.

Wouldn't there be any other possibilities to work around the Solver?

[mikerickson]

What you have is a function, with input in K14 and output in K55 and you are looking for a zero of that function.

If that function is it always increasing or always decreasing, you could use a binary search.
This will also work if the function is continuous and the values at 0 and 1000 have different signs.

Please Login or Register  to view this content.

[pietermeulendijk]

Hi mikerickson,

Thanks for your reply. I get the idea of the code. There is one thing that prevents it from working correctly in my case, however. In my first post, the code includes running application "Conversie". This application must be run intermediately and changes the result in N55. Here are its contents:

Please Login or Register  to view this content.

Where and how would you integrate this piece of code?

Regards,
Pieter

[mikerickson]

Then just put it in where needed

Please Login or Register  to view this content.

The linear search in the OP takes (up to) 2000 loops to find a 0 between 0 and 1000.
A binary search will do it in 22 loops. Log(1000/.001)/Log(2)

(the .001 is the closeness specified in the Loop Until condition.)

[pietermeulendijk]

Thanks mikerickson,

Works perfectly now, and much faster and more accurate than before!

Case solved.

Regards,
Pieter