Solver, Goal Seek or ??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a target cell and changing cell on the same row of a spreadsheet. I want to set my target cell to =0. The formula in the target cell references the changing cell and it works fine.

My problem is that I have a hundred and sometimes thousands of rows. Each row has a target cell that I want to zero. Is there something out there that can do this? Maybe I am using the wrong add-in. Any help would be appreciated.

Thanks.
 
If your changing cell is A1 and it contains the number 5
then in the target cell enter =IF(A1>0,A1,0). The results
will be a 5 in the target cell. If the changing cell is 0
then the target cell becomes 0. Is this what you are
looking for?

Charlie O'Neill
-----Original Message-----
I have a target cell and changing cell on the same row of
Click to expand...
a spreadsheet. I want to set my target cell to =0. The
formula in the target cell references the changing cell
and it works fine.
My problem is that I have a hundred and sometimes
Click to expand...
thousands of rows. Each row has a target cell that I want
to zero. Is there something out there that can do this?
Maybe I am using the wrong add-in. Any help would be
appreciated.
 
No.....the target cell actually contains an equation which when simplified looks like the following
=5+2x+lnx. The changing cell value resolves the unknown "x" so that the final answer in the target cell is always zero. I have no problems generating a correct answer using goal seek or solver, one row at a time. The problem is I have to do this for hundreds and even thousands of rows. Thanks in advance for any help you can provide

----- Charlie wrote: ----

If your changing cell is A1 and it contains the number 5
then in the target cell enter =IF(A1>0,A1,0). The results
will be a 5 in the target cell. If the changing cell is 0
then the target cell becomes 0. Is this what you are
looking for

Charlie O'Neill
-----Original Message----
I have a target cell and changing cell on the same row of
Click to expand...
a spreadsheet. I want to set my target cell to =0. The
formula in the target cell references the changing cell
and it works finethousands of rows. Each row has a target cell that I want
to zero. Is there something out there that can do this?
Maybe I am using the wrong add-in. Any help would be
appreciated
 
Suppose the 'x' cells are in column C and the formula is in column D.

Turn on the macro recorder (Tools | Macro > Record new macro...), use
Goal Seek to solve the D1=0 problem. Turn off the recorder. Switch to
the VBE and after cleaning up the code, you would have

Sub Macro9()
Range("D1").GoalSeek Goal:=0, ChangingCell:=Range("C1")
End Sub

Next, we generalize it to work with as many cells in column D have
formulas in them.

Sub Macro9Generalized()
Dim aCell As Range
With Range("d1")
For Each aCell In Range(.Cells(1, 1), .End(xlDown))
aCell.GoalSeek Goal:=0, ChangingCell:=aCell.Offset(0, -1)
Next aCell
End With
End Sub

Finally, recognize that since the function has a LN() part in it, when
the corresponding cell in C is empty, the result is a #NUM! error.
Goal Seek won't work and the macro faults. So, we build in a bit of
error protection:

Sub Macro9GeneralizedErrorTraps()
Dim aCell As Range
With Range("d1")
For Each aCell In Range(.Cells(1, 1), .End(xlDown))
With aCell
If IsEmpty(.Offset(0, -1).Value) Then
.Offset(0, -1).Value = 1
End If
.GoalSeek Goal:=0, ChangingCell:=.Offset(0, -1)
End With
Next aCell
End With
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
This worked very well for me! Thank you very much

Ton

----- Tushar Mehta wrote: ----

Suppose the 'x' cells are in column C and the formula is in column D

Turn on the macro recorder (Tools | Macro > Record new macro...), use
Goal Seek to solve the D1=0 problem. Turn off the recorder. Switch to
the VBE and after cleaning up the code, you would hav

Sub Macro9(
Range("D1").GoalSeek Goal:=0, ChangingCell:=Range("C1"
End Su

Next, we generalize it to work with as many cells in column D have
formulas in them

Sub Macro9Generalized(
Dim aCell As Rang
With Range("d1"
For Each aCell In Range(.Cells(1, 1), .End(xlDown)
aCell.GoalSeek Goal:=0, ChangingCell:=aCell.Offset(0, -1
Next aCel
End Wit
End Su

Finally, recognize that since the function has a LN() part in it, when
the corresponding cell in C is empty, the result is a #NUM! error.
Goal Seek won't work and the macro faults. So, we build in a bit of
error protection

Sub Macro9GeneralizedErrorTraps(
Dim aCell As Rang
With Range("d1"
For Each aCell In Range(.Cells(1, 1), .End(xlDown)
With aCel
If IsEmpty(.Offset(0, -1).Value) The
.Offset(0, -1).Value =
End I
.GoalSeek Goal:=0, ChangingCell:=.Offset(0, -1
End Wit
Next aCel
End Wit
End Su

--
Regards

Tushar Meht
www.tushar-mehta.co
Excel, PowerPoint, and VBA add-ins, tutorial
Custom MS Office productivity solution
 
... an equation which when simplified looks like the following:
=5+2x+lnx.
Click to expand...

Just something different. Are all your equations in the form of:

=a+b*x+LN(x)

Here is a quick and dirty custom function to try. It might give you a few
more digits of accuracy.

Here, =Fx(5,2) returned: .00664893970810946


Function Fx(a, b, Optional Guess = 1)
Dim x As Double
Dim t As Double
Dim LastGuess As Double
Dim Counter As Long

t = b * Exp(-a)

If t < -Exp(-1) Then
Fx = "Complex!"
Exit Function
End If

If t < 0 Then
'Note: Most likely there are two solutions
'Your code here if you want...
End If

If Guess = 1 Then x = 1 Else x = t
LastGuess = x + 1 ' Just make it different

Do While LastGuess <> x And Counter <= 15
LastGuess = x
x = (x ^ 2 + t * Exp(-x)) / (x + 1)
Counter = Counter + 1
Loop
Fx = x / b
End Function


HTH
Dana DeLouis


Tony said:
No.....the target cell actually contains an equation which when simplified looks like the following:
=5+2x+lnx. The changing cell value resolves the unknown "x" so that the
Click to expand...
final answer in the target cell is always zero. I have no problems
generating a correct answer using goal seek or solver, one row at a time.
The problem is I have to do this for hundreds and even thousands of rows.
Thanks in advance for any help you can provide.
 
Back
Top