Goal Seek and SOLVER

Hi,

I was wandering if anyone could help me.

I am trying to get Excel to run a goal seek and a solver at the same time.

In other words I need to program to run the solver for each itteration of the goal seek.

Any ideas for a total programming novice?



Thanks

Dale Birrell

Hi, thanks for the reply.

However im sorry but im not sure i understand, perhaps i'll explain what im trying to do a little more clearly.

I'll just use the names of the variables, their meanings are irrelevant.

Im trying to use solver, to maximise drive by varying flat and twist, subject to the constraint RM = HM and the ranges that flat and twist lie in.

Having maximised drive, i would then like to vary speed untill drive = resistance however both drive and resistance are a function of speed.

So if you get what i mean, at each itteration of speed which will change both drive and resistance, i would like to maximise the drive, untill convergence.


The only way i can get it to work so far is to goal seek the speed (for drive = resistance) and with the speed 'goal seeked' run the solver to get the maximised drive at that speed, which in turn, un balances the drive = resistance and hence a new speed is chosen. This process repeats itself for quite sometime before converging on the answer.

Anyway, thanks again for the reply see what you can make of this

Hi,

I've tried what you suggested but Solver seems to ignore the drive = resistance constraint.

I've attached the spreadsheet, all the variables are on the final page called 'solution'


Thanks again

Im not really sure, i've just opened the program on my laptop and it's fine.

i'll re-attach it

I see, yeh i've got XlXtrFun

The results you've posted there look pretty good, that's what i was lloking for,

but when I open the attached file, cells D5 and D12 say #NAME?, and also when i check SOLVER in references, it comes up with "name conflict with existing module, project or object library."

here you go.

that's pretty much exactly what the doctor ordered!

Is it possible to explain how you've written it?

thanks a lot, that's a big help.

thank you very much,

much appreciation

sorry, but how did you get rid of the prompt after solver runs asking you if you'd like to keep the solution?

thanks a lot

Hello again,

I had to make some adjustments to the rest of the spreadsheet and tried to re-implement the code you had provided to run the two models but it wouldn't work. What was the reference to something in the excel folder in program files?

If it's not too much trouble, could you possibly do it again?

Im sorry, it's very lazy of me I really should get some books out and learn VB for myself but I just don't have the time just now.

Thanks

Dale