Goal Seek in User Defined Functions
Andrew S. Dinnie
Hi.
Has anyone created a User Defined Function which allows the use of the
Goal Seek function for a number of different solution cells in a
worksheet ?
e.g. DoMyGoalSeek( OutputCell, Target, ChangingCell ) where
OutputCell is a cell reference on any sheet
Target is an integer
ChangingCell is a cell reference on any sheet
BTW I will use a comparison before the UDF is referenced to ensure that
the Goal Seek only occurs when data has changed. Otherwise, the
worksheet will never stop calculating !!!
The context of this question is attached.
Can anyone provide a hand ? Thanks...
Regards
Andrew Dinnie
adi...@iinet.net.au
***
Context:
I am trying to calculate the Markup required on about 25 different
products to reach a specified RRP for each. This needs to be done for a
number of countries as follows:
Product Cost * Markup + Other Costs = RRP RRP Specified
1 $100 100% aaa xxx 500 450
2 $200 150% bbb yyy 600 550
3 $300 200% ccc zzz 700 650
.
.
.
n
The problem is that there are a number of other costs which eventually
refer to the Markup when calculating the RRP. These costs include a
number of formulae. Hence I cannot use a formula to backsolve (circular
references, too complex to maintain).
The goal seek function must be automated as the RRP needs to change as
the input data changes frequently.
I have tried to use VBA code to automate this routine but with no luck.
***
--
Andrew S. Dinnie B Bus. MBA
Principal Consultant, Trident Commercial Services
Phone: 61-411 88 2838 Fax: 61-9 242 2180
email: adi...@iinet.net.au www: http://www.iinet.net.au/~adinnie
Stephen Bullen
Hi Andrew
UDFs can not change the values of cells, and hence can not perform a
sheet-based goal seek.
You have two alternatives:
1. Use a cell-based goal seek formula. It is possible to use
circular references to perform cell-based goal-seeking without any
programming.
2. If your calculations are entirely in VBA, you can use a VBA goal
seeking routine.
See gsdemo.xls and goalseek.xls respectively from my web page for
examples.
Regards
Stephen Bullen, Excel MVP
http://ourworld.compuserve.com/homepages/Stephen_Bullen
Andrew S. Dinnie
Thanks.
I have downloaded & will check out. Your page appears very impressive.
Best wishes
Andrew