If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 How can I put Goal Seek into a cell formula?
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## How can I put Goal Seek into a cell formula?

#1
October 27th 06, 02:54 PM posted to microsoft.public.excel.worksheet.functions
 ecalexan external usenet poster Posts: 1
How can I put Goal Seek into a cell formula?

I'm working on a Sales bid template where I need to calculate a unit price
but that price must be equal to a certain profit %. Goal seek will do the
job, but the template is to be used by beginning Excel-ers. The problem I
have is that I need to have the formula calculate to a certain profit margin
%, but I also need to allow for adjustment to that calculated price by the
user in case they want to "tweek" the calculated price to see what happens to
profit margin % if they move the price up or down. Therefore, my gross Sales
dollars must be equal to Price X Units. I just need to calc the starting
price in one cell (to equal desired margin %)(A), have a second cell for
"adjustment" (B) and a third that is adjusted price (A+B). Any ideas?
#2
October 27th 06, 05:29 PM posted to microsoft.public.excel.worksheet.functions
 Bernard Liengme external usenet poster Posts: 4,393
How can I put Goal Seek into a cell formula?

Not possible
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"ecalexan" > wrote in message
news
> I'm working on a Sales bid template where I need to calculate a unit price
> but that price must be equal to a certain profit %. Goal seek will do the
> job, but the template is to be used by beginning Excel-ers. The problem I
> have is that I need to have the formula calculate to a certain profit
> margin
> %, but I also need to allow for adjustment to that calculated price by the
> user in case they want to "tweek" the calculated price to see what happens
> to
> profit margin % if they move the price up or down. Therefore, my gross
> Sales
> dollars must be equal to Price X Units. I just need to calc the starting
> price in one cell (to equal desired margin %)(A), have a second cell for
> "adjustment" (B) and a third that is adjusted price (A+B). Any ideas?

#3
October 27th 06, 06:52 PM posted to microsoft.public.excel.worksheet.functions
 Niek Otten external usenet poster Posts: 3,440
How can I put Goal Seek into a cell formula?

It is not exactly clear to me what you require. But I post one of my standard answers, just in case it happens to be what you
need.
This "solution" requires a User Defined Function (UDF) to be installed. Instructions for that at the end of this post.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

' ================================================== =================
Function Backward(ValueToBeFound As Double, MoreArguments As Double, _
Optional ReasonableGuess, Optional MaxNumberIters, _
Optional MaxDiffPerc) As Double
'
' Niek Otten, March 22 2006
'
' This EXAMPLE function goalseeks another function,
' called Forward. It works for almost any continuous function,
' although if that function has several maximum and/or minimum
' values, the value of the ReasonableGuess argument becomes
' important.
' It calculates the value for ReasonableGuess and for
' 1.2 * ReasonableGuess.
' It assumes that the function's graph is a straight line and
' extrapolates that line from these two values to find the value
' for the argument required to achieve ValueToBeFound.
' Of course that doesn't come out right, so it does it again for
' this new result and one of the other two results, depending on
' the required direction (greater or smaller).
' This process is repeated until the maximum number of calculations
' has been reached, in which case an errorvalue is returned,
' or until the value found is close enough, in which case
' the value of the most recently used argument is returned

Dim LowVar As Double, HighVar As Double, NowVar As Double
Dim LowResult As Double, HighResult As Double, NowResult As Double
Dim MaxDiff As Double
Dim IterCount As Long

If IsMissing(ReasonableGuess) Then ReasonableGuess = 1.5 ' use default Values
If IsMissing(MaxNumberIters) Then MaxNumberIters = 20 ' that make sense in the
If IsMissing(MaxDiffPerc) Then MaxDiffPerc = 0.001 ' context of the function

MaxDiff = ValueToBeFound * MaxDiffPerc
IterCount = 1
LowVar = ReasonableGuess
LowResult = Forward(LowVar, MoreArguments)
HighVar = LowVar * 1.2
HighResult = Forward(HighVar, MoreArguments)

IterCount = IterCount + 1
If IterCount > MaxNumberIters Then
Backward = CVErr(xlErrValue) 'or some other errorvalue
Exit Function
End If

NowVar = ((ValueToBeFound - LowResult) * (HighVar - LowVar) + LowVar _
* (HighResult - LowResult)) / (HighResult - LowResult)
NowResult = Forward(NowVar, MoreArguments)
If NowResult > ValueToBeFound Then
HighVar = NowVar
HighResult = NowResult
Else
LowVar = NowVar
LowResult = NowResult
End If
If Abs(NowResult - ValueToBeFound) < MaxDiff Then NotReadyYet = False
Wend

Backward = NowVar

End Function
' ================================================== =================

Function Forward(a As Double, b As Double) As Double
' This is just an example function;
' almost any continous function will work
Forward = 3 * a ^ (1.5) + b
End Function
' ================================================== =================

================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================

"ecalexan" > wrote in message news | I'm working on a Sales bid template where I need to calculate a unit price
| but that price must be equal to a certain profit %. Goal seek will do the
| job, but the template is to be used by beginning Excel-ers. The problem I
| have is that I need to have the formula calculate to a certain profit margin
| %, but I also need to allow for adjustment to that calculated price by the
| user in case they want to "tweek" the calculated price to see what happens to
| profit margin % if they move the price up or down. Therefore, my gross Sales
| dollars must be equal to Price X Units. I just need to calc the starting
| price in one cell (to equal desired margin %)(A), have a second cell for
| "adjustment" (B) and a third that is adjusted price (A+B). Any ideas?

#4
December 1st 06, 07:51 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 2
How can I put Goal Seek into a cell formula?

Hi Niek,

I have a Goal Seek problem and I would like to hear your opinion to a
solution.
particular situation.

On a sheet (eg. Sheet1) I have the following cells and their content:

A1 contains 200.000 (number) and displays 200.000 meaning 'All Customer
Market'
A2 contains 1% (percentage) and displays 1% meaning 'Sales Target in %'
A3 contains =A1*A2 (formula) and displays 2.000 meaning 'Sales Target
in No of Customers'

A4 contains =A3*1.000 (formula) and displays 2.000.000 meaning 'Sales'
(price is 1.000)
A5 contains 5.000.000 (number) and displays 5.000.000 meaning 'Costs'
A6 contains =A4-A5 (formula) and displays -3.000.000 meaning 'Profit'

Now, I would like a new cell eg. A7 to dynamically calculate, how many
Customers I need in order to get a break-even in Profit (A6).

I can easy do this manually with the Goal Seek Tool, by setting the
following options:
Set Cell = A6 (Profit)
To Value = 0
By Changing Cell = A2 (Sales Target in %)

The problem with Goal Seek is, that Goal Seek (after finding the
result) changes A2 (Sales Target in %) to the calculated result and
then the sheet re-calculates and all other cells changes as well -
finally setting A6 (Profit) to 0.

What I want is just a new cell eg. A7, where the Goal Seek result from
A3 ('Sales Target in No of Customers) is dynamically calculated.
But all other cells are left unchanged (with the same content as
described above).

Hope it is understandable.

Kind regards,
Aleksander

Niek Otten skrev:
> It is not exactly clear to me what you require. But I post one of my standard answers, just in case it happens to be what you
> need.
> This "solution" requires a User Defined Function (UDF) to be installed. Instructions for that at the end of this post.
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> ' ================================================== =================
> Function Backward(ValueToBeFound As Double, MoreArguments As Double, _
> Optional ReasonableGuess, Optional MaxNumberIters, _
> Optional MaxDiffPerc) As Double
> '
> ' Niek Otten, March 22 2006
> '
> ' This EXAMPLE function goalseeks another function,
> ' called Forward. It works for almost any continuous function,
> ' although if that function has several maximum and/or minimum
> ' values, the value of the ReasonableGuess argument becomes
> ' important.
> ' It calculates the value for ReasonableGuess and for
> ' 1.2 * ReasonableGuess.
> ' It assumes that the function's graph is a straight line and
> ' extrapolates that line from these two values to find the value
> ' for the argument required to achieve ValueToBeFound.
> ' Of course that doesn't come out right, so it does it again for
> ' this new result and one of the other two results, depending on
> ' the required direction (greater or smaller).
> ' This process is repeated until the maximum number of calculations
> ' has been reached, in which case an errorvalue is returned,
> ' or until the value found is close enough, in which case
> ' the value of the most recently used argument is returned
>
> Dim LowVar As Double, HighVar As Double, NowVar As Double
> Dim LowResult As Double, HighResult As Double, NowResult As Double
> Dim MaxDiff As Double
> Dim IterCount As Long
>
> If IsMissing(ReasonableGuess) Then ReasonableGuess = 1.5 ' use default Values
> If IsMissing(MaxNumberIters) Then MaxNumberIters = 20 ' that make sense in the
> If IsMissing(MaxDiffPerc) Then MaxDiffPerc = 0.001 ' context of the function
>
> MaxDiff = ValueToBeFound * MaxDiffPerc
> IterCount = 1
> LowVar = ReasonableGuess
> LowResult = Forward(LowVar, MoreArguments)
> HighVar = LowVar * 1.2
> HighResult = Forward(HighVar, MoreArguments)
>
> IterCount = IterCount + 1
> If IterCount > MaxNumberIters Then
> Backward = CVErr(xlErrValue) 'or some other errorvalue
> Exit Function
> End If
>
> NowVar = ((ValueToBeFound - LowResult) * (HighVar - LowVar) + LowVar _
> * (HighResult - LowResult)) / (HighResult - LowResult)
> NowResult = Forward(NowVar, MoreArguments)
> If NowResult > ValueToBeFound Then
> HighVar = NowVar
> HighResult = NowResult
> Else
> LowVar = NowVar
> LowResult = NowResult
> End If
> If Abs(NowResult - ValueToBeFound) < MaxDiff Then NotReadyYet = False
> Wend
>
> Backward = NowVar
>
> End Function
> ' ================================================== =================
>
> Function Forward(a As Double, b As Double) As Double
> ' This is just an example function;
> ' almost any continous function will work
> Forward = 3 * a ^ (1.5) + b
> End Function
> ' ================================================== =================
>
>
> ================================================
> Pasting a User Defined Function (UDF)
> Niek Otten, March 31, 2006
>
> If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
> steps:
>
> Select all the text of the function.
> CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
> Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
> Visual Basic Editor (VBE).
> From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
> press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
> You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
> ================================================
>
>
> "ecalexan" > wrote in message news > | I'm working on a Sales bid template where I need to calculate a unit price
> | but that price must be equal to a certain profit %. Goal seek will do the
> | job, but the template is to be used by beginning Excel-ers. The problem I
> | have is that I need to have the formula calculate to a certain profit margin
> | %, but I also need to allow for adjustment to that calculated price by the
> | user in case they want to "tweek" the calculated price to see what happens to
> | profit margin % if they move the price up or down. Therefore, my gross Sales
> | dollars must be equal to Price X Units. I just need to calc the starting
> | price in one cell (to equal desired margin %)(A), have a second cell for
> | "adjustment" (B) and a third that is adjusted price (A+B). Any ideas?

#5
December 1st 06, 08:43 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 2
How can I put Goal Seek into a cell formula?

Hi Niek,

I have a Goal Seek problem and I would like to hear your opinion to a
solution.
particular situation.

On a sheet (eg. Sheet1) I have the following cells and their content:

A1 contains 200.000 (number) and displays 200.000 meaning 'All Customer
Market'
A2 contains 1% (percentage) and displays 1% meaning 'Sales Target in %'
A3 contains =A1*A2 (formula) and displays 2.000 meaning 'Sales Target
in No of Customers'

A4 contains =A3*1.000 (formula) and displays 2.000.000 meaning 'Sales'
(price is 1.000)
A5 contains 5.000.000 (number) and displays 5.000.000 meaning 'Costs'
A6 contains =A4-A5 (formula) and displays -3.000.000 meaning 'Profit'

Now, I would like a new cell eg. A7 to dynamically calculate, how many
Customers I need in order to get a break-even in Profit (A6).

I can easy do this manually with the Goal Seek Tool, by setting the
following options:
Set Cell = A6 (Profit)
To Value = 0
By Changing Cell = A2 (Sales Target in %)

The problem with Goal Seek is, that Goal Seek (after finding the
result) changes A2 (Sales Target in %) to the calculated result and
then the sheet re-calculates and all other cells changes as well -
finally setting A6 (Profit) to 0.

What I want is just a new cell eg. A7, where the Goal Seek result from
A3 ('Sales Target in No of Customers) is dynamically calculated.
But all other cells are left unchanged (with the same content as
described above).

Hope it is understandable.

Kind regards,
Aleksander

but Goal Seek finds a solution and changes A6 (Profit

Niek Otten skrev:
> It is not exactly clear to me what you require. But I post one of my standard answers, just in case it happens to be what you
> need.
> This "solution" requires a User Defined Function (UDF) to be installed. Instructions for that at the end of this post.
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> ' ================================================== =================
> Function Backward(ValueToBeFound As Double, MoreArguments As Double, _
> Optional ReasonableGuess, Optional MaxNumberIters, _
> Optional MaxDiffPerc) As Double
> '
> ' Niek Otten, March 22 2006
> '
> ' This EXAMPLE function goalseeks another function,
> ' called Forward. It works for almost any continuous function,
> ' although if that function has several maximum and/or minimum
> ' values, the value of the ReasonableGuess argument becomes
> ' important.
> ' It calculates the value for ReasonableGuess and for
> ' 1.2 * ReasonableGuess.
> ' It assumes that the function's graph is a straight line and
> ' extrapolates that line from these two values to find the value
> ' for the argument required to achieve ValueToBeFound.
> ' Of course that doesn't come out right, so it does it again for
> ' this new result and one of the other two results, depending on
> ' the required direction (greater or smaller).
> ' This process is repeated until the maximum number of calculations
> ' has been reached, in which case an errorvalue is returned,
> ' or until the value found is close enough, in which case
> ' the value of the most recently used argument is returned
>
> Dim LowVar As Double, HighVar As Double, NowVar As Double
> Dim LowResult As Double, HighResult As Double, NowResult As Double
> Dim MaxDiff As Double
> Dim IterCount As Long
>
> If IsMissing(ReasonableGuess) Then ReasonableGuess = 1.5 ' use default Values
> If IsMissing(MaxNumberIters) Then MaxNumberIters = 20 ' that make sense in the
> If IsMissing(MaxDiffPerc) Then MaxDiffPerc = 0.001 ' context of the function
>
> MaxDiff = ValueToBeFound * MaxDiffPerc
> IterCount = 1
> LowVar = ReasonableGuess
> LowResult = Forward(LowVar, MoreArguments)
> HighVar = LowVar * 1.2
> HighResult = Forward(HighVar, MoreArguments)
>
> IterCount = IterCount + 1
> If IterCount > MaxNumberIters Then
> Backward = CVErr(xlErrValue) 'or some other errorvalue
> Exit Function
> End If
>
> NowVar = ((ValueToBeFound - LowResult) * (HighVar - LowVar) + LowVar _
> * (HighResult - LowResult)) / (HighResult - LowResult)
> NowResult = Forward(NowVar, MoreArguments)
> If NowResult > ValueToBeFound Then
> HighVar = NowVar
> HighResult = NowResult
> Else
> LowVar = NowVar
> LowResult = NowResult
> End If
> If Abs(NowResult - ValueToBeFound) < MaxDiff Then NotReadyYet = False
> Wend
>
> Backward = NowVar
>
> End Function
> ' ================================================== =================
>
> Function Forward(a As Double, b As Double) As Double
> ' This is just an example function;
> ' almost any continous function will work
> Forward = 3 * a ^ (1.5) + b
> End Function
> ' ================================================== =================
>
>
> ================================================
> Pasting a User Defined Function (UDF)
> Niek Otten, March 31, 2006
>
> If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
> steps:
>
> Select all the text of the function.
> CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
> Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
> Visual Basic Editor (VBE).
> From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
> press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
> You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
> ================================================
>
>
> "ecalexan" > wrote in message news > | I'm working on a Sales bid template where I need to calculate a unit price
> | but that price must be equal to a certain profit %. Goal seek will do the
> | job, but the template is to be used by beginning Excel-ers. The problem I
> | have is that I need to have the formula calculate to a certain profit margin
> | %, but I also need to allow for adjustment to that calculated price by the
> | user in case they want to "tweek" the calculated price to see what happens to
> | profit margin % if they move the price up or down. Therefore, my gross Sales
> | dollars must be equal to Price X Units. I just need to calc the starting
> | price in one cell (to equal desired margin %)(A), have a second cell for
> | "adjustment" (B) and a third that is adjusted price (A+B). Any ideas?

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Is it possible? DakotaNJ Excel Worksheet Functions 25 September 18th 06 09:30 PM Reusing formula Tony29 Excel Discussion (Misc queries) 7 September 7th 06 03:34 AM Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 05:18 PM Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM

All times are GMT +1. The time now is 09:35 PM.