mimic goalseek

I need to write a function that will mimic the function of goalseek.

I cannot use .goalseek in my code for other platforms cannot support.

If anyone could help me... it would be appreciated.

Just keep substituting values (incrementing your start value) until your
function achieves the goal (within a tolerance), or you reach some limit in
the number of increments. I don't think goalseek is any smarter than that.

--
Regards,
Tom Ogilvy

"Monique" <Monique@discussions.microsoft.com> wrote in message
news:CD3C32F2-C06F-4DA1-BAD6-61494F135B69@microsoft.com...
> I need to write a function that will mimic the function of goalseek.
>
> I cannot use .goalseek in my code for other platforms cannot support.
>
> If anyone could help me... it would be appreciated.

OK.

Right now, an input box appears. The user types in a number.
The number the user inputs is the goal that I want to affect the Price.

if i used goalseek, it would look like:

range("W42").goalseek goal:=newCV, changingcell:=range(price)

i don't have a set number of iterations. i just want it to reach the goal as
close as possible and update the cell at the end.

Can you please help me?



"Tom Ogilvy" wrote:

> Just keep substituting values (incrementing your start value) until your
> function achieves the goal (within a tolerance), or you reach some limit in
> the number of increments. I don't think goalseek is any smarter than that.
>
> --
> Regards,
> Tom Ogilvy
>
> "Monique" <Monique@discussions.microsoft.com> wrote in message
> news:CD3C32F2-C06F-4DA1-BAD6-61494F135B69@microsoft.com...
> > I need to write a function that will mimic the function of goalseek.
> >
> > I cannot use .goalseek in my code for other platforms cannot support.
> >
> > If anyone could help me... it would be appreciated.
>
>
>

In article <CD3C32F2-C06F-4DA1-BAD6-61494F135B69@microsoft.com>,
Monique@discussions.microsoft.com says...
> I need to write a function that will mimic the function of goalseek.
>
> I cannot use .goalseek in my code for other platforms cannot support.
>
> If anyone could help me... it would be appreciated.
>
I am curious as to what are the platforms that support XL VBA but not
goalseek. ;-)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

See Stephen Bullen's site:

http://www.bmsltd.ie/Excel/SBXLPage.asp

GoalSeek.zip (20 Aug 1996, 11k, 11343 downloads)
The standard goal seek function in Excel is OK, but can not cope with large,
complex spreadsheets. This is because it only does a simple recalc during
each iteration, and can not cope with circular references. The routine in
this file is a simple linear-interpolation goal seek written in VBA. The
goal seek calls a separate calculation procedure during each iteration, in
which you can include any code you like. For example, you could refresh a
pivot table, filter a database, consolidate data, copy and paste etc.

It is the 4th utility from the bottom.

Stephen has actually moved his site to this URL:

http://www.oaltd.co.uk/Excel/Default.htm

but the first on still seems to work.

the code in the download should be accessible.

--
Regards,
Tom Ogilvy


"Monique" <Monique@discussions.microsoft.com> wrote in message
news:CA6231BB-2C13-485B-B864-D1B5914D3E24@microsoft.com...
> OK.
>
> Right now, an input box appears. The user types in a number.
> The number the user inputs is the goal that I want to affect the Price.
>
> if i used goalseek, it would look like:
>
> range("W42").goalseek goal:=newCV, changingcell:=range(price)
>
> i don't have a set number of iterations. i just want it to reach the goal
as
> close as possible and update the cell at the end.
>
> Can you please help me?
>
>
>
> "Tom Ogilvy" wrote:
>
> > Just keep substituting values (incrementing your start value) until your
> > function achieves the goal (within a tolerance), or you reach some limit
in
> > the number of increments. I don't think goalseek is any smarter than
that.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Monique" <Monique@discussions.microsoft.com> wrote in message
> > news:CD3C32F2-C06F-4DA1-BAD6-61494F135B69@microsoft.com...
> > > I need to write a function that will mimic the function of goalseek.
> > >
> > > I cannot use .goalseek in my code for other platforms cannot support.
> > >
> > > If anyone could help me... it would be appreciated.
> >
> >
> >

Just to add:

goalseek uses the setting in the Iterations section of
tools=>Options=>Calculation.


--
Regards,
Tom Ogilvy


"Monique" <Monique@discussions.microsoft.com> wrote in message
news:CA6231BB-2C13-485B-B864-D1B5914D3E24@microsoft.com...
> OK.
>
> Right now, an input box appears. The user types in a number.
> The number the user inputs is the goal that I want to affect the Price.
>
> if i used goalseek, it would look like:
>
> range("W42").goalseek goal:=newCV, changingcell:=range(price)
>
> i don't have a set number of iterations. i just want it to reach the goal
as
> close as possible and update the cell at the end.
>
> Can you please help me?
>
>
>
> "Tom Ogilvy" wrote:
>
> > Just keep substituting values (incrementing your start value) until your
> > function achieves the goal (within a tolerance), or you reach some limit
in
> > the number of increments. I don't think goalseek is any smarter than
that.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Monique" <Monique@discussions.microsoft.com> wrote in message
> > news:CD3C32F2-C06F-4DA1-BAD6-61494F135B69@microsoft.com...
> > > I need to write a function that will mimic the function of goalseek.
> > >
> > > I cannot use .goalseek in my code for other platforms cannot support.
> > >
> > > If anyone could help me... it would be appreciated.
> >
> >
> >

In article <O81ra9YqFHA.3768@TK2MSFTNGP10.phx.gbl>, twogilvy@msn.com
says...
> Just keep substituting values (incrementing your start value) until your
> function achieves the goal (within a tolerance), or you reach some limit in
> the number of increments. I don't think goalseek is any smarter than that.
>
>
{shudder} I hope you are wrong. {grin}

In any case, if one wanted to implement a "simple" goalseek algorithm,
I'd recommend looking at, in addition to Stephen's code,

Newton's Method
http://mathworld.wolfram.com/NewtonsMethod.html
and
Numerical Recipes' 5.7 Numerical Derivatives
http://www.library.cornell.edu/nr/bookcpdf/c5-7.pdf

Of course, I haven't seen Stephen's code. It may already incorporate
the ideas from Numerical Recipes. ;-)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Thanks so much. This is very helpful.

"Tom Ogilvy" wrote:

> Just to add:
>
> goalseek uses the setting in the Iterations section of
> tools=>Options=>Calculation.
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Monique" <Monique@discussions.microsoft.com> wrote in message
> news:CA6231BB-2C13-485B-B864-D1B5914D3E24@microsoft.com...
> > OK.
> >
> > Right now, an input box appears. The user types in a number.
> > The number the user inputs is the goal that I want to affect the Price.
> >
> > if i used goalseek, it would look like:
> >
> > range("W42").goalseek goal:=newCV, changingcell:=range(price)
> >
> > i don't have a set number of iterations. i just want it to reach the goal
> as
> > close as possible and update the cell at the end.
> >
> > Can you please help me?
> >
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > Just keep substituting values (incrementing your start value) until your
> > > function achieves the goal (within a tolerance), or you reach some limit
> in
> > > the number of increments. I don't think goalseek is any smarter than
> that.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Monique" <Monique@discussions.microsoft.com> wrote in message
> > > news:CD3C32F2-C06F-4DA1-BAD6-61494F135B69@microsoft.com...
> > > > I need to write a function that will mimic the function of goalseek.
> > > >
> > > > I cannot use .goalseek in my code for other platforms cannot support.
> > > >
> > > > If anyone could help me... it would be appreciated.
> > >
> > >
> > >
>
>
>