How can I use Excel to solve an equation?
 

I have a rather complex equation that I cannot solve with pen and paper. I
need to calculate x for a very large nr of variables, by entering r. Can I
type an equation in Excel so that it can solve it automatically? (I don't
want to use the goal seek 150 times!) The equation is:

r=X*(1.24+ln(X))

I have office 2003. Thank you!

How can I use Excel to solve an equation?
 

Suppose you have your X values in column A, enter formula below in cell B1:

=A1*(1.24+LN(A1))

and fill it down as required, you get r values in column B.

Regards,
Stefi


€žtitina€� ezt Ã*rta:

I have a rather complex equation that I cannot solve with pen and paper. I
need to calculate x for a very large nr of variables, by entering r. Can I
type an equation in Excel so that it can solve it automatically? (I don't
want to use the goal seek 150 times!) The equation is:

r=X*(1.24+ln(X))

I have office 2003. Thank you!

How can I use Excel to solve an equation?
 

If you can't reverse the formula, (I think you should), you can use this goalseeking function.

If you're new to VBA functions, follow the instructions in the text at the end
You can set up a table with r's in A1 and down, and use this formula in B1 and below:

=Backward(A1,0)
--
Kind regards,

Niek Otten



Function FindX(x As Double)
FindX = x * (1.24 + Application.WorksheetFunction.Ln(x))
End Function

' ================================================== =================
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 NotReadyYet As Boolean
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
NotReadyYet = True
IterCount = 1
LowVar = ReasonableGuess
LowResult = Forward(LowVar, MoreArguments)
HighVar = LowVar * 1.2
HighResult = Forward(HighVar, MoreArguments)

While NotReadyYet
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 = FindX(a)
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 InsertModule. 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.

Press ALT+F11 again to return to your Excel worksheet.

You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)

================================================

"titina" wrote in message ...
I have a rather complex equation that I cannot solve with pen and paper. I
need to calculate x for a very large nr of variables, by entering r. Can I
type an equation in Excel so that it can solve it automatically? (I don't
want to use the goal seek 150 times!) The equation is:

r=X*(1.24+ln(X))

I have office 2003. Thank you!

How can I use Excel to solve an equation?
 

Hey Niek! It worked like a charm!!! You can't imagine how much I appreciate
it!!!

"Niek Otten" wrote:

If you can't reverse the formula, (I think you should), you can use this goalseeking function.

If you're new to VBA functions, follow the instructions in the text at the end
You can set up a table with r's in A1 and down, and use this formula in B1 and below:

=Backward(A1,0)
--
Kind regards,

Niek Otten



Function FindX(x As Double)
FindX = x * (1.24 + Application.WorksheetFunction.Ln(x))
End Function

' ================================================== =================
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 NotReadyYet As Boolean
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
NotReadyYet = True
IterCount = 1
LowVar = ReasonableGuess
LowResult = Forward(LowVar, MoreArguments)
HighVar = LowVar * 1.2
HighResult = Forward(HighVar, MoreArguments)

While NotReadyYet
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 = FindX(a)
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 InsertModule. 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.

Press ALT+F11 again to return to your Excel worksheet.

You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)

================================================

"titina" wrote in message ...
I have a rather complex equation that I cannot solve with pen and paper. I
need to calculate x for a very large nr of variables, by entering r. Can I
type an equation in Excel so that it can solve it automatically? (I don't
want to use the goal seek 150 times!) The equation is:

r=X*(1.24+ln(X))

I have office 2003. Thank you!

How can I use Excel to solve an equation?
 

r=X*(1.24+ln(X))

This isn't as nice as Niek's excellent example. Here's a simple version of
Newton's equation without much error checking.

Function Find_X(r, Optional Guess = 5)
Dim x
Dim j As Long

x = Guess
For j = 1 To 15
x = (25 * (r + x)) / (56 + 25 * Log(x))
Next j
Find_X = x
End Function

Sub TestIt()
Dim r, x
'// Here we know x, get test value for r
x = 2
r = x * (1.24 + Log(x))

'// Here, r equals 3.86629436111989
'// Now, try to solve for x given r
Debug.Print Find_X(r)
'// returns 2. :)
End Sub

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"titina" wrote in message
...
Hey Niek! It worked like a charm!!! You can't imagine how much I
appreciate
it!!!

"Niek Otten" wrote:

If you can't reverse the formula, (I think you should), you can use this
goalseeking function.

If you're new to VBA functions, follow the instructions in the text at
the end
You can set up a table with r's in A1 and down, and use this formula in
B1 and below:

=Backward(A1,0)
--
Kind regards,

Niek Otten



Function FindX(x As Double)
FindX = x * (1.24 + Application.WorksheetFunction.Ln(x))
End Function

' ================================================== =================
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 NotReadyYet As Boolean
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
NotReadyYet = True
IterCount = 1
LowVar = ReasonableGuess
LowResult = Forward(LowVar, MoreArguments)
HighVar = LowVar * 1.2
HighResult = Forward(HighVar, MoreArguments)

While NotReadyYet
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 = FindX(a)
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 InsertModule. 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.

Press ALT+F11 again to return to your Excel worksheet.

You should now be able to use the function as if it were a built-in
function of Excel, like =SUM(..)

================================================

"titina" wrote in message
...
I have a rather complex equation that I cannot solve with pen and paper.
I
need to calculate x for a very large nr of variables, by entering r.
Can I
type an equation in Excel so that it can solve it automatically? (I
don't
want to use the goal seek 150 times!) The equation is:

r=X*(1.24+ln(X))

I have office 2003. Thank you!